Query 2 Tables - Please Help

[drax007]

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

[Jack_McSlay]

From what I see, you're acquiring only the food with the foodId from the first result of the first query. the best way to do so is to do both queries at once:

mysql_select_db($database_woodside, $woodside);

$query ="SELECT f.* FROM `foods` f, `favoritefoods` a WHERE f.`FoodID` = a.`FoodID` AND AnimalID='" . $row_animal['AnimalID'] . "'"; 
$newtest3 = mysql_query($query, $woodside) or die(mysql_error());
$row_newtest3 = mysql_fetch_assoc($newtest3);
$totalRows_newtest3 = mysql_num_rows($newtest3);