Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 17 total
Thread Mysql PHP Json format problem with DateTime
Thu, Oct 29 2015 6:15 AMPermanent Link

Pasquale

Web Pos srl

I have build dataset.php format json :

<?php

include("config.php");

$Risposta="NULLA";

$connessione = mysql_connect("$host", "$db_user", "$db_psw");
if (!$connessione)
{
  $Risposta="-1";
}
else
{

 mysql_select_db("$db_name", $connessione) or $Risposta="-1";  

 $dataset = mysql_real_escape_string($_GET['dataset']);

 $query = "";

 IF ($dataset=='PosUtenti')
 {
   $Utente = mysql_real_escape_string($_GET['Utente']);

   $Utente = str_replace("\'","",$Utente);
  
   $query = "SELECT * FROM Utenti WHERE Utente='$Utente'";

 }
 Else
 IF ($dataset=='PosMerchant')
 {
   $ID = mysql_real_escape_string($_GET['ID']);

   $query = "SELECT * FROM Merchant WHERE ID=$ID";   
 }
 Else
 IF ($dataset=='PosPlafond')
 {
   $IDMerchant = mysql_real_escape_string($_GET['IDMerchant']);

   $query = "SELECT * FROM Plafond WHERE IDMerchant=$IDMerchant";   
 }
 Else
 IF ($dataset=='PosMovimenti')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Movimenti WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosMessaggi')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Messaggi WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosListini')
 {
   $Operatore = mysql_real_escape_string($_GET['Operatore']);
   $Operatore = str_replace("\'","",$Operatore);


   $Codice = mysql_real_escape_string($_GET['Codice']);
   $Codice = str_replace("\'","",$Codice);

   $Tipo = mysql_real_escape_string($_GET['Tipo']);
   $Tipo = str_replace("\'","",$Tipo);
   

   $query = "SELECT * FROM listini WHERE Operatore='$Operatore' AND Codice='$Codice' AND Tipo='$Tipo'";   
 }
 Else
 IF ($dataset=='PosOperatori')
 {    

   $query = "SELECT * FROM Operatori";   
 }
 Else
 IF ($dataset=='PosRicaricheOnLine')
 {    

   $query = "SELECT * FROM RicaricheOnLine WHERE IDRicarica=0";
 }


 IF ($query != "")
 {
 
    $result = mysql_query($query, $connessione) or $Risposta="-1";

    $emparray = array();

      while($row =mysql_fetch_assoc($result))
      {
          $emparray[] = $row;
      }

    $JsonTemp = json_encode($emparray);
  
    $JsonTestata = '{   "rows": ';

    $JsonCoda =  "}";

    $Json = $JsonTestata. " " .$JsonTemp. " " .$JsonCoda;

    echo $Json;
 }

 mysql_close($connessione);
}


?>

THE PROBLEM WITH THE DATETIME IN EWB CLIENT is : 1970-01-01 01:00:02

in mysql the field is DATETIME
Thu, Oct 29 2015 6:31 AMPermanent Link

Matthew Jones

Pasquale wrote:

> THE PROBLEM WITH THE DATETIME IN EWB CLIENT is : 1970-01-01 01:00:02
>
> in mysql the field is DATETIME

What specifically is the problem? That you don't know how to get it
into the right format, that the value you get doesn't get parsed, or
what?

It's ages since I did PHP, and even then Google gave me the answers,
but we need a clearer question to help I think.

--

Matthew Jones
Thu, Oct 29 2015 6:37 AMPermanent Link

Pasquale

Web Pos srl

in the table is stored date 2015-10-28 20:50:20 but I read when I view the data in the grid always comes out the same day

You can also check you just go on :

www.mbspay.it/payplatform.html

(write test and test for user and password)

then go on AREA AMMINISTRATIVA

then go on MOVIMENTI

if you're in google chrome F12 you can view the values &#8203;&#8203;for realize the PROBLEM.



"Matthew Jones" wrote:

Pasquale wrote:

> THE PROBLEM WITH THE DATETIME IN EWB CLIENT is : 1970-01-01 01:00:02
>
> in mysql the field is DATETIME

What specifically is the problem? That you don't know how to get it
into the right format, that the value you get doesn't get parsed, or
what?

It's ages since I did PHP, and even then Google gave me the answers,
but we need a clearer question to help I think.

--

Matthew Jones
Thu, Oct 29 2015 7:01 AMPermanent Link

