From SQL table to JSON through PHP

Question

I'm struggling with this problem since a couple of days and I can not find the right solution.

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

databse table

and, again with php, have a 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 retrieves the info from the table but I'm not able to put them in the correct order and also there are probably better ways to do it..

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 retrieve is like this: ["2016-03-18","2016-03-13","2016-03-11","2016-03-06","2016-03-04","6","1","1","1","1"]

Can anyone help me please?

• Nate: Do not use any of the functions that begin with mysql_. They are obsolete, not maintained, have been removed from PHP 7+, and unsafe. Use the equivalent mysqli_ functions or the modern PDO database classes.

Answer 1

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

array_merge() will put the first array before 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);

Since the queries you are executing are identical, you can just 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);

• user2431729: thank you for the tip, but now I have this: [{"label":"2016年03月18日"},{"value":"6"},{"label":"2016年03月13日"},{"value":"1"},{"label":"2016年03月11日"},{"value":"1"},{"label":"2016年03月06日"},{"value":"1"},{"label":"2016年03月04日"},{"value":"1"}] but I need that the label and the value are together, how can I do it?
• Refilon: @user2431729, sorry see updated answer :) Should work fine now :) Did not test code ;) So if still not correct, answer again ;)
• Refilon: No problem @user2431729. Glad I could help.

Answer 2

Try

echo json_encode($merged);

return by it self wont be enough sometimes. For example, if you want to recieve the json on an ajax call it has to be echoed.

Other thing, you should take a look on PDO instead of using plain old mysql functions.

Answer 3

Please use it like 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)){
 $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);
}