Help with joining two tables

[dk4210]

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

[trq]

SELECT ad_id, ad_title, ad_photos
FROM ads
LEFT JOIN ad_photos ON (ads.id = ad_photos.ads_id);

[Maq]

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

[dk4210]

Thanks but I am getting the following error

 

Sql error : Column 'ad_id' in field list is ambiguous

[trq]

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);

[dk4210]

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>";

 

 

 

[trq]

Just add the WHERE clause.