Query output duplication

[lukep11a]

Hi, I have three tables, one called 'test_selections' which store each registers users team selections, one called 'test_teams' which stores all teams that can possibly be selected, and one called 'test_fixtures' which stores games to be played. The code below selects all fixtures involving teams that the user who is currently logged has selected. The 'test_fixtures' table doesn't contain the actual team names it only holds the teamid (in test_fixtures.hometeam and test_fixtures.awayteam) so I link to 'test_teams' to get the team name (which is stored in test_teams.team).

 

The only problem with this code is that on each output loop, the same team name is being displayed twice rather than the home team once and the away team once. I realise this is because <?php echo $row['team']; ?> is in there twice but I am thinking that maybe I am doing this the wrong way. Does anyone know how I can fix this or am I better off just putting the team names straight into the mysql table rather than the team id? I hope this makes sense.

 

<table width="380" border="0">
	<?php
	$query = "SELECT test_fixtures.competition, test_fixtures.date, test_teams.team FROM test_selections, test_fixtures, test_teams WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam) AND (test_selections.teamid = test_teams.teamid)";
        $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['team']; ?></td>
	<td width="25" class="fixtures_center">v</td>
	<td width="135" class="fixtures_away_teams"><?php echo $row['team']; ?></td>
	</tr>
	<?php
	}
	?>
	</table>

[xyph]

Giving us a database dump with some sample data will help us provide you with a tested query that'll give you the information you need.

[gizmola]

Rewrite your query using the mysql join syntax:  http://dev.mysql.com/doc/refman/5.1/en/join.html.  This will allow you to alias the connection to test_teams with 2 different aliases.  Something like this is what you want to do:

 

 

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

[lukep11a]

thankyou for your reply, that doesn't seem to be working correctly though, it is displaying all the selected teams with every possible combination of opponents, here is some sample data if it helps:

 

test_fixtures

 

competition hometeamawayteamdate

PL 32040770

CH 373240771

CH 412640771

CH 304340771

CH 213140771

CH 362540771

CH 342340771

CH 403840771

 

test_teams

 

teamidteamselectiongroup

21Birmingham 4

22Blackpool 4

23West Ham 4

24Cardiff 4

25Reading 4

26Nott'm Forest 4

27Leeds 4

28Burnley 4

29Millwall 5

 

test_selections

 

userid teamid

11

15

114

123

130

143

146

159

[gizmola]

You should have enough of a hint now to work on this on your own. 

[gizmola]

Also if you really want someone to try this out, make a sql dump of the tables -- people can't be expected to reverse engineer your schema.

[lukep11a]

I am completely for working things out for myself, It is definitely the best way to learn, however the code that you have provided me is so different to the one I originally had that I am not sure where to start. I mean, for a start what is tth.team? i get that tf.competition is team_fixtures.competition but where does tth come from?

[jcbones]

They are aliases of your database tables, just so you don't have to type them out each time you refer to a column.

 

LEFT JOIN (test_fixtures tf, test_teams tth, test_teams tta)
//also written as
LEFT JOIN (test_fixtures AS tf, test_teams AS tth, test_teams AS tta)

[gizmola]

Along with what jcbones wrote, the reason I alias the same table with two seperate join names is so that you can tell which is the home and which is the away team name, which was your original problem as I read it.

 

This page can be daunting to look at, but if you go down to the examples, you'll see similar constructs which might help:  http://dev.mysql.com/doc/refman/5.1/en/join.html