leerobbo Posted January 22, 2012 Share Posted January 22, 2012 Alright all I'm trying to create a league table from scratch using data from one table in my database. The table contains data about the matches such as the scores between who and etc. Using MySQL based on this data I have been able to get the league information for one team however there are numerous teams and the table obviously needs to be sorted by the team with the most points. I am aiming to provide data over various seasons so there will be more than one table, one set of matches and so on. What would be the best approach for this? Should this be solely done within the webpage to display or should an individual table for the League table be created which could then display on the webpage. Any help/advice will be appreciated. Thanks. Quote Link to comment Share on other sites More sharing options...
joel24 Posted January 23, 2012 Share Posted January 23, 2012 have a relational DB with a table for the teams and another table for the results (including year, season etc) i.e. teams [id, name, city, etc, etc] results [team1, team2, winner, score, season, year] Quote Link to comment Share on other sites More sharing options...
leerobbo Posted January 23, 2012 Author Share Posted January 23, 2012 I thought about that however the problem that arose was how could the field in the teams table like name be related to both home and away as they will be playing home and away. Quote Link to comment Share on other sites More sharing options...
joel24 Posted January 23, 2012 Share Posted January 23, 2012 in the results table, have it set up like results [ home (teamID from teams table) away (teamID from teams table) homeScore, awayScore, season, year ] Quote Link to comment Share on other sites More sharing options...
fenway Posted January 24, 2012 Share Posted January 24, 2012 Or, if you don't want to have annoying problems later, save each "side" of the score as a separate row, and tie them together with game ID, and flag as "home" or "away". Quote Link to comment Share on other sites More sharing options...
leerobbo Posted January 24, 2012 Author Share Posted January 24, 2012 In the table for the matches data I do currently numerous fields which are storing information about the shots, shots on target etc and this includes fthg & ftag. They are the full time home goals and full time away goals which I think is what your suggesting? So now link the team from the team table to the home and away fields and then it'd be performing queries on the column values when said team id is home/away? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 24, 2012 Share Posted January 24, 2012 I'm simply stating that you shouldn't store data for two team in one record. Quote Link to comment Share on other sites More sharing options...
kickstart Posted January 25, 2012 Share Posted January 25, 2012 Hi Further to Fenways comment, if you have a single row for the result of a match it makes finding a teams score more difficult as you need to join against either column. Ie, if you have a table of teams TeamId TeamName And a table of scores ScoreId HomeTeamId AwayTeamId HomeTeamScore AwayTeamScore If you want to know all the scores from matches a team has played you land up needing 2 queries unioned together:- SELECT TeamName, HomeTeamScore, AwayTeamScore FROM teams INNER JOIN scores ON teams.TeamId = scores.HomeTeamId UNION SELECT TeamName, AwayTeamScoe, HomeTeamScore FROM teams INNER JOIN scores ON teams.TeamId = scores.AwayTeamId If you have 2 rows on the score table for each match, one for each team you save the need for a UNION. Another option is to create a view based on the scores table to return both combinations of each row. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.