dk4210 Posted August 16, 2011 Share Posted August 16, 2011 Hello Guys, I am trying to do a some sort of join with a while loop and I am not sure how to do it.. I have 2 tables "ads" and "ads_photo" and I would like to pull the following data out of the tables ad_id and ad_title from the "ads" table The ad_photos from the "ads_photo" table the "ad_photo table id has to match the id from the "ads" table Then i need to display a while loop. Here is what I have so far $result = mysql_query("SELECT * FROM ads WHERE ad_pcat=$cid2 AND approved='1'") or die("Sql error : " . mysql_error()); while($row = mysql_fetch_assoc ($result)){ $ad_id=$row["ad_id"]; $ad_title = $row['ad_title']; echo "<img src=\"$ad_photo\">This is the ad_title". $ad_title."ID-$ad_id"."<br>"; Please advise Quote Link to comment Share on other sites More sharing options...
trq Posted August 16, 2011 Share Posted August 16, 2011 SELECT ad_id, ad_title, ad_photos FROM ads LEFT JOIN ad_photos ON (ads.id = ad_photos.ads_id); Quote Link to comment Share on other sites More sharing options...
Maq Posted August 16, 2011 Share Posted August 16, 2011 Look at the manual for JOIN, there are plenty of examples on how to do this: http://dev.mysql.com/doc/refman/5.0/en/join.html Quote Link to comment Share on other sites More sharing options...
dk4210 Posted August 16, 2011 Author Share Posted August 16, 2011 Thanks but I am getting the following error Sql error : Column 'ad_id' in field list is ambiguous Quote Link to comment Share on other sites More sharing options...
trq Posted August 16, 2011 Share Posted August 16, 2011 This means that both your tables have a column called ad_id. You should be able to do: SELECT ads.ad_id, ad_title, ad_photos FROM ads LEFT JOIN ad_photos USING (ad_id); Quote Link to comment Share on other sites More sharing options...
dk4210 Posted August 16, 2011 Author Share Posted August 16, 2011 OK Thorpe it's almost all working except one part It should only display the ads with the ad_pcat=$cid AND approved='1' How would I add that in the current code? I tried but received an error $result = mysql_query("SELECT * FROM ads LEFT JOIN ads_photo USING (ad_id);") or die("Sql error : " . mysql_error()); //$result = mysql_query("SELECT * FROM ads WHERE ad_pcat=$cid2 AND approved='1'") or die("Sql error : " . mysql_error()); while($row = mysql_fetch_assoc ($result)){ $ad_id=$row["ad_id"]; $ad_title = $row['ad_title']; $ad_photo = $row['ad_photos']; echo "This is the ad photo" . $ad_photo; echo "<img src=\"$ad_photo\">This is the ad_title ". $ad_title."ID-$ad_id"."<br>"; Quote Link to comment Share on other sites More sharing options...
trq Posted August 16, 2011 Share Posted August 16, 2011 Just add the WHERE clause. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.