LEFT JOIN Array

luiddog

I have the following statement:

"SELECT *
FROM Name LEFT JOIN Dish ON Name.ID = Dish.DNameID LEFT JOIN Side ON Name.ID = Side.SNameID "

And I get the following:

John - Cake - Fries
John - Coffee - Fries
John - Cake - O Rings
John - Coffee - O Rings
Tom - Cake - Fries
Tom - Coffee - Fries
Tom - Cake - O Rings
Tom - Coffee - O Rings

John
John
John
John
Tom
Tom
Tom
Tom

I need two things:
1)
John - Coffee, Cake - Fries, O Rings
Tom - Coffee, Cake - Fries, O Rings

2)
John
Tom

I've never done an array before.

luiddog

I found I can use this:

"SELECT
FROM Name LEFT JOIN Dish ON Name.ID = Dish.DNameID LEFT JOIN (SELECT FROM Side LIMIT 1) sSide ON Name.ID = sSide.SNameID "

To get:
John
Tom

but that's it

ahundiak

Using your original query, do something like:

$rows = mysql_query($sql);
$data = array();
while($row = mysql_fetch($rows)
{
  $name = $row['name']; // John
  if (!isset($data[$name])) {
    $data[$name] = array();
    $data[$name]['dishes'] = array();
    $data[$name]['sides'] = array();
  }

  $dish = $row['dish'];
  if (!isset($data[$name]['dishes'][$dish])) $data[$name]['dishes'][$dish] = $dish;

  $side = $row['side'];
  if (!isset($data[$name]['sides'][$side])) $data[$name]['sides'][$side] = $side;
}
print_r($data);

From there you should be able to extract the needed info from $data.