Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Showing League Table from DB

    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
    Last edited by mjp_uk; 03-04-2007 at 03:34 PM. Reason: none

  • #2
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Code:
    $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 ?

  • #3
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ..
    Code:
    <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

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Quote Originally Posted by mjp_uk View Post
    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.

    Code:
    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

  • #5
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    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.

    Code:
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •