joun joun - 11 months ago 102
MySQL Question

How to push array values into json string

I have this json string as datatable to draw a chart:

{"cols":[
{"id":"","label":"Month","pattern":"","type":"string"},
{"id":"","label":"Job","pattern":"","type":"number"},
{"id":"","label":"Internship","pattern":"","type":"number"}],
"rows":[
{"c":[{"v":"July","f":null},{"v":6,"f":null},{"v":2,"f":null}]},
{"c":[{"v":"August","f":null},{"v":0,"f":null},{"v":3,"f":null}]}]}


The json string is derived from this array (the values are from sql query):

$rows = array_map(function($item) {
return (object) ['c' => [
(object) ['v' => $item->month, 'f' => null],
(object) ['v' => intval($item->jobcount), 'f' => null],
(object) ['v' => intval($item->interncount), 'f' => null]
]];
}, array_values($data));


// prepare return data
$cols = [
(object) ['id' => '', 'label' => 'Month', 'pattern' => '', 'type' => 'string'],
(object) ['id' => '', 'label' => 'Job', 'pattern' => '', 'type' => 'number'],
(object) ['id' => '', 'label' => 'Internship', 'pattern' => '', 'type' => 'number'],
];

$returndata = new stdClass;
$returndata->cols = $cols;
$returndata->rows = $rows;

echo json_encode($returndata);


I want to insert another months from January to December with 0 values for jobcount and interncount into the json string.

How to put this array into the json string so that the other months will be included into the chart?

$months = array('January', 'February', 'March', 'April', 'May', 'Jun', 'July', 'August', 'September', 'October', 'November', 'December');


This is the chart.

chart

Answer Source

I think is easier if you add the empty months to the source data, before doing all that conversions to generate the final object. Looking at your code, I looks like array_values($data) in this case would be this...

[
    { month: 'July', jobcount: "6", interncount: "2" },
    { month: 'August', jobcount: "0", interncount: "3" }
]

The idea is loop through all months and on each interation check if the month exists, and in case not, add an empty row. But to avoid searching the existence of a month in an array of objects, I will first extract the current month list in an array...

// Your source data.
$currentdata = array_values($data);

// Lets's extract all months from your data into an array.
$datamonths = array();
foreach ($currentdata as $monthdata)
    $datamonths[] = $monthdata->month;

// Now, let's loop all months and check if are present or not in our data.
$allmonths = array('January', 'February', 'March', 'April', 'May', 'Jun', 'July', 'August', 'September', 'October', 'November', 'December');

$newsourcedata = array();
foreach ($allmonths as $onemonth) {
    if (($pos=array_search($onemonth,$datamonths)) === false)
        $newsourcedata[] = (object) ['month'=>$onemonth,'jobcount'=>"0",'interncount'=>"0"];
    else
        $newsourcedata[] = $currentdata[$pos];
}

// Now goes the rest of your code, but changing the sourcedata for the new one...
$rows = array_map(function($item) {
    return (object) ['c' => [
        (object) ['v' => $item->month, 'f' => null],
        (object) ['v' => intval($item->jobcount), 'f' => null],
        (object) ['v' => intval($item->interncount), 'f' => null]
    ]];
},$newsourcedata);

..........

I hope it helps.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download