Table join query

[lukep11a]

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.

<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>

[Spookster]

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.

[lukep11a]

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:

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 (tth.teamid = tf.hometeam AND tta.teamid = tf.awayteam)
WHERE ts.userid =6 AND (tf.hometeam = ts.teamid OR tf.awayteam = ts.teamid)
GROUP BY tf.fixtureid 

[lukep11a]

WHERE ts.userid =6 should of been WHERE ts.userid = '{$_SESSION['userid']}'

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

[Spookster]

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.