Two Queries on single table to output in the same display.

[iainlang]

Hello.

 

Some FOOL told a local charity that I was just the boy to take over their web-site, and an even bigger fool said "Yes" when I was asked to do it.

 

So... I've inherited this charity site whose volunteers do Casework among its clients.  The task is to list the Caseworkers in alphabetical order with the total number of Cases that each has done throughout the past year.  I've tried messing around with GROUP BY and AS and stuff, but I just get deeper and deeper into the Coding Swamp.  Unfortunately, I can't change the MySql database structure.

 

Perhaps one of the gurus here can help?

 

<?php
include("connection_string");
$n="0";
$year=date("Y");
$year--;
$start_date=$year."-".date("m")."-".date("d");
$TableName="personnel";
$Query="SELECT firstname, surname, telephone, mobile, email FROM $TableName WHERE position LIKE \"%Caseworker%\" AND based=\"P\" AND date_case_added >= $start_date ORDER BY surname, firstname ";
$Result=mysql_db_query ($DBName, $Query, $Link);
while ($Row=mysql_fetch_array ($Result))
{
$surname[]=$Row[surname];
$firstname[]=$Row[firstname];
$telephone[]=$Row[telephone];
$mobile[]=$Row[mobile];
$email[]=$Row[email];
$count++;
}
$count--;
$original_count=$count;

while ($n<=$count)
{
$Query="SELECT firstname, surname AS name FROM $TableName WHERE position LIKE \"%Caseworker%\" AND based=\"P\" AND date_case_added >= $start_date GROUP BY name ORDER BY surname, firstname ";
$Result=mysql_db_query ($DBName, $Query, $Link);
$num=mysql_num_rows($Result);
$cases_done[]=$num;
}

$n="0";
print("<table width=\"800\" cellspacing=\"0\" cellpadding=\"2\" border=\"1\">");
print("<colgroup span=\"5\"><col width=\"8%\"><col width=\"30%\"><col width=\"25%\"><col width=\"23%\"><col width=\"17%\"></colgroup>");
while ($n<=$original_count)
{
print("<tr bgcolor=\"#ffffee\"><td>$cases_done[$n]</td><td align=\"right\">$firstname[$n] <strong>$surname[$n]</strong> </td><td align=\"right\">$telephone[$n]</td><td> $mobile[$n]</td><td> <a href=\"mailto:$email[$n]\" style=\"text-decoration:none;\">$email[$n]</a></td></tr>");
$n++;
}
print("</table>");

include("php-lib/footer_admin.php");
?>

 

Any help would be greatly appreciated.

 

TIA.

 

Iain.

(edited by kenrbnsn to add

tags)

[TEENFRONT]

Hey

 

So what are you trying to do again? Got an example of the output that you want to do?  is it like this

 

Adam    4 cases

Barry    5 cases

Caroline 2 cases

 

etc?

 

If so id run the first query to select * from table  blah blah blah to give you $firstname $lastname etc etc

 

then in that query while() loop the 2nd query can just be select * from table where firstname = $firstname then use mysql_num_rows to count the rows per name.

 

The output $firstname  $rows

 

 

[iainlang]

Aaaarrggghh!  I forgot to change the $TableName for the second query; no wonder it wouldn't work.  (I shouldn't be allowed out alone, really...)

 

Thanx, Teenfront.