Jump to content

Archived

This topic is now archived and is closed to further replies.

bob_the _builder

Table joins

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
As you want a single image per maincat, add LIMIT 1 to end of the $preview query

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.