I am trying to create a soccer results matrix from an existing database (tables and current query are at the bottom of the post) and am not sure where to start with it.
It is a bit hard to explain (unless you are a soccer fan where these results matrix's or grids are very common) but will give it a go!
Basically, I need to create a grid where the rows relate to the Home Teams and the columns relate to the Visiting Teams. In the corresponding cell I need to put the score of that game (I will also want to put in the date if the game has not been played but should be able to sort that once I have the basics sorted).
For example, if the result of a game was United 2 City 0, the cell of the United row and the City column should show 2-0. An example of what I am trying to achive can be found at http://en.wikipedia.org/wiki/2009%E2%80%9310_Premier_League#Results.
As I said, I already have an existing database that I want to use and have an idea how it should work but really need a starting point on how to go about it. My guess is that it would need to loop through the Home Teams (alphabetically is standard for these grids) and for each Home Team then loop through the Away Teams and then place the correct result
in that cell for the game where the two teams match. If the Home Team = Away Team then something else needs to be shown (usually an X or preferably a black cell) as a Team cannot play itself.
Any suggestions on how I could start this would be greatly appreciated.
Thanks in advance
Steve
The tables and sample data (these are not the full tables, just the relevant fields)
CREATE TABLE `all_games` (
`all_games_id` int(11) NOT NULL auto_increment,
`date` date default NULL,
`time` time default NULL,
`home_team` int(11) NOT NULL default NULL,
`away_team` int(11) NOT NULL default NULL,
`home_goals` int(11) default NULL,
`away_goals` int(11) default NULL,
PRIMARY KEY (`all_games_id`)
)
INSERT INTO `all_games` VALUES (1, '2009-08-15', '15:00:00', 19, 42, 4, 0);
INSERT INTO `all_games` VALUES (2, '2009-08-18', '19:45:00', 42, 29, 0, 4);
INSERT INTO `all_games` VALUES (3, '2009-08-22', '15:00:00', 42, 30, 2, 1);
INSERT INTO `all_games` VALUES (4, '2009-08-24', '19:45:00', 1, 42, 0, 3);
INSERT INTO `all_games` VALUES (5, '2009-08-29', '15:00:00', 11, 42, 3, 0);
CREATE TABLE `teams` (
`team_id` int(11) NOT NULL auto_increment,
`team_name` varchar(45) collate latin1_general_ci default NULL
PRIMARY KEY (`team_id`)
)
INSERT INTO `teams` VALUES (1, 'Aveley');
INSERT INTO `teams` VALUES (2, 'Billericay Town');
INSERT INTO `teams` VALUES (3, 'Bury Town');
INSERT INTO `teams` VALUES (4, 'Canvey Island');
INSERT INTO `teams` VALUES (5, 'Carshalton Athletic');
The query I currently use to show the result of the game eg (in pseudo code)...
showdate ' ' HomeTeam ' ' home_goals ' ' AwayTeam ' ' away_goals
would show
Monday, 26th December 2011 United 2 City 0
SELECT *,
DATE_FORMAT(`time`,'%l:%i%p ') AS showtime,
DATE_FORMAT(`date`,'%W, %D %M %Y') AS showdate,
HT.team_name as HomeTeam,
VT.team_name as AwayTeam,
FROM all_games
JOIN teams as HT ON all_games.home_team = HT.team_id
JOIN teams as VT ON all_games.away_team = VT.team_id
WHERE all_games.home_team IS NOT NULL