Matthew Jones

Pasquale wrote:

> in the table is stored date 2015-10-28 20:50:20 but I read when I
> view the data in the grid always comes out the same day

Well, my first thought was that you should use the proper ISO date time
format (with the T in the middle). But I think that is probably wrong,
as the JSON reference at
http://www.elevatesoft.com/manual?action=viewtopic&id=ewb2&topic=DataSet
_JSON_Reference says that you need a simple integer which is number of
seconds past the reference time. It has examples of the date values
there.

I presume that what you are seeing is that the EWB code is not getting
the integer it expects, and thus defaulting to today, or something
similar.

--

Matthew Jones
Thu, Oct 29 2015 7:02 AMPermanent Link

Matthew Jones

Also, I think Tim wrote a PHP interface, so it might be worth looking
at that to see if there is code you could use.


--

Matthew Jones
Thu, Oct 29 2015 7:51 AMPermanent Link

Ivan Mihailov

To convert php datetime variable  to EWB datetime format, you can use finction like this:

public static function MyDateToEwbDate($my_date_string)
{
   $temp_date = date_create($my_date_string, timezone_open('UTC'));
   return (date_format($temp_date, 'U') * 1000) ;   
}

where $my_date_string is datetime variable in ISO format - '2015-10-29 13:50:00'

Ivan

Pasquale wrote:

I have build dataset.php format json :

<?php

include("config.php");

$Risposta="NULLA";

$connessione = mysql_connect("$host", "$db_user", "$db_psw");
if (!$connessione)
{
  $Risposta="-1";
}
else
{

 mysql_select_db("$db_name", $connessione) or $Risposta="-1";  

 $dataset = mysql_real_escape_string($_GET['dataset']);

 $query = "";

 IF ($dataset=='PosUtenti')
 {
   $Utente = mysql_real_escape_string($_GET['Utente']);

   $Utente = str_replace("\'","",$Utente);
  
   $query = "SELECT * FROM Utenti WHERE Utente='$Utente'";

 }
 Else
 IF ($dataset=='PosMerchant')
 {
   $ID = mysql_real_escape_string($_GET['ID']);

   $query = "SELECT * FROM Merchant WHERE ID=$ID";   
 }
 Else
 IF ($dataset=='PosPlafond')
 {
   $IDMerchant = mysql_real_escape_string($_GET['IDMerchant']);

   $query = "SELECT * FROM Plafond WHERE IDMerchant=$IDMerchant";   
 }
 Else
 IF ($dataset=='PosMovimenti')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Movimenti WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosMessaggi')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Messaggi WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosListini')
 {
   $Operatore = mysql_real_escape_string($_GET['Operatore']);
   $Operatore = str_replace("\'","",$Operatore);


   $Codice = mysql_real_escape_string($_GET['Codice']);
   $Codice = str_replace("\'","",$Codice);

   $Tipo = mysql_real_escape_string($_GET['Tipo']);
   $Tipo = str_replace("\'","",$Tipo);
   

   $query = "SELECT * FROM listini WHERE Operatore='$Operatore' AND Codice='$Codice' AND Tipo='$Tipo'";   
 }
 Else
 IF ($dataset=='PosOperatori')
 {    

   $query = "SELECT * FROM Operatori";   
 }
 Else
 IF ($dataset=='PosRicaricheOnLine')
 {    

   $query = "SELECT * FROM RicaricheOnLine WHERE IDRicarica=0";
 }


 IF ($query != "")
 {
 
    $result = mysql_query($query, $connessione) or $Risposta="-1";

    $emparray = array();

      while($row =mysql_fetch_assoc($result))
      {
          $emparray[] = $row;
      }

    $JsonTemp = json_encode($emparray);
  
    $JsonTestata = '{   "rows": ';

    $JsonCoda =  "}";

    $Json = $JsonTestata. " " .$JsonTemp. " " .$JsonCoda;

    echo $Json;
 }

 mysql_close($connessione);
}


?>

THE PROBLEM WITH THE DATETIME IN EWB CLIENT is : 1970-01-01 01:00:02

in mysql the field is DATETIME
Thu, Oct 29 2015 8:20 AMPermanent Link

Pasquale

Web Pos srl

