Posted by NC on 09/06/05 20:58

guitarroman...@gmail.com wrote:
>
> I have a 'staff' table with a column 'status', corresponding to a
> 'statuses' table with the appropriate titles (eg 1 | Editor in Chief).
> I want to display on the page the headings (the titles from statuses)
> along with all of the staff members with that status ID.
....
> How can I do this efficiently?

Something like this should work (since I don't know your field
naming scheme, I had to invent my own field names):

$query = <<<EOQ
SELECT
staff.firstname AS firstname,
staff.lastname AS lastname,
staff.status AS status,
statuses.title AS title
FROM staff LEFT JOIN statuses ON staff.status = statuses.id
ORDER BY status
EOQ;
$result = mysql_query($query)
or die('Could not execute query: ' . mysql_error());
$status = null;
while ($record = mysql_fetch_array($result, MYSQL_ASSOC)) {
if ($record['status'] <> $status) {
$title = $record['title'];
echo "<p>Status: $title</p>\r\n";
$status = $record['status'];
}
$name = $record['firstname'] . ' ' . $record['lastname'];
echo "<p style='padding-right:10px'>$name</p>\r\n";
}

Cheers,
NC

[Back to original message]