Combining a few queries into one

[mickmel]

I'm trying to combine a few queries together to (obviously) require my script to make fewer queries.

This will need to combine stuff from three tables. I can handle two, but this is a bit beyond me.

This site will allow users to subscribe to various sports games. When they pull up a page, I want it to show some games (based on other criteria). For each game, it needs to translate the team IDs into their names, but also needs to see if there is an entry for that game subscription already in the database. The result would look something like:

Detroit vs. Atlanta - Subscribe now
Philly vs. LA - Already subscribed
NY vs. Chicago - Subscribe now
etc.

I've removed many of the unnecessary fields from the tables. Here is what we've got:
-----
games
-----
ID
teamone (int)
teamtwo (int)

------
teams
------
ID
teamname (text)

------------
subscriptions
------------
ID
userid
gameid

For the two-part query (turning team IDs into names), I'm using this:

$result = mysql_query("
SELECT gms.teamone as teamone, tms.teamname as teamonename, tms2.teamname as teamtwoname, gms.teamtwo as teamtwo
FROM games gms, teams tms, teams tms2
WHERE gms.teamone = tms.ID AND gms.teamtwo = tms2.ID");

Any thoughts on how this could be done without running a separate query as each row is spit out?

[UFTimmy]

Something like:

SELECT * 
FROM games
LEFT JOIN teams AS teamone ON teamone.id = games.teamone
LEFT JOIN teams AS teamtwo ON teamtwo.id = games.teamtwo
LEFT JOIN subscriptions ON games.id = subscriptions.gameid 

[UFTimmy]

I haven't tested that, but in theory it should work. Note to get at the two team data you'd have to change the select from * to something like teamone.name AS teamonename and the same for teamtwo.

[mickmel]

Ok, that makes sense. How do I get the result, though?

I know I spit out $row["teamone"] or whatever for each variable. But what does the subscription stuff come out as? I imagine I'd do an if-->then to determine what text to print, but what variable would I be doing the if-->then on?

[UFTimmy]

Assuming that the above column names are correct, userid isn't used in any other tables. So you could check to see if $row['userid'] != ''.

So something like:

while( $row = mysql_fetch_array( $result ) )
{
    if( $row['userid'] != '' )
        echo 'Subscribed';
    else
        echo 'Subscribe to this game';
} 

[mickmel]

Cool, thanks. I'll see what I can do!

[mickmel]

Perfect! They've been tweaked to fit my site and they are working great. Thanks so much for your help!

(of course, my brain is about to explode... )