at which point I have to call this function conversion ?


  $result = mysql_query($query, $connessione) or $Risposta="-1";
 
       $emparray = array();

       while($row =mysql_fetch_assoc($result))
         {
          $emparray[] = $row;
         }

       $JsonTemp = json_encode($emparray);
     
       $JsonTestata = '{   "rows": ';
 
       $JsonCoda =  "}";

       $Json = $JsonTestata. " " .$JsonTemp. " " .$JsonCoda;

       echo $Json;





Ivan Mihailov wrote:

To convert php datetime variable  to EWB datetime format, you can use finction like this:

public static function MyDateToEwbDate($my_date_string)
{
   $temp_date = date_create($my_date_string, timezone_open('UTC'));
   return (date_format($temp_date, 'U') * 1000) ;   
}

where $my_date_string is datetime variable in ISO format - '2015-10-29 13:50:00'

Ivan

Pasquale wrote:

I have build dataset.php format json :

<?php

include("config.php");

$Risposta="NULLA";

$connessione = mysql_connect("$host", "$db_user", "$db_psw");
if (!$connessione)
{
  $Risposta="-1";
}
else
{

 mysql_select_db("$db_name", $connessione) or $Risposta="-1";  

 $dataset = mysql_real_escape_string($_GET['dataset']);

 $query = "";

 IF ($dataset=='PosUtenti')
 {
   $Utente = mysql_real_escape_string($_GET['Utente']);

   $Utente = str_replace("\'","",$Utente);
  
   $query = "SELECT * FROM Utenti WHERE Utente='$Utente'";

 }
 Else
 IF ($dataset=='PosMerchant')
 {
   $ID = mysql_real_escape_string($_GET['ID']);

   $query = "SELECT * FROM Merchant WHERE ID=$ID";   
 }
 Else
 IF ($dataset=='PosPlafond')
 {
   $IDMerchant = mysql_real_escape_string($_GET['IDMerchant']);

   $query = "SELECT * FROM Plafond WHERE IDMerchant=$IDMerchant";   
 }
 Else
 IF ($dataset=='PosMovimenti')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Movimenti WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosMessaggi')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Messaggi WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosListini')
 {
   $Operatore = mysql_real_escape_string($_GET['Operatore']);
   $Operatore = str_replace("\'","",$Operatore);


   $Codice = mysql_real_escape_string($_GET['Codice']);
   $Codice = str_replace("\'","",$Codice);

   $Tipo = mysql_real_escape_string($_GET['Tipo']);
   $Tipo = str_replace("\'","",$Tipo);
   

   $query = "SELECT * FROM listini WHERE Operatore='$Operatore' AND Codice='$Codice' AND Tipo='$Tipo'";   
 }
 Else
 IF ($dataset=='PosOperatori')
 {    

   $query = "SELECT * FROM Operatori";   
 }
 Else
 IF ($dataset=='PosRicaricheOnLine')
 {    

   $query = "SELECT * FROM RicaricheOnLine WHERE IDRicarica=0";
 }


 IF ($query != "")
 {
 
    $result = mysql_query($query, $connessione) or $Risposta="-1";

    $emparray = array();

      while($row =mysql_fetch_assoc($result))
      {
          $emparray[] = $row;
      }

    $JsonTemp = json_encode($emparray);
  
    $JsonTestata = '{   "rows": ';

    $JsonCoda =  "}";

    $Json = $JsonTestata. " " .$JsonTemp. " " .$JsonCoda;

    echo $Json;
 }

 mysql_close($connessione);
}


?>

THE PROBLEM WITH THE DATETIME IN EWB CLIENT is : 1970-01-01 01:00:02

in mysql the field is DATETIME
Thu, Oct 29 2015 9:01 AMPermanent Link

Ivan Mihailov

You don't have to use function json_encode, you have to create the json string manually.


$json_str = '';
while($row =mysql_fetch_assoc($result))
{
    if( !empty($json_str) ) $json_str = $json_str . ',' ;

    $json_str = $json_str . '{' . '"FIELD1": ' . strval($row['FIELD1']) . ',' . '"FIELD2": ' . strval($row['FIELD2']) . ',' .....
           
    if( isset($row['DATE_FIELD']) )
    {
    $json_str = $json_str . ','  . '"DATE_FIELD": ' . strval(self::MyDateToEwbDate($db_record['DATE_FIELD']))  ;
    }   
    
    $json_str = $json_str  . '}';
            
}

 $json_data = '{  "rows": ' . $json_str  .  ' }';


This code is from a php class , that's why I use self::

Ivan

Pasquale wrote:

at which point I have to call this function conversion ?


  $result = mysql_query($query, $connessione) or $Risposta="-1";
 
       $emparray = array();

       while($row =mysql_fetch_assoc($result))
         {
          $emparray[] = $row;
         }

       $JsonTemp = json_encode($emparray);
     
       $JsonTestata = '{   "rows": ';
 
       $JsonCoda =  "}";

       $Json = $JsonTestata. " " .$JsonTemp. " " .$JsonCoda;

       echo $Json;





Ivan Mihailov wrote:

To convert php datetime variable  to EWB datetime format, you can use finction like this:

public static function MyDateToEwbDate($my_date_string)
{
   $temp_date = date_create($my_date_string, timezone_open('UTC'));
   return (date_format($temp_date, 'U') * 1000) ;   
}

where $my_date_string is datetime variable in ISO format - '2015-10-29 13:50:00'

Ivan

Pasquale wrote:

I have build dataset.php format json :

<?php

include("config.php");

$Risposta="NULLA";

$connessione = mysql_connect("$host", "$db_user", "$db_psw");
if (!$connessione)
{
  $Risposta="-1";
}
else
{

 mysql_select_db("$db_name", $connessione) or $Risposta="-1";  

 $dataset = mysql_real_escape_string($_GET['dataset']);

 $query = "";

 IF ($dataset=='PosUtenti')
 {
   $Utente = mysql_real_escape_string($_GET['Utente']);

   $Utente = str_replace("\'","",$Utente);
  
   $query = "SELECT * FROM Utenti WHERE Utente='$Utente'";

 }
 Else
 IF ($dataset=='PosMerchant')
 {
   $ID = mysql_real_escape_string($_GET['ID']);

   $query = "SELECT * FROM Merchant WHERE ID=$ID";   
 }
 Else
 IF ($dataset=='PosPlafond')
 {
   $IDMerchant = mysql_real_escape_string($_GET['IDMerchant']);

   $query = "SELECT * FROM Plafond WHERE IDMerchant=$IDMerchant";   
 }
 Else
 IF ($dataset=='PosMovimenti')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Movimenti WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosMessaggi')
 {
   $LinkMerchant = mysql_real_escape_string($_GET['LinkMerchant']);

   $query = "SELECT * FROM Messaggi WHERE LinkMerchant=$LinkMerchant";   
 }
 Else
 IF ($dataset=='PosListini')
 {
   $Operatore = mysql_real_escape_string($_GET['Operatore']);
   $Operatore = str_replace("\'","",$Operatore);


   $Codice = mysql_real_escape_string($_GET['Codice']);
   $Codice = str_replace("\'","",$Codice);

   $Tipo = mysql_real_escape_string($_GET['Tipo']);
   $Tipo = str_replace("\'","",$Tipo);
   

   $query = "SELECT * FROM listini WHERE Operatore='$Operatore' AND Codice='$Codice' AND Tipo='$Tipo'";   
 }
 Else
 IF ($dataset=='PosOperatori')
 {    

   $query = "SELECT * FROM Operatori";   
 }
 Else
 IF ($dataset=='PosRicaricheOnLine')
 {    

   $query = "SELECT * FROM RicaricheOnLine WHERE IDRicarica=0";
 }


 IF ($query != "")
 {
 
    $result = mysql_query($query, $connessione) or $Risposta="-1";

    $emparray = array();

      while($row =mysql_fetch_assoc($result))
      {
          $emparray[] = $row;
      }

    $JsonTemp = json_encode($emparray);
  
    $JsonTestata = '{   "rows": ';

    $JsonCoda =  "}";

    $Json = $JsonTestata. " " .$JsonTemp. " " .$JsonCoda;

    echo $Json;
 }

 mysql_close($connessione);
}


?>

THE PROBLEM WITH THE DATETIME IN EWB CLIENT is : 1970-01-01 01:00:02

in mysql the field is DATETIME
Thu, Oct 29 2015 9:19 AMPermanent Link

Ivan Mihailov

sorry for the typo  "$db_record['DATE_FIELD']" , I meant  "$row['DATE_FIELD']" ..


Ivan
Thu, Oct 29 2015 10:20 AMPermanent Link

Pasquale

Web Pos srl

tanks


Ivan Mihailov wrote:

sorry for the typo  "$db_record['DATE_FIELD']" , I meant  "$row['DATE_FIELD']" ..


Ivan
Page 1 of 2Next Page »
Jump to Page:  1 2
Image