View Full Version : Showing League Table from DB
mjp_uk 03042007, 04:15 PM I am sure this has probably been covered a million times on here before  I am but a novice compared to most on here .... What I am trying to do is show a league table from my sql db . The problem i seem to have other than not knowing much commands is that my db already has the full fixture list in with 00 scores etc , so I am not just trying to select 'Counts' from results as they are put in.
The fields I have are : date,home,hscore,hpoints,vs,away,apoints,played ( where played is just a numeric of '1' if played )
i really need help on this if anybody is willing to point me in the right direction.
Many Thanks
mjp_uk 03042007, 04:34 PM I want to display a table with the following headers :
Name , Plyd , legsw , legsl, diff, Points.
I have come up so far with the following code , but only takes into account 'home' names
$sql = 'SELECT home , SUM( hscore ) , SUM( ascore ) , SUM( hpoints ) , SUM( apoints ) , COUNT( played ) '
. ' FROM fixturesdiv1 '
. ' GROUP BY home LIMIT 0, 30 ; ';
Obviously i need to work on home and away fixtures and relevant results and also how o display the difference between legsw and legsl , can this be done by an extra db field ?
mjp_uk 03042007, 05:47 PM I have added a won and lost field to simplify code needed to work out if its a win or loss  can simply put a 1 into db for a win record
Got to this stage with code so far , but plenty to change i feel ..
<TABLE ALIGN="center" BORDER="4" CELLSPACING="0" CELLPADDING="0" WIDTH="80%">
<tr><th>PLAYER</th><th>PLAYED</th><th>WON</th><th>LOST</th><th>LEGS FOR</th><th>LEGS AGAINST</th><th>DIFF</th><th>POINTS</th></tr>
<?
/* declare some relevant variables */
$DBhost = "localhost";
$DBuser = "******";
$DBpass = "******";
$DBName = "******";
$table = "fixturesdiv1";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");
mysql_select_db("$DBName") or die("Unable to select database $DBName");
$sql = 'SELECT home , SUM( played ) , SUM( won ) , SUM( lost ) , SUM( hscore ) , SUM( ascore ) , SUM( hpoints ) , SUM( apoints ) '
. ' FROM fixturesdiv1 '
. ' GROUP BY home '
. ' ORDER BY apoints DESC LIMIT 0, 30 ; ';
if (!$result = mysql_query($sql))
{
die('<b>Error:</b> could not query database');
}
while ($row = mysql_fetch_assoc($result))
{
echo '<tr>';
foreach ($row as $r)
echo '<td align="center">' . $r . '</td>';
echo "</tr>\n";
}
?>
</table>
HELP is still desperately needed :)
guelphdad 03052007, 03:54 PM The fields I have are : date,home,hscore,hpoints,vs,away,apoints,played ( where played is just a numeric of '1' if played )
you don't need a played field unless it is possible a game is not played on the date scheduled. Otherwise if your date has passed then the game has been played.
also what is the vs column for? and did you forget to type ascore?
what are the points columns for?
in your additional post you don't say what legsw or legsl stand for either.
here is something you might be able to adapt.
There is a separate team table with a teamid and teamname field and then the main table with
hometeam,
homescore,
awayteam,
awayscore
You end up with a games played, wins, losses, ties, points in the final table.
SELECT
t.teamname as Tm
, (sum(CASE WHEN (
g.hometeam = t.teamid AND g.homescore > g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore > g.homescore
) THEN 3 ELSE 0 END) + sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) AND g.homescore = g.awayscore THEN 1 ELSE 0 END)) AS P
, sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (
g.hometeam = t.teamid AND g.homescore > g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore > g.homescore
) THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) AND g.homescore = g.awayscore THEN 1 ELSE 0 END) AS D
, SUM(CASE WHEN (
g.hometeam = t.teamid AND g.homescore < g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore < g.homescore
) THEN 1 ELSE 0 END) AS L
, SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.homescore
WHEN (g.awayteam = t.teamid) THEN g.awayscore
END) as GF
, SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.awayscore
WHEN (g.awayteam = t.teamid) THEN g.homescore
END) as GA
, (SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.homescore
WHEN (g.awayteam = t.teamid) THEN g.awayscore
END)  SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.awayscore
WHEN (g.awayteam = t.teamid) THEN g.homescore
END)) as GD
from teams t
left join fixtures g
on t.teamid in (g.hometeam,g.awayteam)
GROUP BY t.teamname
ORDER BY P desc, GP desc
mjp_uk 03072007, 07:09 PM you don't need a played field unless it is possible a game is not played on the date scheduled. Otherwise if your date has passed then the game has been played.
also what is the vs column for? and did you forget to type ascore?
what are the points columns for?
in your additional post you don't say what legsw or legsl stand for either.
here is something you might be able to adapt.
There is a separate team table with a teamid and teamname field and then the main table with
hometeam,
homescore,
awayteam,
awayscore
You end up with a games played, wins, losses, ties, points in the final table.
SELECT
t.teamname as Tm
, (sum(CASE WHEN (
g.hometeam = t.teamid AND g.homescore > g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore > g.homescore
) THEN 3 ELSE 0 END) + sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) AND g.homescore = g.awayscore THEN 1 ELSE 0 END)) AS P
, sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (
g.hometeam = t.teamid AND g.homescore > g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore > g.homescore
) THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) AND g.homescore = g.awayscore THEN 1 ELSE 0 END) AS D
, SUM(CASE WHEN (
g.hometeam = t.teamid AND g.homescore < g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore < g.homescore
) THEN 1 ELSE 0 END) AS L
, SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.homescore
WHEN (g.awayteam = t.teamid) THEN g.awayscore
END) as GF
, SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.awayscore
WHEN (g.awayteam = t.teamid) THEN g.homescore
END) as GA
, (SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.homescore
WHEN (g.awayteam = t.teamid) THEN g.awayscore
END)  SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.awayscore
WHEN (g.awayteam = t.teamid) THEN g.homescore
END)) as GD
from teams t
left join fixtures g
on t.teamid in (g.hometeam,g.awayteam)
GROUP BY t.teamname
ORDER BY P desc, GP desc
Hi and thanks for the reply .... been away so only just seen it.
Let me try answer your questions.
Basically the league is based on a 1 vs 1 game where it is the first to win 3 legs. i.e. the legsw , legsl columns
The table with fixtures and relevant columns set all to zero is already established and edited as games are played. The games may not be played on the set dates but are updated on a daily basis.
The scoring is slightly different to what you would imagine too whereby a win is worth 3 points but also a point is awarded for every leg won with an additional point for a 30 win. This is irrespective anyway as all that is needed is a Count / Sum of the legsw and legsl aswell as the already entered points.
The date and 'vs' columns are also just their for showing when calling upon a list of fixtures , making it easier just to call them columns.
Regards
Mike

