From SQL table to JSON through PHP

Question

I have been struggling with this problem since two days, and I cannot find the right solution.

I would like to get data from this database table via PHP:

data table

and, again, with php, they have JSON output similar to this:

[{ "label": "2013-01-07", "value": "4" }, { "label": "2013-01-06", "value": "65" }, { "label": "2013-01-05", "value": "96" }] 

I wrote a function that extracts information from a table, but I can’t put them in the correct order, and there are probably better ways to do this.

 function getUsersCountOnDate() { $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC"); while($row = mysql_fetch_array($result)){ $date[] = $row['date']; } $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC"); while($row = mysql_fetch_array($result)){ $count[] = $row['count']; } $merged = array_merge($date, $count); return json_encode($merged); } 

What I get looks like this: ["2016-03-18", "2016-03-13", "2016-03-11", "2016-03-06", "2016-03-04", "6", "1", "1", "1", "1"]

Can anybody help me?

Answer 1

First of all, use the PDO or mysqli functions for querying the database.

array_merge() will put the first array in front of the second array. For example: $array1 = array('red', 'yellow'); $array2 = array('blue', 'green'); $arrayMerged = array_merge($array1, $array2); $arrayMerged is now array(red,yellow,blue,green); $array1 = array('red', 'yellow'); $array2 = array('blue', 'green'); $arrayMerged = array_merge($array1, $array2); $arrayMerged is now array(red,yellow,blue,green);

Since the queries you execute are identical, you can simply do this:

 $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC"); $i = 0; while($row = mysql_fetch_array($result)){ $date[$i][label] = $row['date']; $date[$i][value] = $row['count']; $i++; } $newArray = json_encode($date); 

Answer 2

Try

echo json_encode($merged);

return by it self will usually not be enough. For example, if you want to get json by calling ajax, it should be an echo.

Another thing is you should take a look at PDO instead of using old old mysql functions.

Answer 3

Please use it as follows:

 function getUsersCountOnDate() { $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC"); while($row = mysql_fetch_array($result)){ $dates[] = $row['date']; } $result = mysql_query("Select FROM_UNIXTIME(regtime, '%Y-%m-%d') as date, count(FROM_UNIXTIME(regtime, '%Y-%m-%d')) as count from users group by FROM_UNIXTIME(regtime, '%Y-%m-%d') order by FROM_UNIXTIME(regtime, '%Y-%m-%d') DESC"); while($row = mysql_fetch_array($result)){ $count[] = $row['count']; } $dates = array("2013-01-07", "2013-01-06", "2013-01-05"); $count = array("4", "65", "96"); $i = 0; foreach ($dates as $date) { $newArray[$i]['label'] = $date; $newArray[$i]['value'] = $count[$i]; $i++; } ///print_r($newArray); return json_encode($newArray); }