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 ?

Answer 1

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

Answer 2

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

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

Answer 4

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)

Answer 5

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.

Answer 6

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.