Query 2 Tables

[yandoo]

Hi there,

 

Im having a bit of trouble with my php and was hopnig for a little help.

 

I have 2 SELECT queries that search tables in my database and display the results.....

 

Heres what I have:

mysql_select_db($database_woodside, $woodside);
$query_testfood = "SELECT * FROM favouritefoods WHERE AnimalID='" . $row_animal['AnimalID'] . "'";
$testfood = mysql_query($query_testfood, $woodside) or die(mysql_error());
$row_testfood = mysql_fetch_assoc($testfood);
$totalRows_testfood = mysql_num_rows($testfood);

$query_newtest3="SELECT * FROM  foods WHERE FoodID='" . $row_testfood['FoodID'] . "'"; 
$newtest3 = mysql_query($query_newtest3, $woodside) or die(mysql_error());
$row_newtest3 = mysql_fetch_assoc($newtest3);
$totalRows_newtest3 = mysql_num_rows($newtest3);

 

The first query searches a table and finds all the favouritefoods for a specific animal using (the AnimalID of previous query).....With these new known food ID's  it then searches another table to get the NAMES of favouritefoods to displays the names and NOT the ID numbers....back

 

Im getting wierd results in that all the Names of the favouritefoods that are displayed, are the SAME?? (ONLY EVER THE FIRST ID RECORD NAME)This this strange because i can confirm that the ID's are correct .....

 

I think that it may be that the 2nd query that searches the 2nd table($newtest3) that it ONLY uses the FIRST ID number (of the result of the 1sy query) and NOT all of them as needed.  I would guess only the first would be used by the 2nd table????

 

I need the 2nd query to search the 2nd table with ALL the ID's (FoodID) from the 1st query!

 

I hope im explaning this ok..??

 

If anybody could help me that would be ace!

 

Kind Regards 

 

 

 

[Barand]

assuming the food name column in the foods table is "foodName"

$query_testfood = "SELECT fav.FoodID, f.foodName 
            FROM favouritefoods fav
            INNER JOIN foods f ON fav.FoodID = f.FoodID            
            WHERE fav.AnimalID='" . $row_animal['AnimalID'] . "'";

[jonsjava]

you need to run a Join statement to do this right. I hate them, so I am going to let someone who loves MySQL answer this in full for you.

 

**looks like someone already did.