-1

I am trying to create a json object in php that looks like this:

[{"month":"Jan","ABC":"79","DEF":"21","HIJ":"12"},
{"month":"Feb","ABC":"89","DEF":"35","HIJ":"8"}]

where ABC, DEF, HIJ are providers of a service and the counts are how many products they delivered each month. I pull this data out of a MySQL database.

So far, I've only been able to produce this json object (which has value but I really need to prdice the other one too)

[{"month":"Jan",ABC":"79"},{"month":"Jan","DEF":"21"},{"month":"Jan","HIJ":"12"},{"month":"Feb","ABC":"89"},{"month":"Feb","DEF":"35"},{"month":"Feb","HIJ":"8"}]

using the php loop below:

while ($row = mysqli_fetch_assoc($result)) {

if ($row['Month'] == "01")

$new_array[] = array("month"=>"Jan", $row['provider']=>$row['count']);

if ($row['Month'] == "02")
$new_array[] = array( "month"=>"Feb",$row['provider']=>$row['count']);

and then I follow up with 

**print json_encode($new_array);**

to view my output.

Thanks for helping me figure this out! happy New Year everyone.

Ankur
  • 3,584
  • 1
  • 24
  • 32
tomish
  • 77
  • 1
  • 13
  • sorry for my previous question. But, what I can assure you is that you can be more dynamic with your code. You are checking for month 01 and with that value you can cast it into a date. – Oliver M Grech Jan 05 '15 at 18:07
  • 1
    Do you really want the integers to be included as strings?... – War10ck Jan 05 '15 at 18:07
  • Integers as strings right now does not matter to me, and the "Jan" for "01" is also fine for now – tomish Jan 05 '15 at 18:09
  • As far as the month conversions are concerned, I believe [this answer](http://stackoverflow.com/questions/18467669/convert-number-to-month-name-in-php) can simplify your code a lot. Consider using [DateTime](http://php.net/manual/en/class.datetime.php) objects instead of manually converting from number to month yourself... – War10ck Jan 05 '15 at 18:12

1 Answers1

0

Split it into multiple stages:

$rawdata = array()
while ($row = mysqli_fetch_assoc($result)) {
    $rawdata[$row['month']][] = array($row['provider'], $row['count']);
}

This'll give you an array of provider/count values, indexed by their month. Then you take that array and build your final structure:

$final = array()
foreach($rawdata as $month => $counts) {
   $temp = array('month' => convert_month_to_word($month));
   $final = array_merge($temp, $counts);
}

echo json_encode($final);

Note that this structure is pretty horrible. It'll make looking up a particular month's data difficult, since now you have to search sub-arrays. Wouldn't it be easier to just have

$data = array(
  'Jan' => array(
       'abc' => 1,
       'def' => 2,
etc...

for your structure?

War10ck
  • 12,387
  • 7
  • 41
  • 54
Marc B
  • 356,200
  • 43
  • 426
  • 500