Help with JOIN query

[lukep11a]

Hi, I have two tables, one called 'selections' and one called 'teams' structured like this:

 

selections

id

username

section1

section2

section3

 

teams

id

team

group

points

 

I am trying to display team names selected by the user that has logged in from the selections table followed by the points each team has from the teams table. The code below is not quite right, it displays each team name selected by the user repeatedly for each entry in the teams table, any help trying to fix the code below would be much appreciated.

 

<?php

	$query = "SELECT * FROM selections, teams WHERE selections.username = '{$_SESSION['username']}'";
	$result = mysql_query($query) or die(mysql_error());

	while($row = mysql_fetch_assoc($result))
	{
		echo $row['section1']. " - ". $row['points'];
		echo "<br />";
		echo $row['section2']. " - ". $row['points'];
		echo "<br />";
		echo $row['section3']. " - ". $row['points'];
		echo "<br />";
      	}
 	?>

[lukep11a]

I think I need to do something like the code below to specify which teams are pulled out of the 'teams' table:

 

<?php

	$query = "SELECT * FROM selections, teams WHERE selections.username = '{$_SESSION['username']}' AND (selections.section1 = teams.team OR selections.section2 = teams.team OR selections.section3 = teams.team)";
	$result = mysql_query($query) or die(mysql_error());

	while($row = mysql_fetch_assoc($result))
	{
		echo $row['section1']. " - ". $row['points'];
		echo "<br />";
		echo $row['section2']. " - ". $row['points'];
		echo "<br />";
		echo $row['section3']. " - ". $row['points'];
		echo "<br />";
      	}
 	?>

 

But something is still not quite right as this is the result that is displayed:

 

Man Utd - 150

Liverpool - 150

Aston Villa - 150

Man Utd - 180

Liverpool - 180

Aston Villa - 180

Man Utd - 210

Liverpool - 210

Aston Villa - 210

 

Does anybody know how I can amend the code to get each team only once??

[mikesta707]

Mysql joins aren't my strong point, but try using the group by command

 

here is a tutorial on its use: http://www.tizag.com/mysqlTutorial/mysqlgroupby.php

[jcbones]

Are you wanting to the total of all of the points?  ie 150 + 180 + 210? With the name of the team, or do you want the team with a listing of the points?

 

 

[lukep11a]

I was wanting a list of the teams with their points, and then at the bottom I was going to do a SUM to get the total points.

[lukep11a]

And then I wanted to store the total with the users name to use again in an overall table of all users, but don't know if this is possible?