Create dynamic JSON from foreach

Question

I'm using jquery flot charts to represent my data. Here's the example JSFiddle I made that shows how the JSONS's required for the chart should look.

The data source is from a MySql stored procedure that has the below output example:

I need to represent in the chart, the count values stacked for different innumber's on y-axis, the name values on x-axis, and in another chart, the values for outnumber. (in stacked bars).

-The data series should match, so the specific labels should appear against customers.

Here's the PHP I have so far:

$query = $this->db->query("call GetAllCustomersV2($id, $year, $month, $day)");
$customers = $query->result_array();

foreach ($customers as $customer) {

  if($customer['innumber'] != null){

      $chartInbound['name'] = $customer['name'];
      $chartInbound['label'] = $customer['innumber'];
      $chartInbound['count'] = $customer['count'];
      $chartInbound['customerid'] = $customer['id'];

      array_push($out['chartInbound'], $chartInbound);
   }

   if($customer['outnumber'] != null){

      $chartOutbound['name'] = $customer['name'];
      $chartOutbound['label'] = $customer['outnumber'];
      $chartOutbound['count'] = $customer['count'];
      $chartOutbound['customerid'] = $customer['id'];

      array_push($out['chartOutbound'], $chartOutbound);
   }
}

The output of print_r($out['chartInbound']); is:

Array
(
[0] => Array
    (
        [name] => 1st Online Solutions
        [label] => 01-02
        [count] => 577
        [customerid] => 129
    )

[1] => Array
    (
        [name] => Bookngo
        [label] => 01-02
        [count] => 2
        [customerid] => 95
    )

[2] => Array
    (
        [name] => Boutixury
        [label] => 07
        [count] => 1
        [customerid] => 14
    )

[3] => Array
    (
        [name] => Cruise Village
        [label] => 01-02
        [count] => 16
        [customerid] => 25
    )

[4] => Array
    (
        [name] => Cruise Village
        [label] => 00
        [count] => 1
        [customerid] => 25
    )

[5] => Array
    (
        [customer] => Cruise Village
        [label] => 07
        [countInbound] => 16
        [minsInbound] => 125
        [customerid] => 25
    )
  ...................
)

The output of print_r(json_encode($out['chartInbound'])); is:

[
{
    "name": "1st Online Soultions"
    "label": "01-02",
    "count": "577",
    "customerid": "129"
},
{
    "name": "Bookngo"
    "label": "01-020",
    "count": "2",
    "customerid": "129"
},
{
    "name": "Boutixury"
    "label": "07",
    "count": "1",
    "customerid": "14"
},
{
    "name": "Cruise Village"
    "label": "07",
    "count": "16",
    "customerid": "25"
},
 .................
]

But this is not very helpful.

Q: How can I create the dynamic JSON's shown in the above jsfiddle, from the query data ?

Gynandry

Should the data in your question (the data from MySql) and the data in your jsfiddle match?? – Terzetto

the format of chartData and chartTicks should be the same and their values should be populated dynamicaly – Gynandry

Can you modify the stored process, or it's fixed? Is it possible to attack the data directly, via a SQL sentence? If so, could you provide the data structure, maybe you could just modify the way you retrieve the data to obtain the data as if it were JSON. – Tunny

Answer 1

Going through your data with a loop and building up the newData and newTicks arrays for flot to use:

var newData = [];
var newLabels = []; // only used to get index since newData has objects in it
var newTicks = [];

for (var i = 0; i < dataFromServer.length; i++) {
    var datapoint = dataFromServer[i];

    var tick = newTicks.indexOf(datapoint.name);
    if (tick == -1) {
        tick = newTicks.length;
        newTicks.push(datapoint.name);
    }

    var index = newLabels.indexOf(datapoint.label);
    if (index == -1) {
        index = newLabels.length;
        newLabels.push(datapoint.label);

        newDataPoint = {
            label: datapoint.label,
            data: []
        };
        newDataPoint.data[tick] = [tick, datapoint.count];
        newData.push(newDataPoint);
    } else {
        newData[index].data[tick] = [tick, datapoint.count];
    }
}
for (var i = 0; i < newTicks.length; i++) {
    newTicks[i] = [i, newTicks[i]];
}
newLabels = null;

I also had to change your tooltip generation since your code only worked when all dataseries where complete and sorted. It is also simpler now.

complete fiddle

Slump

can you do a small update to the following JsFiddle to get the customerid also on the plot click event (bar click event). I've added the plot click bind function to the jsFiddle: jsfiddle.net/t1jgvyLx – Gynandry

See this updated fiddle. We fill a customerIds object in the data-building loop and use it in the tooltip creation. – Slump

I actually need it in the plot click bind function so I could redirect the page using the customerid as a URI parameter, I don't need it in the tooltip. – Gynandry

I think I discovered a problem in your solution. The issue is that the stacked bars overlaps / doesn't show all labels sometimes. Maybe due to the fact that the series are not the same length and it doesn't contain 0 if no data for a label. Can you check this please? I'll create a new post if needed, thanks. – Gynandry

also, there was a problem from my side, the same name should have the same customerid. see jsfiddle.net/d7gah2wd/1 – Gynandry

please see this post with the overlapping problem : #34161291 – Gynandry

Answer 2

You can do it on the client side (though ideally it should be done server-side), by using something like:

var table = [
    {name: 'a', label: 'l1', count: '15', customerid: '1'},
    {name: 'a', label: 'l2', count: '1', customerid: '1'},
    {name: 'a', label: 'l3', count: '7', customerid: '1'},
    {name: 'b', label: 'l1', count: '3', customerid: '2'},
    {name: 'b', label: 'l2', count: '9', customerid: '2'},
    {name: 'b', label: 'l3', count: '2', customerid: '2'},
    {name: 'c', label: 'l1', count: '1', customerid: '3'},
    {name: 'c', label: 'l2', count: '7', customerid: '3'},
    {name: 'a', label: 'l3', count: '5', customerid: '4'},
    {name: 'a', label: 'l2', count: '6', customerid: '4'}
];

