Create a JSON for series of data using PHP with MySQL query

Question

I am trying to create a bar chart using JSON data and need to change the format into month series, but I am confused on how to do that. Please help on how I can fix this.

Current JSON data:

{ "data": [

{

"name": "January",

"facility": "Discussion Room",

"value": 22

},

{

"name": "January",

"facility": "Carrel Room",

"value": 102

},

{

"name": "January",

"facility": "Locker",

"value": 5

},

{

"name": "February",

"facility": "Discussion Room",

"value": 86

},

{

"name": "February",

"facility": "Carrel Room",

"value": 155

},

{

"name": "Mac",

"facility": "Carrel Room",

"value": 224

},

{

"name": "Mac",

"facility": "Locker",

"value": 3

},

]

}

Desired JSON data:

{ "data": [

{

"name": "January",

"series":[

{ "name":"Discussion Room","value": 22},

{ "name": "Carrel Room", "value": 102},

{ "name": "Locker", "value": 5},

]

"name": "February",

"series":[

{ "name":"Discussion Room","value": 86},

{ "name": "Carrel Room", "value": 155},

{ "name": "Locker", "value": 0},

]

"name": "March",

"series":[

{ "name":"Discussion Room","value": 0},

{ "name": "Carrel Room", "value": 224},

{ "name": "Locker", "value": 3},

]

]

}

My Code:

$db = mysqli_connect($host, $user, $pass, $database) or die("you did not connect");

    header('Access-Control-Allow-Origin: *');
    header("Access-Control-Allow-Credentials: true");
    header('Access-Control-Allow-Methods: GET');
    header('Access-Control-Max-Age: 1000');
    header('Access-Control-Allow-Headers: Origin, Content-Type');

$facility =($_GET['year']);

$query = "select monthname(datetime) as 'name',
        case 
            when all_items.itype = '127' then 'Discussion Room'
            when all_items.itype = '126' then 'Carrel Room'
            when all_items.itype = '121' then 'Locker'
            else '0' 
        end as 'facility',
        count(*) AS 'value'

        from statistics 
        left join (
            select itemnumber, itype from deleteditems
            union
            select itemnumber, itype from items 
            ) as all_items USING (itemnumber)

        where all_items.itype in (127, 126, 121) and
        statistics.type = 'issue' and
        year(statistics.datetime) = '$facility' 

        group by month(datetime), all_items.itype desc";

$result = mysqli_query($db, $query)or die(mysqli_error());

$response = array();

$posts = array();

while($row=$result->fetch_assoc()) 
{ 
$month=$row['name'];
$facility=$row['facility'];
$value=$row['value'];

$posts[] = array('name'=> $month, 'facility'=> $facility, 'value'=> $value); 
}

$response['data'] = $posts;

header('Content-Type: application/json');
echo json_encode($response, JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT);

You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Dec 16 '19 at 09:17

RyDog · Accepted Answer · 2019-12-16T09:22:32.583

0

You should change the way you query the database. You should use prepared statements rather than injecting strings into the query. This leaves you vulnerable to sql injection.

Read more about prepared statements.

Back to the question:

For each month found you need to create an array with a series array inside it. Check to see if the month array exists and if it doesn't create it and create the series array. Then creat an array with series data in it and push the series data in it and push that into the series array for that month.

Its a lot easier to look at the code. I have added comments as well.

$result = mysqli_query($db, $query)or die(mysqli_error());

$response = array();

$posts = array();

while($row=$result->fetch_assoc()) 
{
    $month=$row['name'];

    if(!isset($posts[$month])){// Check to see if the month array exists
    //if it doesn't create it
        $posts[$month] = array();

        //create the series array so data can be pushed to it
        $posts[$month]["series"] = array();
    }

    //check to see if it is null and if it is make it 0
    if(is_null($row['value'])){
        $row['value'] =0;
    }

    //put the series data in the correct format with correct names
    $series_array = array(
        "name" => $row['facility'],
        "value" => $row['value']
    );

    //push the series data into the months series array
    array_push($posts[$month]["series"],$series_array);
}
// put months and series together 
$new_posts=array();
foreach($posts as $month_name => $data){
    array_push($new_posts, array("name"=> $month_name, "series"=> $data['series'] ));
}

$posts = $new_posts;
$response['data'] = array($posts);

header('Content-Type: application/json');
echo json_encode($response, JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT);

edited Dec 16 '19 at 09:22

answered Dec 16 '19 at 04:22

RyDog

1,169
1
9
12

your output is incorrect according to desired output needed by Hana – AHSAN KHAN Dec 16 '19 at 04:55
@AHSANKHAN I have fixed the out put just for got to put it in an array. changed $response['data'] = $posts; to $response['data'] = array($posts); . It works now. – RyDog Dec 16 '19 at 06:07
It works! Thank you for your help @RyDog! But is there any way to return zero value for NULL / 0 data in Feb/March as shown in required JSON data – Hana Haniz Dec 16 '19 at 06:52
@HanaHaniz I added a check for null and set to zero (with comments. See if that works for you. – RyDog Dec 16 '19 at 07:09
@RyDog your output is still not as per requirements please review my code , Hana do not want month in index key – AHSAN KHAN Dec 16 '19 at 09:06
You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman Dec 16 '19 at 09:17
@AHSANKHAN, I fixed the output for my solutionagain, thank you for pointing that out. Your solution looks a lot cleaner than mine. could you add the null check to it as mentioned in the comments above. – RyDog Dec 16 '19 at 09:25

AHSAN KHAN · Answer 2 · 2019-12-16T10:31:44.017

Here is your required code

$result = mysqli_query($db, $query)or die(mysqli_error($db));
$response = array();
$posts = array();
while($row=$result->fetch_assoc()){
    $month = $row['name'];
    $value = $row['value'];
    if(empty($value)){
        $value = 0;
    }
    $series = array(
        "name"  => $row['facility'],
        "value" => $value 
    );
    $found_key = array_search($month, array_column($posts, 'name'));// check if month value exist in name key 
    if(!$found_key){//if not found
        $found_key = sizeof($posts);// get the size of array which will be 1 more then last index of array 
    }
    $posts[$found_key]['name']  = $month;// this add name of month 
    if(!isset($posts[$found_key]['series'])){ // check if series is set
       $posts[$found_key]['series'] = array();// or set it
    }
    $posts[$found_key]['series'][] = $series; //assign the series array
}
$response['data'] = $posts;
header('Content-Type: application/json');
echo json_encode($response, JSON_NUMERIC_CHECK | JSON_PRETTY_PRINT);

above snippet is just the sub snippet of question poster code — AHSAN KHAN, Dec 16 '19 at 10:21

Create a JSON for series of data using PHP with MySQL query

2 Answers2