Jump to content

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.

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

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

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
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.

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

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.

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..

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
Link to post
Share on other sites
This thread is more than a year old.

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.

×
×
  • 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.