Using MAX() in a Loop

[iainlang]

 

I've inherited the database of a charity whose volunteeers do Casework with the clients, and, as Caseworkers can occasionally be tackling more than one case at a time, I need to know the last Case number done by each Caseworker.  I've never worked with MySQL's MAX() before and I'm haviing notta lotta luck.  My (pretty naive, I guess) code follows.

 

The "personnel" fields include - firstname, surname, based, position, telephone, mobile, email

The "clients" fields include - caseworker_firstname, caseworker_surname, case_number

 

 

$count=1;

$TableName="personnel";

$Query="SELECT * FROM $TableName WHERE based=\"P\" AND position LIKE \"%Caseworker%\" ORDER BY surname desc, firstname desc";

$Result=mysql_db_query ($DBName, $Query, $Link);

while ($Row=mysql_fetch_array ($Result))

{

$firstname[]=$Row[firstname];

$surname[]=$Row[surname];

$telephone[]=$Row[telephone];

$mobile[]=$Row[mobile];

$email[]=$Row;

$count++;

}

 

$original_count=$count;

 

print("<table width=\"900\" cellspacing=\"0\" cellpadding=\"2\" border=\"1\">");

print("<colgroup span=\"6\"><col width=\"17%\"><col width=\"5%\"><col width=\"14%\"><col width=\"14%\"><col width=\"14%\"><col width=\"35%\"></colgroup>");

print("<tr bgcolor=\"#ffffee\" align=\"center\"><td>Caseworker</td><td>Total</td><td>Last Case done</td><td>Telephone</td><td>Mobile</td><td>e-mail</td></tr>");

 

$TableName="clients";

while ($count>0)

{

$Query="SELECT max(case_number) AS maximum FROM $TableName WHERE caseworker_surname='$surname[$count]' AND caseworker_firstname='$firstname[$count]' ORDER BY caseworker_surname desc, caseworker_firstname desc";

$Result=mysql_db_query ($DBName, $Query, $Link);

while ($Row=mysql_fetch_array ($Result))

{

$maximum[]=$Row[maximum];

}

$count--;

}

 

$count=$original_count;

while ($count > 0)

{

print("<tr bgcolor=\"#ffffee\"><td align=\"right\">$firstname[$count] $surname[$count] </td><td>$count</td><td><a href=\"cli_update_handle.php\">$last_case_number[$count]</a></td><td align=\"right\">$telephone[$count]</td><td align=\"right\">$mobile[$count]</td><td align=\"right\"><a href=\"mailto:$email[count]\">$email[$count]</a></td></tr>");

$count--;

}

print("</table>");

 

Any help would be appreciated.

 

Thanx,

 

Iain.