php: query timestamp table with JSON

Question

I want to output timestamp without double qoutes in "data:"

Here is my php code:

$sth = mysql_query("SELECT time FROM tableName");
$rows1 = array();
$rows1['name'] = 'Timestamp';
while($rr = mysql_fetch_assoc($sth)) {
    $rows1['data'][] = $rr['time'];
}
$result = array();
array_push($result,$rows1);
print json_encode($result, JSON_NUMERIC_CHECK);

This outputs this: [{"name":"Timestamp","data":["2015-05-03 22:03:37","2015-05-03 22:03:41"]}]

I want this: [2015-05-03 22:03:37,2015-05-03 22:03:41]

Then thats not JSON anymore. only number and booleans could be without quotes in JSON — Shiji.J, May 04 '15 at 21:05
I see nothing wrong with this code. Please provide more information on why you need that specific format. If you just want the array of times, why are you making an array of arrays? — Twisty, May 04 '15 at 21:21

score 1 · Accepted Answer · edited May 23 '17 at 12:21

Refer to http://en.wikipedia.org/wiki/JSON on JSON basic types and to http://markembling.info/2011/07/json-date-time on JSON DateTime representation.

Also make sure that you aware of MySQL integer field is returned as string in PHP (so with mysql_fetch_assoc you getting string values from DB and without "JSON_NUMERIC_CHECK" all your values would be strings)

It very depends on your JSON reader, how to store data to be read correctly. For an instance PHP DateTime object used in article http://nitschinger.at/Handling-JSON-like-a-boss-in-PHP to transmit dates. Also I would recommend to use ISO8601 date format for date values as your dates are not including timezone and could be treated differently on different readers.

<?php
$sth = mysql_query("SELECT time FROM tableName");
$rows1 = array();
$rows1['name'] = 'Timestamp';
$tz =  new DateTimeZone('UTC');
while($rr = mysql_fetch_assoc($sth)) {
    $date = new DateTime($rr['time'], $tz);
    $rows1['data'][] = $date->format(DateTime::ISO8601);
}
$result = array();
array_push($result,$rows1);
print json_encode($result);

But if you interested only on quotes removing for dates, you can use following code (however result will not be JSON):

<?php
$sth = mysql_query("SELECT time FROM tableName");
$rows1 = array();
$rows1['name'] = 'Timestamp';
while($rr = mysql_fetch_assoc($sth)) {
    $rows1['data'][] = '{' . $rr['time'] . '}';
}
$result = array();
array_push($result,$rows1);
print str_replace('}"', '', str_replace('"{', '', json_encode($result, JSON_NUMERIC_CHECK)));

php: query timestamp table with JSON

1 Answers1