Most efficient retrieval method

What is the most efficient way to retrieve groups of infomation and print the headings only once. For example, I have a list of physicians (name, phone number) grouped by specialty (anesthesiology, cardiology, etc.). I want to list them by specialty but print the specialty once (first specialty, list of names and phones; second specialty, list of names and phones; etc.). Do I have to reconnect to the database (MySQL), or get a new result each time?

------------------
From the day we're born, we're running out of time.

You can do a join between the physician and the specialty tables... that would result in extra data in the result set but you don't have to use it all.

SELECT physicians.*, specialty.specialty_name FROM physicians, specialty where physician.specialty_id = specialty.specialty_id group by specialty.specialty_id

That way, in the result set, you have the specialty name... as you are reading through the results, when the specialty changes, you print it.



------------------
----------------------
Ulysses J Ludwig
Consultant
Ujludwig@yahoo.com
213.948.8278

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by ujludwig:
You can do a join between the physician and the specialty tables... that would result in extra data in the result set but you don't have to use it all.

SELECT physicians.*, specialty.specialty_name FROM physicians, specialty where physician.specialty_id = specialty.specialty_id group by specialty.specialty_id

That way, in the result set, you have the specialty name... as you are reading through the results, when the specialty changes, you print it.

[/quote]

Well, of course, I didn't explain it well. The existing page is at: http://www.wathosp.org/physicians/p..._directory.php3
Problem is, the specialty and names and phones are in the same table (don't really need a separate table for specialty, though I could make one if I'm wrong).

Here's the stripped down code (without the text formatting, etc.):

<?
$db = mysql_connect("username", "password");
mysql_select_db("databasename",$db);
$result = mysql_query("SELECT * FROM physicians ORDER BY specialty,last",$db);

if ($myrow = mysql_fetch_array($result)) {
printf("<TABLE BORDER=0 BGCOLOR='#FF0000' CELLSPACING=1 CELLPADDING=2 WIDTH=490 >n");

printf("<TR><TD><b>Specialty</b></TD>
<TD>Physician</TD>
<TD>Telephone</TD></TR>n");
do {
printf("<TR><TD>%s</TD>
<TD>%s %s, %s</TD>
<TD>%s</TD></tr>n",
$myrow["specialty"], $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table><br><br>n";
} else {
echo "Sorry, no physicians were found!";
}
mysql_free_result($result);
mysql_close($db);
?>



------------------
From the day we're born, we're running out of time.

Ok, you were 1 step away from what I was getting at... within your loop just check to see if the specialty changes, if it does, print it


do {
if ($myrow["specialty"] != $old_specialty)
{
echo ($myrow["specialty"]);
}
$old_specialty = $myrow["specialty"];

printf("<TR><TD>%s</TD>
<TD>%s %s, %s</TD>
<TD>%s</TD></tr>n",
$myrow["specialty"], $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
} while ($myrow = mysql_fetch_array($result));


------------------
----------------------
Ulysses J Ludwig
Consultant
Ujludwig@yahoo.com
213.948.8278

Thanks! That was it except for an "else" clause (to print the physicians with the specialty field blank). The final code that worked is:

<?
$db = mysql_connect("username", "password");
mysql_select_db("databasename",$db);
$result = mysql_query("SELECT * FROM physicians ORDER BY specialty,last",$db);
if ($myrow = mysql_fetch_array($result)) {
printf("<TABLE BORDER=1 WIDTH=500 >n");
printf("<TR><b>Specialty</b></TD><TD><b>Physician</b></TD>
<TD><b>Telephone</b></TD></TR>n");
do {
if ($myrow["specialty"] != $old_specialty)
{
printf ("<TR><TD>%s</TD><TD>%s %s, %s</TD><TD>%s</TD></tr>", $myrow["specialty"], $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
}
else {
printf ("<TR><TD></TD><TD>%s %s, %s</TD><TD>%s</TD></tr>", $myrow["first"], $myrow["last"], $myrow["degree"], $myrow["phone"]);
}
$old_specialty = $myrow["specialty"];
} while ($myrow = mysql_fetch_array($result));
echo "</table><br><br>n";
} else {
echo "Sorry, no physicians were found!";
}
mysql_free_result($result);
mysql_close($db);
?>

The result can be seen at: http://www.wathosp.org/physicians/p..._directory.php3

------------------
From the day we're born, we're running out of time.