Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

From SQL table to JSON through PHP

Tags:

json

php

mysql

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?

like image 225
user2431729 Avatar asked Mar 24 '16 10:03

user2431729


2 Answers

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);
like image 98
Refilon Avatar answered Sep 23 '22 23:09

Refilon


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.

like image 40
pihh-rocks Avatar answered Sep 24 '22 23:09

pihh-rocks