View Full Version : Showing League Table from DB

mjp_uk

03-04-2007, 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 0-0 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

03-04-2007, 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

03-04-2007, 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

03-05-2007, 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

03-07-2007, 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 3-0 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

Powered by vBulletin® Version 4.2.2 Copyright © 2015 vBulletin Solutions, Inc. All rights reserved.