Web Analytics Made Easy -
StatCounter Table join query - CodingForum

Announcement

Collapse
No announcement yet.

Table join query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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
    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
    CodingForum Supreme Overlord
    All Hail Spookster

    Comment


    • #3
      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 

      Comment


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

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

        Comment


        • #5
          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
          CodingForum Supreme Overlord
          All Hail Spookster

          Comment

          Working...
          X