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

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