Soccer Results Matrix

[MargateSteve]

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

[phorce]

Few questions:

- Will this be updated on a weekly basis? (Each team play once a week, maybe 2 times)
- Will the teams remain constant, in that, they can be called from the database for each week?

=)

[MargateSteve]

Will this be updated on a weekly basis? (Each team play once a week, maybe 2 times)

Each team will play each other 2 times during one season/campaign. There may be occasions where teams will play each other 4 times in a season but for what I am doing at the moment it will be twice.
There is also not set pattern to when the games will be played. Sometimes the two games between two teams will be within the space of a few weeks, sometimes they may be months apart.

Will the teams remain constant, in that, they can be called from the database for each week?

Yes and No! The teams will remain constant per competition per season. However, each season teams disappear and new ones appear. However, in my full code, there are a few extra bits such as, in the query, making sure that only the games for the correct competition and the correct season are shown. Each time I use a grid it would be restricted to 1 competition and 1 season.

An example of the results in action on the site I am working on can be found at http://www.margate-fc.com/content/1st_team/leagueresults.php?season_id=105.

On there, two of the options are 'Choose Season' and 'Choose Competition' and they show how I use $_get data to collect the correct results.

Regards
Steve

OK, I've been working on something similar recently, but with a totally different application.

It may be useful to you, so here's the idea:

1. get an array of teams, sorted by name from the DB.
2. get all the results for the season, with a unique 'built' index, e.g. 1_2 for team1 vs team2 and 5_3 for team5 vs team3 (hometeam first).

It may work, it may not. I'm no pro, so there may be a much easier method of doing things:

//GET THE TEAMS IN ALPHABETICAL ORDER
$r = mysql_query("SELECT team_id, team_name FROM teams ORDER BY team_name");
while($d=mysql_fetch_array($r)){
  $teams[] = array($d['team_id'],$d['team_name']);
}

//GET THE RESULTS INTO AN ARRAY - YOU NEED TO SORT OUT THE SEASON HERE THOUGH
$r = mysql_query("SELECT CONCAT(`home_team`, "_", `away_team`) AS ident, `home_goals`, `away_goals` FROM `all_games`");
while($d=mysql_fetch_array($r)){
  switch(true){
     case $d['home_goals'] == $d['away_goals']:
        $class = 'draw';
        break;
     case $d['home_goals'] > $d['away_goals']:
        $class = 'win';
        break;
     default:
        $class = 'lose';
  }
  $results[$d['ident']] = array($d['home_goals'] . ' - ' . $d['away_goals']),$class); 
}

//CREATE THE TABLE HEADER
 
$tbl = "<table><thead><tr><th> Home | Away</th>"
foreach($teams as $away){
  $tbl .= "<th>{$away['team_name']}</th>";
}
$tbl .= "</tr></thead><tbody>";
//CREATE TABLE BODY
foreach($teams as $home){   
  $tbl .= "<tr><th>{$home['team_name']}</th>";
  foreach($teams as $away){
     $check = home[0] . '_' . $away[0];
     if($home[0] == $away[0]){
        $tbl .= '<td class="clash">&nbsp;</td>';
     }elseif(isset($results[$check])){
        $tbl .= "<td class=\"{$results[$check][1]}\">{$results[$check][0]}</td>";  
     }else{
        $tbl .= '<td class="toplay">&nbsp;</td>';
     }     
  $tbl .= "</tr>";
}
$tbl .= "</tbody></table>";
//....
echo $tbl;

The classes are just hooks into CSS - so have styles for .win, .lose, .draw, .toplay and .clash

[MargateSteve]

Thanks for that ardav.

Unfortunately it has not worked 'straight out of the box' but that is possibly because I only posted the parts of the queries/tables that were relevant. There may be other bits causing the problem.

The page takes forever to load and then returns nothing so what I will do is break it down bit by bit to see which parts work with the full queries.

I will post my findings once done!!

Thanks again
Steve

OK, I didn't think that it would work out the box, but it should be close-ish. If it takes forever, it may be that there's an bad loop somewhere?!

Put a few echoes here and there just to see that the data you expect is getting extracted.

EDIT

It may be because $teams is being used nested-wise.

Try:

$teams2 = $teams;

foreach($teams as $home){   
  $tbl .= "<tr><th>{$home['team_name']}</th>";
  foreach($teams2 as $away){
     $check = home[0] . '_' . $away[0];
     if($home[0] == $away[0]){
        $tbl .= '<td class="clash">&nbsp;</td>';
     }elseif(isset($results[$check])){
        $tbl .= "<td class=\"{$results[$check][1]}\">{$results[$check][0]}</td>";  
     }else{
        $tbl .= '<td class="toplay">&nbsp;</td>';
     }     
  $tbl .= "</tr>";
}

I really don't know whether that would make a difference.

[MargateSteve]

I have not had a chance to try this yet as either my ISP or host has blocked my access to the site!!
Once I am reconnected to it I will give it a try.

Thanks
Steve