Displaying a league table

[mtgriffiths]

Hey all,

 

I have the following code that i have working in Microsoft access but i cannot change it over to mysql and php.

 

The code is:

 

$Home = mysql_query("SELECT team_details.Team_Name, Sum((If(results.Goals1>results.Goals2,3,If(results.Goals1=results.Goals2,1,0)))) AS Home_Points
FROM results INNER JOIN team_details ON results.Team1=team_details.Team_ID
GROUP BY team_details.Team_Name") or die(mysql_error());

 

It prints out the teams names and the points that they have.

 

Can any body help me in how to change this over?

 

Thanks in advance

 

Matthew

[soycharliente]

The only thing I can suggest is this:

$Home = mysql_query("SELECT team_details.Team_Name, results.Goals1 AS rg1, results.Goals2 AS rg2
FROM results INNER JOIN team_details ON results.Team1=team_details.Team_ID
GROUP BY team_details.Team_Name") or die(mysql_error());

 

Then with PHP, do the if or switch or whatever you want on rg1 and rg2.

[mtgriffiths]

Hi,

 

Same kinda thing now. I have managed to get a system working to show a league for the teams who are in team 1 and a separate table for teams in team2.

 

Problem is i want to combine both these leagues so that it works correctly.

 

The code i am using now is:

 

$Home = mysql_query("SELECT team_details.Team_Name, Sum((If(results.Goals1>results.Goals2,3,If(results.Goals1=results.Goals2,1,0)))) AS Home_Points
FROM results INNER JOIN team_details ON results.Team1=team_details.Team_ID WHERE team_details.League='1' GROUP BY team_details.Team_Name ORDER BY Home_Points DESC") or die(mysql_error());

while($row = mysql_fetch_assoc($Home))
{
echo "<table border='0' width='30%'><tr align='centre'><td align='Left' width='20%'><font color='#FFFFFF' face='Calibri'>";
echo $team_name = $row['Team_Name'];
echo "</td>";
echo "<td width='10%'><td align='Right' width='20%'><font color='#FFFFFF' face='Calibri'>";
echo $home_points = $row['Home_Points'];
echo "</td></tr></table>";
}

$Away = mysql_query("SELECT team_details.Team_Name, Sum(((If(results.Goals2>results.Goals1,3,If(results.Goals1=results.Goals2,1,0))))) AS Away_Points
FROM team_details INNER JOIN results ON team_details.Team_ID = results.Team2 WHERE team_details.League='1' GROUP BY team_details.Team_Name ORDER BY Away_Points DESC") or die(mysql_error());

while($row1 = mysql_fetch_assoc($Away))
{
echo "<table border='0' width='30%'><tr align='centre'><td align='Left' width='20%'><font color='#FFFFFF' face='Calibri'>";
echo $team_name = $row1['Team_Name'];
echo "</td>";
echo "<td width='10%'><td align='Right' width='20%'><font color='#FFFFFF' face='Calibri'>";
echo $Away_points = $row1['Away_Points'];
echo "</td></tr></table>";
}

 

Can anyone see a way i can put $Home and $Away into one query.

 

Thanks again