Table joins

[bob_the _builder]

Hi,

Thanks that seems to work, I guess it has to be 2 queries?

I dont really understand when you should use left joins or inner joins etc   :(

A query to show a random image for each maincat from a corrosponding random subcat .. I guess thats hard as there is no post data to make the select from?

Also abit off mysql, should you also clean any data sent across the url like &maincat_id=$_POST['maincat_id'], I guess code can be added to the url to cause damage to the site?

also is (list($photo_filename) away around creating a variable $photo_filename = $row['photo_filename'];?


Thanks

[Barand]

A left join B

- select all recs from A even if there is no matching rec in B


A inner join B

- select only those recs from A and B that match on the join column{s)


Clean data

Never put data straight into a query that originates form the client. That means any data in GET, POST or COOKIE. Always clean it - as a minimum addslashes, if magic_quotes_gpc is off (if it's on it does it for you). You might also want to strip_tags() to prevent javascript and html code from being entered.

[bob_the _builder]

Thanks,

That makes the joins bit more clearer .. Still no luck with the random maincat image. Seems it keeps showing images for maincats that arnt even in a related subcat.

Any ideas?


Cheers

[bob_the _builder]

Hi,

I got it using:

[code]$preview = mysql_query("SELECT i.photo_filename
        FROM gallery_images i
        INNER JOIN gallery_subcat s
        ON i.subcat_id = s.subcat_id
        INNER JOIN gallery_maincat m
        ON m.maincat_id = s.maincat_id
        WHERE m.maincat_id = {$row['maincat_id']}
ORDER BY RAND()") or die(mysql_error());[/code]

had a query prior the the $preview one for he pagination .. so was able to use $row['maincat_id']

[code] $sql = mysql_query("SELECT * FROM gallery_maincat ORDER BY maincat_id DESC LIMIT $from, $max_results");[/code]

Is this a reasonable way to go about it?


Thanks

[Barand]

As you want a single image per maincat, add LIMIT 1 to end of the $preview query