#16
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    11
    Rep Power
    0
    Ok, I have made the pages where you can add a league, update, delete, and so forth. I am going to start work on the teams pages today. For the league table would the results for it be created on the fly?
  2. #17
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Originally Posted by d4v1s
    Ok, I have made the pages where you can add a league, update, delete, and so forth. I am going to start work on the teams pages today. For the league table would the results for it be created on the fly?
    I would guess you would have followed the link in my first reply to you by now right?
  4. #18
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    11
    Rep Power
    0
    yes i have...
  6. #19
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Do you understand how to use those queries to adapt your information on wins and losses as well as goals scored and against or would you like some help adapting it to your tables?

    Basically a good rule of thumb is not to store data in your tables that you can already calculate from known information.
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    11
    Rep Power
    0
    Thats the bit Im having difficulties with, so I might need some help. Sorry to be a pain.
  10. #21
  11. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Do a
    Code:
    show create table yourtablename
    for your football table so we can get the column names and see how the table is laid out. Then we can assist you with the dynamic queries.
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    11
    Rep Power
    0
    LEAGUES TABLE BELOW

    Table Create Table
    LeaguesTable CREATE TABLE `LeaguesTable` (\n `LeagueID` int(2) NOT NULL auto_increment,\n `LeagueName` varchar(50) NOT NULL default '',\n PRIMARY KEY (`LeagueID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1



    TEAMS TABLE BELOW

    Table Create Table
    TeamsTable CREATE TABLE `TeamsTable` (\n `TeamID` int(2) NOT NULL auto_increment,\n `TeamName` text NOT NULL,\n `LeagueName` text NOT NULL,\n PRIMARY KEY (`TeamID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1



    FIXTURES TABLE BELOW

    Table Create Table
    FixturesTable CREATE TABLE `FixturesTable` (\n `FixtureID` int(5) NOT NULL auto_increment,\n `League` text NOT NULL,\n `Date` date NOT NULL default '0000-00-00',\n `HomeTeam` text NOT NULL,\n `HomeScore` char(2) NOT NULL default '',\n `AwayTeam` text NOT NULL,\n `AwayScore` char(2) NOT NULL default '',\n `Details` varchar(50) NOT NULL default '',\n PRIMARY KEY (`FixtureID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1
  14. #23
  15. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    A few things before we get started, suggested changes for your tables:

    Don't use a word like Date for a column name. It is a reserved word in mysql, (for some reason they allow some reserved words to be used) but would give you an error elsewhere.

    In the fixtures table you should note/change the following:

    1) don't use text for any of your fields, it is overkill as it holds 65K in size, char or varchar will do

    2) don't identify league in that table with text, identify it by the same id you have in the league table. the same applies to your home and away teams, identify them by the id created for them in the teams tables.

    3) home and away scores shouldn't be identified as char(2) they should be tinyint or smallint (tinyint should do as it will hold values up to 127, unlikely you will have a score that high). using characters when you need numeric data forces the query to convert from a string to a digit to do any calculations and then back to a string to store the info. this results in extra overhead
  16. #24
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    11
    Rep Power
    0
    Originally Posted by Guelphdad
    A few things before we get started, suggested changes for your tables:

    Don't use a word like Date for a column name. It is a reserved word in mysql, (for some reason they allow some reserved words to be used) but would give you an error elsewhere.

    In the fixtures table you should note/change the following:

    1) don't use text for any of your fields, it is overkill as it holds 65K in size, char or varchar will do

    2) don't identify league in that table with text, identify it by the same id you have in the league table. the same applies to your home and away teams, identify them by the id created for them in the teams tables.

    3) home and away scores shouldn't be identified as char(2) they should be tinyint or smallint (tinyint should do as it will hold values up to 127, unlikely you will have a score that high). using characters when you need numeric data forces the query to convert from a string to a digit to do any calculations and then back to a string to store the info. this results in extra overhead
    Thanks I will make these ammendments when Im back, just popping out for afew hours, greatly appreciating all this help.
  18. #25
  19. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Two other small items:

    1) when you create a column and make it a primary key you don't have to label it as NOT NULL, by default primary keys can't be null

    2) I think the details should come out of the fixture table and go into a separate table with the fixtureid and the details as columns.

    it will make lookup of the other information from the match faster IIRC.
  20. #26
  21. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    For the purposes of demonstration, here I've re-created the three tables and filled them with some data:

    sql Code:
    DROP TABLE IF EXISTS Leagues;
     
    CREATE TABLE Leagues (LeagueID tinyint AUTO_INCREMENT, 
    LeagueName VARCHAR(50) NOT NULL DEFAULT '', 
    PRIMARY KEY (LeagueID)) 
    ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
    DROP TABLE IF EXISTS Teams;
    CREATE TABLE `Teams` ( `TeamID` tinyint AUTO_INCREMENT, 
    TeamName text NOT NULL, 
    Leagueid tinyint, 
    PRIMARY KEY (TeamID)) 
    ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
    DROP TABLE IF EXISTS Fixtures;
    CREATE TABLE Fixtures (FixtureID INT(5) AUTO_INCREMENT, 
    League tinyint, 
    GameDate DATE NOT NULL DEFAULT '0000-00-00', 
    HomeTeam tinyint, 
    HomeScore tinyint, 
    AwayTeam tinyint, 
    AwayScore tinyint, 
    Details text, 
    PRIMARY KEY (FixtureID)) 
    ENGINE=MyISAM DEFAULT CHARSET=latin1;
     
     
    INSERT INTO LEAGUES (leagueName) VALUES ('Premiership'),('Championship');
     
     
    INSERT INTO TEAMS VALUES
    (1,'Chelsea',1),
    (2,'Man U.',1),
    (3,'Arsenal',1),
    (4,'Liverpool',1),
    (5,'Reading',2),
    (6,'Sheffield U.',2),
    (7,'Leeds',2),
    (8,'Watford',2);
     
    INSERT INTO FIXTURES VALUES
    (1,1,'2006-03-01',1,2,2,1,'Chelsea jump to top of table with hard fought win over Man U.'),
    (2,1,'2006-03-01',3,2,4,2,'Liverpool score in injury time to draw with Arsenal'),
    (3,2,'2006-03-02',5,2,6,0,'Reading put boots to Sheffield U, dropping them to fourth in the table'),
    (4,2,'2006-03-01',7,1,8,1,'Watford\'s draw against Leeds moves them past Sheffield U. into second spot'),
    (5,2,'2006-03-08',5,5,8,1,'Reading run their undefeated streak to 32 with easy win over Watford'),
    (6,1,'2006-03-08',1,0,4,2,'Liverpool surprise Chelesa at Stamford Bridge');
  22. #27
  23. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    And here is a query that will draw information out of those tables. The leagues are not separated in this example, the query of course can be expanded to include other info. This will build a league table for you:

    sql 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
  24. #28
  25. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    If you (or someone else) has questions about expanding on this with PHP to create a web page I'd be happy to assist. Drop me a PM and we can chat off-line.
  26. #29
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    30
    Rep Power
    4
    Originally Posted by Guelphdad
    If you (or someone else) has questions about expanding on this with PHP to create a web page I'd be happy to assist. Drop me a PM and we can chat off-line.
    I know this is a very old thead but i'm wondering if the code suppllied above by Guelphdad can be altered to allow the league table to be updated from latest score instead of final score.

    I've been trying for a bit to get my head around how to allocate variables so that when a score is updated during a match it takes into account how the score has changed from last entry and adjusts goaldifference and points to suit.

    At the moment all i can only get it to treat the updated score as a new game so if a team is winning 1-0 then it goes to say 3-0 the team gets the 3 goals added instead of 2 (2+1=3). I got the points problem sorted by adding a variable that i switch off after i enter the first score update but goals for/against/difference part is causing me problems

    kenny
  28. #30
  29. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    You're right, it IS an old thread!

    Until the whistle blows, isn't the 'final score' simply the 'latest score' !?!?

IMN logo majestic logo threadwatch logo seochat tools logo