ORDER BY question

[bpgillett]

hi. i'm trying to display users' names in an html table ordered by the number of evaluations they have submited. my table displays: name, number of evals, and date of last eval. i use COUNT() to get the number of evals, and i asign this number to $eval_num. i can't figure out a way to order the users' names in descending order by $eval_count. especially, since i used a while loop. thanks in advance. here's my code. --brian

//get all users' names and id's from 'users' table (a_id is the primary key)
$result = mysql_query("SELECT first_name, last_name, a_id FROM users ORDER BY last_name",$db);
if ($myrow = mysql_fetch_array($result)){

//start html table
echo "<table border=1>\n";
echo "<tr><td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><strong>Attending</strong></font></td>";
echo "<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><strong>Evals</strong></font></td>";
echo "<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\"><strong>Last Eval</strong></font></td>";

do {

//get number of evals for each respective user from evaluations table.  if a_id happens not to be present in the table the count defaults to '0'
$a_id = $myrow["a_id"];
$result2 = mysql_query("SELECT COUNT(1) as eval_num FROM evaluations WHERE a_id = '$a_id'",$db);
$myrow2 = mysql_fetch_array($result2);
$eval_num = $myrow2["eval_num"];

//get date of last eval for each respective user
$result3 = mysql_query("SELECT date FROM evaluations WHERE a_id = '$a_id' ORDER BY date DESC",$db);
if ($myrow3 = mysql_fetch_array($result3)) {
$last_eval = $myrow3["date"];
}else{
$last_eval = "n/a";
}

//output to html table
printf("<tr><td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s, %s</font></td>", $myrow["last_name"], $myrow["first_name"]);
printf("<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></td>", $eval_num);
printf("<td><font color=\"003399\" size=\"2\" face=\"Arial, Helvetica, sans-serif\">%s</font></td>", $last_eval);

} while ($myrow = mysql_fetch_array($result));

//end table
echo "</tr></table>\n";
}

[coreyp_1]

I'm not able to test this, but what about getting all of this information in one query?

$query = "SELECT first_name, last_name, COUNT(date) AS eval_num, MAX(date) AS date
  FROM users u
  LEFT JOIN evaluations USING a_id
  GROUP BY u.a_id
  ORDER BY last_name";

[bpgillett]

thanks for the suggestion. doesn't seem to work...is there a way that i can derive a table consisting of two columns (a_id and eval_num) from my evaluations table then select all from this derived table and order by eval_num? i guess i would then need a way to deal with the users who have not submited evals as they are not listed in the 'evaluations' table (they are only listed in the 'users' table.

--brian