PHP Group By with Arrays
By far the most common idiom when using SQL from a web application (PHP or otherwise) is simply listing records. The standard logic looks something like this (give or take real templating):
<?php
$result = mysql_query("SELECT tid, name, size, color FROM things ORDER BY name, size");
print "<table>\n";
print "<tr><th>Name</th> <th>Size</th> <th>Color</th></tr>\n";
while ($record = mysql_fetch_object($result)) {
print "<tr>\n";
print "<td><a href='viewthing.php?tid={$record->tid}'>{$record->name}</a></td>\n";
print "<td>{$record->size}</td>\n";
print "<td>{$record->color}</td>\n";
print "</tr>\n";
}
print "</table>\n";
?>That's all well and good, but in practice can be quite limiting. Why? Because you can't then group records, that is, display not one but several tables, one for each color. SQL, of course, offers a GROUP BY clause. That doesn't do what we want, however. GROUP BY is an aggregate clause, and is used for creating totals and summaries of records. We want to cluster records by a field that is not the ordering field, or a value that is calculated off of the record itself.
I've generally used two different methods for PHP-side grouping, one of them much cleaner and more flexible at the cost of a little performance.