Concat query results into output of a first query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
colossal
Forum Newbie
Posts: 2
Joined: Fri Apr 24, 2009 4:13 pm

Concat query results into output of a first query

Post by colossal »

Hi there,

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>";
    }
 
colossal
Forum Newbie
Posts: 2
Joined: Fri Apr 24, 2009 4:13 pm

Re: Concat query results into output of a first query

Post by colossal »

Well, I supposed I just figured it out. Is this an acceptable solution? This code feels cumbersome.

Clearing $fac_suff before the while loop seems to do the trick.

So, add $fac_suff = ""; to line 12 in the code.
Post Reply