This seems painfully simple to me, but I can't figure it out. I would like to list the names of faculty members from tblFaculty followed by their suffixes from tblSuffixes. For example:
John Smith, M.D.
Barbara Jones, M.D, M.P.H.
etc.
A single faculty member may have more than one suffix, so I need to query the name first, then concatenate the suffixes to appear at the end of the name. My code below, instead of listing only the current faculty member's suffixes, is concatenating every suffix in tblSuffixes. So the first queried name has the correct suffix, then the second has the first name's suffix and it's own, the third has both the first two names and it's own, etc. I don't see why my suffix loop below is not "resetting" in between queries.
Thanks for any help!
Code: Select all
$query = "SELECT fac_id, firstname, lastname FROM tblFaculty ORDER BY lastname";
if(!($result = @ mysql_query($query, $connection)))
showErr();
while($row = mysql_fetch_array($result)) {
$fac_id = $row["fac_id"];
$querySuff = "SELECT fac_id, suffix FROM tblSuffixes WHERE fac_id='$fac_id'";
if(!($resultSuff = @ mysql_query($querySuff, $connection)))
showErr();
while($rowSuff = mysql_fetch_array($resultSuff)) {
$fac_suff .= ", ".$rowSuff["suffix"];
}
echo "<p>".$row["firstname"]." ".$row["lastname"].$fac_suff."</p>";
}