Two MySQL result sets to a nested array

Question

I'm having two tables of data "Item" and "Subsidiary" with the following structure:

ITEM ItmCod ItmName

SUBSIDIARY ItmCodParent ItmCodChild

I need to show a list of Items each with a list of its subsidiaries, like in this json:

{
   "ItmCod":1,
   "ItmName":"BogusItem1",
   "Subsidiaries":
   [
      {
         "ItmCodParent":1,
         "ItmCodChild":15
      },{
         "ItmCodParent":1,
         "ItmCodChild":16      
      }
   ]
},{
   "ItmCod":2,
   "ItmName":"BogusItem2",
   "Subsidiaries":
   [
      {
         "ItmCodParent":2,
         "ItmCodChild":17
      },{
         "ItmCodParent":2,
         "ItmCodChild":18      
      }
   ]
}

How can I add the second result set to the first one to have the nested as shown above. I have this code so far:

$sql = "SELECT ItmCod, ItmName FROM item";

$item_rows = array();

while($item_row = $database->fetch_array_assoc($item_result)){

   $sub_sql = "SELECT ItmCodParent, ItmCodChild FROM subsidiary WHERE subsidiary.ItmCodParent = " . $item_row["ItmCod"];

   $sub_result = $database->query($sub_sql);

   $sub_rows = array();

   while($sub_row = $database->fetch_array_assoc($sub_result)){
      $sub_rows[] = $sub_row;
   }

   $item_rows[] = $item_row;
}

print json_encode($item_rows);

Thanks.

Take a look at stackoverflow.com/questions/9241800/…

bestprogrammerintheworld
– bestprogrammerintheworld

2013-04-05 22:09:47 +00:00
Commented Apr 5, 2013 at 22:09 — bestprogrammerintheworld
– bestprogrammerintheworld, Commented Apr 5, 2013 at 22:09

Jibran K · Accepted Answer · 2013-04-05 22:13:46Z

2

just above the line

$item_rows[] = $item_row;

simply add

$item_row['Subsidiaries']=$sub_rows;

answered Apr 5, 2013 at 22:13

Jibran K

8857 silver badges20 bronze badges

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

Comments

Mike Brant · Accepted Answer · 2013-04-05 22:18:46Z

1

I would do a single join query like this:

SELECT i.ItmCod AS ItmCod, i.ItmName AS ItmName, s.ItmCodChild AS ItmCodChild
FROM item AS i
INNER JOIN subsidiary AS s
   ON i.ItmCod = s.ItmCodParent

Note I didn't select s.ItmCodParent as this is just redundant to i.ItmCod.

Then build the array like this:

$item_rows = array();
while($item_row = $database->fetch_array_assoc($item_result)){
   $item_rows[(int)$item_row['ItmCod']]['ItmCod'] = $item_row['ItmCod'];
   $item_rows[(int)$item_row['ItmCod']]['ItmName'] = $item_row['ItmCod'];
   $sub_array = array(
       'ItdCodParent' => $item_row['ItmCod'],
       'ItmCodChild' => $item_row['ItmCodChild']
   );
   $item_rows[(int)$item_row['ItmCod']]['Subsidiaries'][] = $sub_array;
}
$item_rows = array_values($item_rows); // reset numerical indexes.
echo json_encode($item_rows);

answered Apr 5, 2013 at 22:18

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

1 Comment

Keyslinger Over a year ago

I tried this solution and lumped all my results from the second query inside the first result from the first query.

mrbanzai · Accepted Answer · 2013-04-05 22:23:32Z

I wouldn't attempt to solve this with two queries:

$sql = '
    SELECT I.ItmCod, I.ItmName, S.ItmCodChild
    FROM item I
    LEFT JOIN subsidiary S ON (S.ItmCodParent = I.ItmCod)
';

// fetch $item_result with $sql

$item_rows = array();
while ($item_row = $database->fetch_array_assoc($item_result)) {
  $cod = $item_row['ItmCod'];
  if (!array_key_exists($cod, $item_rows)) {
    $item_rows[$cod] = $item_row;
  }

  $item_rows[$cod]['Subsidiaries'] = array(
    'ItmCodParent' => $cod,
    'ItmCodChild' => $item_row['ItmCodChild'],
  );
}

// array_values is because json_encode will keep the keys
// otherwise
print json_encode(array_values($item_rows));

That way, you aren't running an additional query for every single item row to get the subsidiaries (minimizing round-trip time, and letting the database do what it's good at).

Collectives™ on Stack Overflow

Two MySQL result sets to a nested array

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related