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:

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?

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. — Nate
– Nate, Commented Mar 24, 2016 at 10:57

Refilon · Accepted Answer · 2016-03-24 10:53:01Z

3

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

edited Mar 24, 2016 at 10:53

answered Mar 24, 2016 at 10:38

Refilon

3,4971 gold badge32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2431729 Over a year ago

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 Over a year ago

@user2431729, sorry see updated answer :) Should work fine now :) Did not test code ;) So if still not correct, answer again ;)

Refilon Over a year ago

No problem @user2431729. Glad I could help.

pihh-rocks · Accepted Answer · 2016-03-24 10:21:56Z

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.

answered Mar 24, 2016 at 10:21

pihh-rocks

3082 silver badges15 bronze badges

Comments

Atif Tariq · Accepted Answer · 2016-03-24 11:08:31Z

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

Collectives™ on Stack Overflow

From SQL table to JSON through PHP

3 Answers 3

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Related