Jump to content
Sign in to follow this  
FooKelvin

PHP JSON Encode Date Format

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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 + ")/".

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by FooKelvin

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

post-179514-0-22217600-1483922742_thumb.png

 

But yet, the date not able to show up.

hmm...let me figure it out..

Share this post


Link to post
Share on other sites

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 by Jacques1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.