LEFT JOIN into nested array in php

Question

Im looking for a general solution for such queries:

SELECT * FROM A
LEFT JOIN B on A.ID = B.FK_ID
SELECT * FROM A

LEFT JOIN B on A.ID = B.FK_ID
LEFT JOIN C on B.ID = C.FK_ID

to have it in an array, so joined records would be in a nested arrays. It's very complicated isn't it? All I have is the query and fetched all records

EDIT:

it normally would return

A.col1  A.col2  A.col3  B.col1  B.col2  B.col3

if A has 4 rows and 2 records of B connects to A, A will have 5 lines. Instead, I want this:

A.col1  A.col2  A.col3  B (array)

I'm sorry but what is your question? What is a "joined record"? How do you decide which columns returned by your query are nested and to what depth? There is one column which exists in both tables. — Ben, May 05 '12 at 09:52

Micromega · Accepted Answer · 2012-06-18T07:47:49.940

When you construct the source array slightly different you can use this function.

It's a duplicate (http://stackoverflow.com/questions/8431463/more-efficient-hierarchy-system/8431551) (parent_id,id,title):

$q = mysql_query("SELECT id, parent_id, name FROM categories");
while ($r = mysql_fetch_row($q)) {
  $names[$r[0]] = $r[2];
  $children[$r[0]][] = $r[1];
 }

function render_select($root=0, $level=-1) {
  global $names, $children;
  if ($root != 0)
    echo '<option>' . strrep(' ', $level) . $names[$root] . '</option>';
  foreach ($children[$root] as $child)
    render_select($child, $level+1);
}

echo '<select>';
render_select();
echo '</select>';

LEFT JOIN into nested array in php

1 Answers1