var customers = {};
var labels = {};

var i;
for (i = 0; i < table.length; ++i) {
    customers[table[i].customerid] = table[i].name;
    labels[table[i].label] = labels[table[i].label] || [];
    labels[table[i].label].push([+table[i].customerid, +table[i].count]);
}

var chartData = [];
var chartTicks = [];

for (customer in customers) {
    if (customers.hasOwnProperty(customer)) {
        chartTicks.push([+customer, customers[customer]]);
    }
}
for (label in labels) {
    if (labels.hasOwnProperty(label)) {
        chartData.push({label: label, data: labels[label]});
    }
}

It accounts for different customers (different customerids) with the same name (though Flot will not really deal well with that), and customers with missing data for some labels. Shouldn't be too hard to shift this logic into PHP, and do it server-side.

EDIT: Okay, I hadn't noticed it acts weirdly when there are labelID "gaps". Here's the revised code:

var table = [
    {name: 'a', label: 'l1', count: '15', customerid: '1'},
    {name: 'a', label: 'l2', count: '1', customerid: '1'},
    {name: 'a', label: 'l3', count: '7', customerid: '1'},
    {name: 'b', label: 'l1', count: '3', customerid: '2'},
    {name: 'b', label: 'l2', count: '9', customerid: '2'},
    {name: 'b', label: 'l3', count: '2', customerid: '2'},
    {name: 'c', label: 'l1', count: '1', customerid: '3'},
    {name: 'c', label: 'l2', count: '7', customerid: '3'},
    {name: 'a', label: 'l3', count: '5', customerid: '7'},
    {name: 'a', label: 'l2', count: '6', customerid: '7'}
];

var customers = {};
var labels = {};

var chartData = [];
var chartTicks = [];

var i;
var customerNo = 0;
for (i = 0; i < table.length; ++i) {
    if(!customers.hasOwnProperty(table[i].customerid)) {
        customers[table[i].customerid] = table[i].name;
        chartTicks.push([customerNo, table[i].name]);
        customerNo++;
    }
    labels[table[i].label] = labels[table[i].label] || [];
    labels[table[i].label].push([customerNo - 1, +table[i].count]);
}

for (label in labels) {
    if (labels.hasOwnProperty(label)) {
        chartData.push({label: label, data: labels[label]});
    }
}

Label IDs are given in the order they appear in the table that comes from the server. (Though it still distinguishes between two customers with the same name but different customerIDs)

Adalai

thanks, but the problem with the above is that flot expects a series of data for both chartTicks and data in the form of "data":[[0,577],[1,2],[2,16],[3,11],[4,22],[5,43]], .... Instead of cusomerid it should be the index (costumer position in chart) and should match the labels with the customers, like in the jsfiddle – – Gynandry

Answer 3

Just an idea, I suppose you're using a group by in your stored procedure. If you could modify it and add a WITH ROLLUP the database would have the count calculated for you... See https://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html or search SO for suggestion

Cherub

thanks, that's some useful info, I've added it to the stored procedure! – Gynandry

Answer 4

You are going to have to transform the structures yourself. You can either do this server side or client side. In either case run through the results and build the structure you want. Be careful of trying to encode php associative arrays in json and beware of the behaviour of NUMERIC_CHECK.

Nunatak

Sorry you thought that. I don't know if you were expecting code. – Nunatak

Answer 5

It looks like your data points in chartTicks[i] need to match the order of the ticks in chartData[i].data. One way to ensure such a match is to sort the data by name in sql and to stack your results by customer first and label second in php.

$query = $this->db->query("call GetAllCustomersV2($id, $year, $month, $day)");
$customers = $query->result_array(); //should be sorted by name
$results = array();

foreach ($customers as $customer) {
    $i = is_array($results[$customer['name']][$customer['innumber']]) 
        ? count($results[$customer['name']][$customer['innumber']])
        : 0;

    //stack data points by customer name first and label second
    $results[$customer['name']][$customer['innumber']][] = array($i,$customer['count']);
}

$chartData = array();
$chartTicks = array();
$i=0;

foreach($results as $name => $labels) {
    $chartTicks[] = array($i++,$name);
    foreach($labels as $label => $data) {
        $chartData[] = array(
            'label' => $label,
            'data' => $data,
        );
    }
}

print json_encode($chartData);
print json_encode($chartTicks);

Loaning

Answer 6

This is a concise way to transform your current JSON data structure into the desired output:

var reduced;
var chartData = Object.keys(reduced = data.reduce(function(a, b) {
  if(a[b.label]) {
    a[b.label].push([a[b.label].length, parseInt(b.count, 10)]);
  } else {
    a[b.label] = [[0, parseInt(b.count, 10)]];
  }
  return a;
}, {})).map(function(key) {
  return {
    label: key,
    data: reduced[key]
  };
});

Fiddle: http://jsfiddle.net/rdkgbteq/1/

Here is the same thing in PHP if you are wanting to transform the data on the server:

$reduced = array_reduce($data, function($result, $current) {
    if(array_key_exists($current['label'], $result)) {
        array_push($result, [count($result[$current['label']]), $current['count']]);
    } else {
        $result[$current['label']] = [[0, $current['count']]];
    }
    return $result;
}, array());

$formatted = array_map(function($key) {
    return array(
        'label' => $key,
        'data'  => $reduced[$key]
    ); 
}, array_keys($reduced));

echo json_encode($formatted);

Let me know if you want me to expand upon what's going on here.

Montane