Group by or Join needed ?

[pb&j]

hi team. i am unsure if perhaps a "group by" or a "join" would help or if this is rather a php question. i am trying to put information with the same date onto the same table row on the webpage.

events

+----+-------------+----------+----------+
| id | my_date | my_group | my_event |
+----+-------------+----------+----------+
| 1 | 2008-01-01 | ABC | event1 |
+----+-------------+----------+----------+
| 1 | 2008-03-03 | DEF | event2 |
+----+-------------+----------+----------+
| 1 | 2008-03-03 | GHI | event3 |
+----+-------------+----------+----------+
| 1 | 2008-04-04 | DEF | event4 |
+----+-------------+----------+----------+
| 1 | 2008-02-02 | GHI | event5 |
+----+-------------+----------+----------+


$q = "SELECT * FROM events ORDER BY e_date";
$r = @mysqli_query ($dbc, $q);

echo '<table border="1">';
while ($row = mysqli_fetch_array($r)) {
echo '<tr><td>'.$row['my_date'].'</td>';
echo '<td>'; if ($row['my_group'] == "ABC"){echo $row['my_title'];}else{echo 'nada';} echo '</td>';
echo '<td>'; if ($row['my_group'] == "DEF"){echo $row['my_title'];}else{echo 'nada';} echo '</td>';
echo '<td>'; if ($row['my_group'] == "GHI"){echo $row['my_title'];}else{echo 'nada';} echo '</td></tr>';
}
echo '</table>';


Getting now...

+-------------+----------+----------+----------+
| Date | ABC | DEF | GHI |
+-------------+----------+----------+----------+
| 2008-01-01 | event1 | nada | nada |
+-------------+----------+----------+----------+
| 2008-02-02 | nada | nada | event5 |
+-------------+----------+----------+----------+
| 2008-03-03 | nada | event2 | nada |
+-------------+----------+----------+----------+
| 2008-03-03 | nada | nada | event4 |
+-------------+----------+----------+----------+
| 2008-04-04 | nada | event4 | nada |
+-------------+----------+----------+----------+

that returns two seperate rows of 03-03


Wanting ...

+-------------+----------+----------+----------+
| Date | ABC | DEF | GHI |
+-------------+----------+----------+----------+
| 2008-01-01 | event1 | nada | nada |
+-------------+----------+----------+----------+
| 2008-02-02 | nada | nada | event5 |
+-------------+----------+----------+----------+
| 2008-03-03 | nada | event2 | event4 |
+-------------+----------+----------+----------+
| 2008-04-04 | nada | event4 | nada |
+-------------+----------+----------+----------+

where there is only one row for the 03-03 but shows DEF and GHI info.

[awatson]

Don't think there's anything that JOIN or GROUP BY will do for you. Perhaps something using CONCAT() somehow. You're probably better off doing it through the PHP code that displays the results returned by the database query. Perhaps loop through the results putting everything into elements in an array, i.e. $events[date][ABC][DEF][GHI] then loop through the array and output the table.