php - Mysql Quering two tables

Hello Guys..

I have two mysql tables one called "ads" and the other called "ads_photo" and what they have in common is the ad_id.

I need to be able to query both tables (maybe a left join?) and grab the top 8 - 10 profile_photos out based on the date.

The columns
Table - ads

ad_id, ad_date
--------------------------------
Table - ads_photo

profile_photo


Just need some help..Thanks!

Did you supply all columns of both tables? There should be some kind of Primary/Foreign key relation tying the photos to the ad_id.

And, yes, a JOIN query would be perfect for this.

BTW.. this is really more of a SQL question than a PHP question. Just sayin'. :)

Thanks for the comment wolf.. The primary key is the ad_id

The reason I posted this in PHP is because I will have to do some type of loop..

Ah, understood. If the PK is ads.ad_id, what is the FK in ads_photo?

Thats correct...

What is correct?

Oh Sorry I'm not sure what the foreign Key would be...

You can't tie a photo in the photo_ads table to an ad in the ads table unless you have something to associate the two. You need a FK in the ads table that is the ad_id associated with the photo.

Anyway, if you do put an FK in the photo_ads table that ties to the ad_id, the query would be something like:

SELECT a.*, b.*
FROM ads a JOIN photo_ads b on b.ad_id = a.ad_id
ORDER BY ad_id


... or something similar.