Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    46
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Table join query

    Hi, I have a fantasy football website, and on a user account page I want to display fixtures that are coming up that include teams that the current user has chosen. My test_teams table stores all the team names and their teamid. The test_selections table is where each users team selections are stored, it has two columns, userid and teamid. The test_fixtures table has two columns, hometeam and awayteam, these two cloumns hold the teamid of the teams that are playing.

    I am really struggling writing the code. I have got the code below that seems to be selecting the users teams correctly but then is matching them up with every possible combination from test_teams table and is not picking the correct fixtures up from the test_fixtures table. Any help would be very much appreciated. Thanks in advance.

    PHP Code:
    <table width="635" border="0">
            <?php
            $query 
    "SELECT tf.competition, tf.date, tth.team as hometeam, tta.team as awayteam
    FROM test_selections ts
    LEFT JOIN (test_fixtures tf, test_teams tth, test_teams tta)
    ON (ts.userid = '{$_SESSION['userid']}' AND 
    (tf.hometeam = ts.teamid AND tth.teamid = tf.hometeam) OR 
    (tf.awayteam = ts.teamid AND tta.teamid = tf.awayteam))"
    ;
            
    $result mysql_query($query) or die(mysql_error());
            
            while(
    $row mysql_fetch_assoc($result))
            {
            
    ?>
            <tr>
            <td width="85" class="fixtures_date"><?php echo $row['date']; ?></td>
            <td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td>
            <td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td>
            <td width="25" class="fixtures_center">v</td>
            <td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td>
            </tr>
            <?php
            
    }
            
    ?>
            </table>

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    I don't have enough time to answer this properly but I might suggest when writing queries for MySQL especially complicated ones it is a good idea to run your query against the database using phpMyAdmin before you try to integrate into your code. This way you can make sure it is correct before you start adding more things that could go wrong.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • Users who have thanked Spookster for this post:

    lukep11a (08-22-2011)

  • #3
    New Coder
    Join Date
    Jul 2011
    Posts
    46
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks, great bit of advice. I've been trying to sort this out for over a week now and finally done it! Once I got it into phpmyadmin I could break it down much more easily and try out different combinations much quicker!! Just incase you are interested, this is the correct code:

    PHP Code:
    SELECT tf.competitiontf.datetth.team as hometeamtta.team as awayteam 
    FROM test_selections ts 
    LEFT JOIN 
    (test_fixtures tftest_teams tthtest_teams tta
    ON (tth.teamid tf.hometeam AND tta.teamid tf.awayteam)
    WHERE ts.userid =AND (tf.hometeam ts.teamid OR tf.awayteam ts.teamid)
    GROUP BY tf.fixtureid 

  • #4
    New Coder
    Join Date
    Jul 2011
    Posts
    46
    Thanks
    12
    Thanked 0 Times in 0 Posts
    WHERE ts.userid =6 should of been WHERE ts.userid = '{$_SESSION['userid']}'

    WHERE ts.userid =6 was how I was testing it in phpmyadmin!!

  • #5
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,280
    Thanks
    4
    Thanked 83 Times in 82 Posts
    Glad that helped you. These days I run all my queries through it first to make sure I will get the data I was intending.

    And another thing I sometimes do if I run into problems with my query and my query depends upon some value being given to it from one more more other variable, array, etc is to just print the SQL string to the screen. Then I can copy the query from the page after the variable, array, etc has been parsed so I can see if the value I am passing to the query is correct or not. Then I paste that into phpMyAdmin to see if it returns the result I expect.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster


  •  

    Posting Permissions

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