PHP JSON Encode Date Format

[FooKelvin]

Hi All,

 

I am using JTable Plug in and using PHP code as my server language.

The plug in requires json encode in this format,

 

** please ignore other data. except for "RecordDate" **

{
 "Result":"OK",
 "Records":[
  {"PersonId":1,"Name":"Benjamin Button","Age":17,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":2,"Name":"Douglas Adams","Age":42,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":3,"Name":"Isaac Asimov","Age":26,"RecordDate":"\/Date(1320259705710)\/"},
  {"PersonId":4,"Name":"Thomas More","Age":65,"RecordDate":"\/Date(1320259705710)\/"}
 ]
}

What i have for my output is,

{
"Result": "OK",
"TotalRecordCount": 1,
"Records": [
{"Row": "1","emp_id": "Nhhh","course1": {"date": "2017-01-06 00:00:00","timezone_type": 3,"timezone": "Asia\/Brunei"}
}]
}

Im currently using datetime as my data type in my database (SQL Server), and the output is empty due to wrong json date format.

 

thanks for the help.

[requinix]

You do realize your JSON is completely different from the example, right?

 

JSON doesn't have a "date" type. It's just a string. When you encode the date, do it with the string "/Date(" + the Unix timestamp + ")/".

[FooKelvin]

this is my current php code.

		$rows = array();
		while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
		{
		    $rows[] = $row; // <= do you mean i need to convert it here?
		}

                //$recordCount = $row['RecordCount'];
                //Return result to jTable
		$jTableResult = array();
		$jTableResult['Result'] = "OK";
                $jTableResult['TotalRecordCount'] = $recordCount;
		$jTableResult['Records'] = $rows;
		print json_encode($jTableResult);

[requinix]

Yeah, you'd need to do it there. course1 is apparently a DateTime - discard that and replace it with a string value. Or be fancy and replace it with

class JsonDateTime extends DateTime implements JsonSerializable {

	public function __construct(DateTime $dt) {
		parent::__construct($dt->format("r"));
	}

	public function jsonSerialize() {
		return "/Date(" . $this->getTimestamp() . ")/";
	}

}

$row["course1"] = new JsonDateTime($row["course1"]);

and let JsonSerializable do the rest of the work.

 

 

Anyway, fine, whatever, but your code is still completely different from the example. So can you resolve that? What do the two samples have to do with each other?

[FooKelvin]

The first sample code is from documentation, the second code sample is my own output..

 

Here is the link of documentation.

the problem that i facing now is the date format. i cant get the exactly the date format in the documentation. which is converted to unix timestamp.

 

Documentation => http://jtable.org/GettingStarted

[requinix]

the problem that i facing now is the date format. i cant get the exactly the date format in the documentation. which is converted to unix timestamp.

What is your code?

[FooKelvin]

What is your code?

 $result = sqlsrv_query( $conn, "SELECT COUNT(*) AS RecordCount FROM [A_Sys].[dbo].[testdate]");
 $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
 $recordCount = $row['RecordCount'];
 //Get records from database
 $result = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY [emp_id]) As Row,  *  FROM [A_Sys].[dbo].[testdate]) As PeopleWithRowNumbers WHERE Row >" . $_REQUEST["jtStartIndex"] . "AND Row <= " . $_GET['jtStartIndex'] ." + ". $_GET['jtPageSize']."";
 $stmt = sqlsrv_query($conn, $result);
 //Add all records to an array
 $rows = array();
	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
	  $rows[] = $row;
	}
         //$recordCount = $row['RecordCount'];
         //Return result to jTable
	 $jTableResult = array();
	 $jTableResult['Result'] = "OK";
         $jTableResult['TotalRecordCount'] = $recordCount;
	 $jTableResult['Records'] = $rows;
	 print json_encode($jTableResult);

This is my DB:

 

emp_id      course1

---------------------------

1012345   1/5/2017

 

My Print JSON

{"Result":"OK","TotalRecordCount":1,"Records":[{"Row":"1","emp_id":"1012345","course1":{"date":"2017-01-05 00:00:00","timezone_type":3,"timezone":"Asia\/Brunei"}}]}

Edited January 6, 2017 by FooKelvin

[requinix]

It doesn't look like you've tried anything I said. How about doing the

 

course1 is apparently a DateTime - discard that and replace it with a string value.

from earlier and seeing if that works for you? If not, post the code you tried.

[FooKelvin]

It doesn't look like you've tried anything I said. How about doing the

 

 

from earlier and seeing if that works for you? If not, post the code you tried.

 

Yup, I tried to put it the php class like this, am i put in a correct place?

class JsonDateTime extends DateTime implements JsonSerializable {  //<== Getting Red line error "JsonDateTime"

	public function __construct(DateTime $dt) {
		parent::__construct($dt->format("r"));
	}

	public function jsonSerialize() {
		return "/Date(" . $this->getTimestamp() . ")/";
	}

}
$result = sqlsrv_query( $conn, "SELECT COUNT(*) AS RecordCount FROM [A_Sys].[dbo].[testdate]");
 $row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);

The browser show HTTP ERROR 500

[requinix]

Forget the class thing. It would be nice but it's probably too complicated for you to do right now.

 

This code

	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
	  $rows[] = $row;
	}

is where you fetch all the data from the database. Inside, each course1 in $row will be a DateTime object. When you json_encode() everything, DateTime objects turn into that date/timezone_type/timezone thing. You don't want that.

So instead of using course1 as is, change the value to be a simple string: you need "/Date(", the Unix timestamp which you can get with DateTime::getTimestamp(), and ")/".

$row["course1"] = "/Date(" . $row["course1"]->getTimestamp() . ")/";

Do that before you add $row into $rows.

 

Try that, then post your code if you're still having problems.

[FooKelvin]

Forget the class thing. It would be nice but it's probably too complicated for you to do right now.

 

This code

	while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
	{
	  $rows[] = $row;
	}

is where you fetch all the data from the database. Inside, each course1 in $row will be a DateTime object. When you json_encode() everything, DateTime objects turn into that date/timezone_type/timezone thing. You don't want that.

So instead of using course1 as is, change the value to be a simple string: you need "/Date(", the Unix timestamp which you can get with DateTime::getTimestamp(), and ")/".

$row["course1"] = "/Date(" . $row["course1"]->getTimestamp() . ")/";

Do that before you add $row into $rows.

 

Try that, then post your code if you're still having problems.

 

Thanks!

now it successfully convert to timestamp format

 

But yet, the date not able to show up.

hmm...let me figure it out..

[Jacques1]

Are you sure that the data format you're sending can even be processed? As requinix pointed out multiple times, your format is vastly different from the example; until now, you've essentially just created a database dump in JSON rather than a purposely crafted payload.

Edited January 9, 2017 by Jacques1