How do I use two mysql tables in one statement

Question

So, I've put in a favourite images table, and I can't figure out how to get it working properly

Basically, this is the setup:

ImageFavs
FavID, UserID, ImgID

ImageList
ImgID, SiteID

I'd like it to select 20 or so images from the favourites table, but only ones that match the siteid in the image list table

This is the code I have at the moment, but it dawned on me it'd select 20 images from favourites, then only display them if the matching site was actually checked.

#select matching sites
for($i=0;$i<count($sites)-1;$i++){
    $siteinfo = explode("-",$sites[$i]);
    $siteid = $siteinfo[0];
    $sitegroup = $siteinfo[1];
    $selection[$siteid]="exists";
    if($i!=0){
        $sqlextra .= " OR ";
    }
    else{
        $sqlextra = "AND (";
    }
    $sqlextra .= "SiteID='".$siteid."'";
}
if(!empty($sqlextra)){
    $sqlextra .= ")";
}
else{
    $sqlextra = "AND SiteID='-1'";
}

#show favourites
if($_GET['f']==1){
    $sql="SELECT * FROM ImageFavs WHERE UserID='".$_SESSION['User_ID']."' AND Active = '1' ORDER BY RAND() LIMIT 20";
    #(rand is just me being lazy, eventually I'll figure out how to separate it onto pages)
    $result = mysql_query($sql);
    $num = mysql_numrows($result);
    if($num>0){
        while ($row=mysql_fetch_array($result, MYSQL_BOTH)){
            if(empty($sqlextra2)){
                $sqlextra2 = " AND (";
            }
            else{
                $sqlextra2 .= " OR ";
            }
            $sqlextra2 .= "ImgID='".$row['ImgID']."'";
        }
        $sqlextra2 .= ")";
    }
}
#don't show favourites
if(empty($sqlextra2)){
    $sqlextra2 = " ORDER BY RAND() LIMIT 20";
}
$sql="SELECT * FROM ImageList WHERE Valid='1' ".$sqlextra.$sqlextra2;

This output $sql from this seems like it could be so much neater though, an example of it is like this

SELECT * FROM ImageList WHERE Valid='1' AND (SiteID='6') AND (ImgID='5634' OR ImgID='8229' OR ImgID='9093' OR ImgID='5727' OR ImgID='7361' OR ImgID='5607' OR ImgID='7131' OR ImgID='5785' OR ImgID='7339' OR ImgID='5849' OR ImgID='7312' OR ImgID='5641' OR ImgID='8921' OR ImgID='7516' OR ImgID='7284' OR ImgID='5873' OR ImgID='8905' OR ImgID='7349' OR ImgID='7392' OR ImgID='8725')

Also, while I'm here, would there be a non resource heavy way to count the number of favourites for a user per website?

It's not for anything big, just messing around on a personal website to see what I can do.

score 1 · Answer 1 · answered Jan 31 '14 at 00:55

1

you want to use "JOIN"

SELECT * FROM ImageFavs LEFT JOIN ImageList ON ImageFavs.ImgID = ImageList.ImgID WHERE ImageList.SiteID = <your_site_id>

answered Jan 31 '14 at 00:55

arieljuod

15,460
2
25
36

Thanks man, I replied to yours but someone else posted while I was writing and it added the reply to theirs aha, but yeah, it works fine, cheers, might select the other guy for best answer though as i learned a bit from it :) – Peter Jan 31 '14 at 01:22

Machavity · Accepted Answer · 2014-01-31T03:15:40.760

1

You can INNER JOIN your two tables to get the results you want. INNER is used when you want results from both tables. It's best to use aliases to keep your tables straight.

SELECT l.*
FROM ImageFavs f
    INNER JOIN ImageList l ON f.ImgID = l.ImgID
WHERE l.SiteID = [your site ID]
    AND f.UserID='" . $_SESSION['User_ID'] . "' 
    AND f.Active = '1' 
    ORDER BY RAND() LIMIT 20

To get a count by site you can use GROUP BY. I think this should get you that count

SELECT COUNT(f.ImgID)
FROM ImageFavs f
    INNER JOIN ImageList l ON f.ImgID = l.ImgID
WHERE f.UserID='" . $_SESSION['User_ID'] . "' 
    AND f.Active = '1' 
GROUP BY l.SiteID

edited Jan 31 '14 at 03:15

answered Jan 31 '14 at 00:56

Machavity

30,841
27
92
100

Thanks man, you kinda made it easy to understand so I think I'll be able to do similar things from now on :) – Peter Jan 31 '14 at 01:26
Oh, one more thing, I tried to count the values and it gave an error, so I looked it up and found you need to use group by. I've tried 'GROUP BY l.ImgID, l.SiteID, f.FavID' and a few other combinations but it doesn't work, would you happen to know how to do this? – Peter Jan 31 '14 at 01:51
You want to know how many records overall or how many records returned? – Machavity Jan 31 '14 at 01:56
Basically how many favourites the current user has for each website, it seems basically the same sql statement, aside from I can't get it to count the matching rows – Peter Jan 31 '14 at 02:14

score 1 · Answer 3 · answered Jan 31 '14 at 01:08

1

This works-

//Assuming $site_id contains the site ID/
$query = "select *.IF from ImageFavs as IF, ImageList as IL where IL.ImgId = IF.ImgId and IL.SiteId = $site_id LIMIT 20"

answered Jan 31 '14 at 01:08

Kamehameha

5,423
1
23
28

Ah thanks man, got it working, will this also be fairly light to run 7 times per page load for the counting part? – Peter Jan 31 '14 at 01:11
I think it should.It's simple and readable. Performance-wise, sub-query vs Joins in MySql seems to be the same for simple queries. Refer [this](http://stackoverflow.com/questions/2577174/join-vs-sub-query) – Kamehameha Jan 31 '14 at 01:18

How do I use two mysql tables in one statement

3 Answers3