Index array element for JSON object

Question

I am pulling data from three MySQL tables to produce a bootstrap dataTable via AJAX and JSON in PHP.

I have multiple organizational units (Unit 1, Unit 2, Unit 3, etc.) I also have multiple program types (Beginning, Intermediate and Advanced). Finally, I want to show two years data (2105 and 2014) for each organizational unit side-by-side.

In other words, the resulting table would be structured like this (with one row of dummy values for the Beginning program type):

                   Unit 1       Unit 2       Unit 3
                2015   2014   2015  2014   2015 - 2014

Beginning       7      9       136   152     0     3
Intermediate     
Advanced

The JSON object that will create and fill this dataTable would need to look something like:

[{"program_category":"Beginning","unit_1":{"2015":"7","2014":"9"},
"unit_2":{"2015":"136","2014":"152"},
"unit_3":{"2015":"0","2014":"3"}}]

So far, I've been able to write a SQL query that produces a JSON string that gets me pretty close, but as you can see the, 'program_type' repeats itself for each organizational unit:

Here's the SQL:

 select all_programs_test.program_category as program_category,       report_db.unit,
  sum(case when all_programs_test.year = '2015' then 1 else 0 end) yr_2015,
  sum(case when all_programs_test.year = '2014' then 1 else 0 end) yr_2014
  from all_programs_test
  JOIN report_db on report_db.id = all_programs_test.id
  group by all_programs_test.program_category,report_db.unit

Which I then json_encode in PHP:

while($row = mysql_fetch_assoc($query4)) {
$query4_result[] = array ( 

'program_category' => $row['program_category'],

 $row['unit'] => array(
          '2015' => $row['yr_2015'],
          '2014' => $row['yr_2014']
    )
);

which then produces

[{"program_category":"Beginning","unit_1":{"2015":"7","2014":"9"}},
{"program_category":"Beginning","unit_2":{"2015":"136","2014":"152"}},
{"program_category":"Beginning","unit_3":{"2015":"0","2014":"3"}}]

As you can see in the json object snippet above, 'Beginning' is repeated for each organizational unit. Any ideas on how to get to this instead:

[{"program_category":"Beginning","unit_1":{"2015":"7","2014":"9"},
"unit_2":{"2015":"136","2014":"152"},
"unit_3":{"2015":"0","2014":"3"}}]

Thanks!

Allan · Accepted Answer · 2016-01-06 16:38:39Z

1

Try this:

$category_aux = '';
$record_aux = array();

while($row = mysql_fetch_assoc($query4)) {
    if ($category_aux !== $row['program_category']){
        $category_aux = $row['program_category'];

        if ( ! empty($record_aux)) {
           $query4_result[] = $record_aux;
        }

        $record_aux = array();

        $record_aux['program_category'] = $category_aux;
    }

    $record_aux[$row['unit']] = array(
        '2015' => $row['yr_2015'],
        '2014' => $row['yr_2014']
    );
}

// For the last one.
$query4_result[] = $record_aux;

answered Jan 6, 2016 at 16:38

Allan

2731 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tomish Over a year ago

Hi Allan, just tried your solution and it works great! Thanks so much.

Allen Taylor · Accepted Answer · 2016-01-06 18:03:45Z

0

I have built an static version of your code to try to reproduce your issue however it worked with no issues.

I think the problem may have something to do with your grouping in the query. Can you maybe show us the result of the query?

answered Jan 6, 2016 at 18:03

Allen Taylor

293 bronze badges

Collectives™ on Stack Overflow

Index array element for JSON object

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related