SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict mickmel's Avatar
    Join Date
    Feb 2001
    Location
    Marietta, GA
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Combining a few queries into one

    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:
    Code:
    $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?
    MickMel.com - My portfolio, etc.
    @mickmel - Twitter
    Google Earth Hacks - Fun stuff for Google Earth.

  2. #2
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like:

    PHP Code:
    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 

  3. #3
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict mickmel's Avatar
    Join Date
    Feb 2001
    Location
    Marietta, GA
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    MickMel.com - My portfolio, etc.
    @mickmel - Twitter
    Google Earth Hacks - Fun stuff for Google Earth.

  5. #5
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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


  6. #6
    SitePoint Addict mickmel's Avatar
    Join Date
    Feb 2001
    Location
    Marietta, GA
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, thanks. I'll see what I can do!
    MickMel.com - My portfolio, etc.
    @mickmel - Twitter
    Google Earth Hacks - Fun stuff for Google Earth.

  7. #7
    SitePoint Addict mickmel's Avatar
    Join Date
    Feb 2001
    Location
    Marietta, GA
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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... )
    MickMel.com - My portfolio, etc.
    @mickmel - Twitter
    Google Earth Hacks - Fun stuff for Google Earth.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •