mysql query within a mysql query

Question

I'm trying to display information from a table in my database in a loop, but for certain information, I'm referencing other tables. When I try to get data from other tables, any data following will disappear. here is the code I am using:

`

    //Below is the SQL query
    $listing = mysql_query("SELECT * FROM Musicians");

    //This is displaying the results of the SQL query
    while($row = mysql_fetch_array($listing))
        {




 ?>
...html here...

 <? echo $row['name']; ?>
 <? echo $row['Town']; ?>

    <?

    $CountyRef = $row['CountyId'];

    $county = mysql_query("SELECT * FROM County WHERE CouInt='$CountyRef'");



    while($row = mysql_fetch_array($county))
        {        
           echo $row['CouName'];

        }

    ?>

       <?php echo $row['instrument']; ?>

       <?php echo $row['style']; ?>`

My problem is that everything after the second while loop is not displaying. Anyone have any suggestions?

Thanks

Answer 1

Second loop should say $row2. $row is being overwritten. Both variables should be named different from each other.

Answer 2

You can acomplish that with a one single query:

SELECT *,
(SELECT CouName FROM County WHERE CouInt=mus.CountyId) as Country
FROM Musicians mus;

You final code should looks like:

<?php
$listing = mysql_query("SELECT *,
(SELECT CouName FROM County WHERE CouInt=mus.CountyId) as Country
FROM Musicians mus;");

//This is displaying the results of the SQL query
while($row = mysql_fetch_assoc($listing))
{


echo $row['name']; 
echo $row['Town']; 
echo $row['Country']; //Thats all folks xD 
echo $row['instrument'];
echo $row['style']; 


 }   ?>

Saludos ;)

Answer 3

And that?:

while($row2 = mysql_fetch_array($county)) {        
       echo $row2['CouName'];
}