• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

auto generate team matches

Hi,

I need to auto generate matches for football teams where the each football team is matched up with another to play a game.


Each team places another team twice.


So Match set 1 and Match Set 2


Match set one is at "home" and Match set two is "away"


I am not sure how to get this done so it will hold the information on the matches.
0
cataleptic_state
Asked:
cataleptic_state
  • 23
  • 17
1 Solution
 
ollyatstithiansCommented:
You can do pretty much all of this in sql:

1. Create a table for your teams and fill it with the name (and other details) of each team.
2. Create a table for your matches and leave it empty.
3. Populate the matches table with this query.
  insert matches (team1, team2) select a.name, b.name from teams a, teams b where a.name <> b.name;
4. Assign a date to each match.

So all you need to do in PHP is make an interface to add/remove teams, add dates and regenerate the list of matches.
0
 
cataleptic_stateAuthor Commented:
Hi,
Thats great, can you tell me if my database is correct or if I need to add anything to make it work.
CREATE TABLE IF NOT EXISTS `matches` (
  `match_id` int(8) NOT NULL auto_increment,
  `date` date default NULL,
  `time` varchar(5) default NULL,
  `report` longtext,
  `referee_id` int(8) NOT NULL,
  `season_id` int(8) NOT NULL,
  PRIMARY KEY  (`match_id`),
  KEY `referee_id` (`referee_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE IF NOT EXISTS `matchscores` (
  `matchscores_id` int(8) NOT NULL auto_increment,
  `match_id` int(8) NOT NULL,
  `team` int(8) NOT NULL,
  `score` int(8) default NULL,
  PRIMARY KEY  (`matchscores_id`),
  KEY `match_id` (`match_id`,`team`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Open in new window

0
 
ollyatstithiansCommented:
To be honest, I don't see the point of having a separate table of scores, as each match will only ever have exactly 2 scores (or nulls) attributed to it.
The matches table needs a team1 (or hometeam) and a team2 (or awayteam) field.

Obvoiusly you will be making tables of referees, seasons and teams.
CREATE TABLE IF NOT EXISTS `matches` (
  `match_id` int(8) NOT NULL auto_increment,
  `date` date default NULL,
  `time` varchar(5) default NULL,
  `report` longtext,
  `referee_id` int(8) NOT NULL,
  `season_id` int(8) NOT NULL,
  `hometeam_id` int(8) NOT NULL,
  `homescore` int(8) default NULL,
  `awayteam_id` int(8) NOT NULL,
  `awayscore` int(8) default NULL,
  PRIMARY KEY  (`match_id`),
  KEY `referee_id` (`referee_id`)
);

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
cataleptic_stateAuthor Commented:
Hi,
I had this originally, but someone suggested the above design so I can get a results table system working.
0
 
ollyatstithiansCommented:
You can still have a separate table if you want, but I would consider it over-complicated.
To generate a table of results, you should be able to run a query (add a where clause to get results for a certain team, time period etc.):
select m.date, h.name, m.homescore, a.name, m.awayscore 
from matches 
left join teams h on (h.team_id = m.hometeam_id)
left join teams a on (a.team_id = m.awayteam_id)

Open in new window

0
 
cataleptic_stateAuthor Commented:
:'(

This is what he gave me. it works so I dont want to remove it.

Can your example for the auto generated listings of which team will play which team still work?
$query_league_tables = "SELECT 
  team.team_name, 
  COUNT(IF(ownscores.score > foreignscores.score, 1, NULL)) AS wins, 
  COUNT(IF(ownscores.score = foreignscores.score, 1, NULL)) AS draws, 
  COUNT(IF(ownscores.score < foreignscores.score, 1, NULL)) AS losses,
  COUNT(matches.match_id) AS number_matches,
  SUM(ownscores.score) AS ourGoals,
  SUM(foreignscores.score) AS theirGoals,
  SUM(ownscores.score) - SUM(foreignscores.score) AS finalGD,
  SUM(IF(ownscores.score > foreignscores.score, 3, IF(ownscores.score < foreignscores.score, 0, IF(ownscores.score = 0, 0, 1)))) AS points 
FROM team 
  INNER JOIN (matches 
    INNER JOIN (matchscores AS ownscores 
      INNER JOIN (matchscores AS foreignscores 
        INNER JOIN team AS foreignteam ON foreignscores.team = foreignteam.team_id) 
      ON (ownscores.team <> foreignscores.team) AND (ownscores.match_id = foreignscores.match_id)) 
    ON matches.match_id = ownscores.match_id) 
  ON team.team_id = ownscores.team 
GROUP BY team.team_name 
ORDER BY points DESC";

Open in new window

0
 
ollyatstithiansCommented:
Ok. So we need to create a record in matches for each match, then create a record in matchscores for each team in each match. You can then populate the new matches with dates, referees etc. (I haven't tested this php code).
<?php
$season_id = 1;

// Get the list of matches
$result = mysql_query('select h.team_id as hometeam, a.team_id as awayteam from teams h, teams a where h.name <> a.name');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}";
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}";
}

Open in new window

0
 
cataleptic_stateAuthor Commented:
Great, I will give this a try.
My season_id is "2010"
I forgot the make it an auto incrementing primary key
0
 
ollyatstithiansCommented:
Have fun!
0
 
cataleptic_stateAuthor Commented:
Hi I am getting this:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

currently I am making the page where I can show the fixtures. Can I use the first query to show the match details?
0
 
ollyatstithiansCommented:
Add:

" or die(mysql_error());"

to the end of each of the mysql_query() lines. There is probably an error in the query.
0
 
cataleptic_stateAuthor Commented:
Parse error: syntax error, unexpected T_LOGICAL_OR
0
 
cataleptic_stateAuthor Commented:
Did this

// Get the list of matches
$result = mysql_query('select h.team_id as hometeam, a.team_id as awayteam from team h, team a where h.name <> a.name') or die(mysql_error());
while($match = mysql_fetch_assoc($result))

Unknown column 'h.name' in 'where clause'
0
 
ollyatstithiansCommented:
The name field is not called "name" in your db.

Please post the result of "show create table teams;" from mysql if you can't figure it out.
0
 
ollyatstithiansCommented:
Ah! seen it in your other query. Here is the fixed code:
<?php
$season_id = 1;

// Get the list of matches
$result = mysql_query('select h.team_id as hometeam, a.team_id as awayteam from teams h, teams a where h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}";
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}";
}

Open in new window

0
 
cataleptic_stateAuthor Commented:
Hi I think it works

But you keep missing out your brackets at the end of the mysql statement.

OK on the page that I want to show the fixture details is giving me problems.

mysql_select_db($database_db, $db);
$query_match_fixtures = "SELECT * FROM matches LEFT JOIN matchscores ON matches.match_id = matchscores.match_id LEFT JOIN team ON matchscores.team = team.team_id WHERE division = 'Pr'";
$match_fixtures = mysql_query($query_match_fixtures, $db) or die(mysql_error());
$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
$totalRows_match_fixtures = mysql_num_rows($match_fixtures);
0
 
cataleptic_stateAuthor Commented:
<table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td><div align="center">Date</div></td>
                <td><div align="center">Division</div></td>
                <td><div align="center">Home</div></td>
                <td><div align="center">Score</div></td>
                <td><div align="center">Away</div></td>
                <td><div align="center">Kick-Off</div></td>
                <td><div align="center">Report</div></td>
                <td><div align="center"></div></td>
              </tr>
              <tr>
                <td><div align="center"><?php echo $row_match_fixtures['date']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['division']; ?></div></td>
                <td><div align="center"></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['score']; ?></div></td>
                <td><div align="center"></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['time']; ?></div></td>
                <td><div align="center"></div></td>
                <td><div align="center"></div></td>
              </tr>
            </table></td>
          </tr>
        </table>
0
 
cataleptic_stateAuthor Commented:
I do not know how I can extract two teams from the table that played in the same match
0
 
cataleptic_stateAuthor Commented:
Here is the screen dump from phpmyadmin
Untitled-1.jpg
0
 
cataleptic_stateAuthor Commented:
I have done this from your example and its not finding the h.team_id field
$query_match_fixtures = "SELECT *, 
h.team_id as hometeam, 
a.team_id as awayteam
FROM matches LEFT JOIN matchscores ON matches.match_id = matchscores.match_id LEFT JOIN team ON matchscores.team = team.team_id WHERE division = 'Pr'";

Open in new window

0
 
ollyatstithiansCommented:
The order of the teams is a bit random, as there is nothing to say which team is at home, but this query does work ok.
select m.match_id, m.date, m.time, m.report, t1.name, s1.score, t2.name, s2.score from matches m left join (matchscores s1 left join teams t1 on t1.id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join teams t2 on t2.id = s2.team) on (s2.match_id = m.match_id) where s1.team <> s2.team group by match_id order by m.match_id;

Open in new window

0
 
ollyatstithiansCommented:
Sorry, you will have to check the fieldnames as I think yours may be different from mine.
0
 
cataleptic_stateAuthor Commented:
is this for the listing page or the page that creates the auto tables?
0
 
cataleptic_stateAuthor Commented:
Hi,
This is what I get from that:
Unknown column 't1.name' in 'field list'

t1.name is what you are calling a temp column to hold the team1's id?
or is it something else?
0
 
cataleptic_stateAuthor Commented:
Hi,
I figured out what you are doing with the SQL.

But the result comes out like this:


Untitled-1.jpg
0
 
ollyatstithiansCommented:
Ahh! Monday again!

You need the part in your where clause that says "s1.team <> s2.team".

Post the whole sql if it still doesn't work.
0
 
cataleptic_stateAuthor Commented:
yes Monday :(

I do have the <> code

$query_match_fixtures = "select m.match_id, m.date, m.time, m.report, t1.team_name, s1.score, t2.team_name, s2.score from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) where s1.team <> s2.team group by match_id order by m.match_id";
0
 
ollyatstithiansCommented:
It works on my test data. Can you post the data you are using for the team, matches and matchscores tables?
0
 
cataleptic_stateAuthor Commented:
the data is already in the database, see screen shot
-- Table structure for table `matches`
--

 

CREATE TABLE IF NOT EXISTS `matches` (
  `match_id` int(8) NOT NULL auto_increment,
  `date` date default NULL,
  `time` varchar(5) default NULL,
  `report` longtext,
  `referee_id` int(8) NOT NULL,
  `season_id` int(8) NOT NULL,
  `venue_id` int(8) NOT NULL,
  PRIMARY KEY  (`match_id`),
  KEY `referee_id` (`referee_id`),
  KEY `venue_id` (`venue_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

 

-- Table structure for table `matchscores`
--

 

CREATE TABLE IF NOT EXISTS `matchscores` (
  `matchscores_id` int(8) NOT NULL auto_increment,
  `match_id` int(8) NOT NULL,
  `team` int(8) NOT NULL,
  `score` int(8) default NULL,
  PRIMARY KEY  (`matchscores_id`),
  KEY `match_id` (`match_id`,`team`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

Open in new window

0
 
cataleptic_stateAuthor Commented:
this is how I am outputting the data
<table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td><div align="center">Date</div></td>
                <td><div align="center">Division</div></td>
                <td><div align="center">Home</div></td>
                <td><div align="center">Score</div></td>
                <td><div align="center">Away</div></td>
                <td><div align="center">Kick-Off</div></td>
                <td><div align="center">Report</div></td>
                <td><div align="center"></div></td>
              </tr>
              <tr>
                <td><div align="center"><?php echo $row_match_fixtures['date']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['division']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['team_name']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['score']; ?> v <?php echo $row_match_fixtures['score']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['team_name']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['time']; ?></div></td>
                <td><div align="center"></div></td>
                <td><div align="center"></div></td>
              </tr>
            </table></td>
          </tr>
        </table>

Open in new window

0
 
ollyatstithiansCommented:
Oh right. That will be it then. You need to either refer to the columns by their numbers with mysql_fetch_row() or give the columns with duplicate names (ie. team_name and score) an alias. Try this modified sql and php.
<?php
$query_match_fixtures = "select m.match_id, m.date, m.time, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2 from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id) where s1.team <> s2.team group by match_id order by m.match_id";
?>

...

<?php
<table width="100%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td><table width="100%" border="0" cellspacing="0" cellpadding="0">
              <tr>
                <td><div align="center">Date</div></td>
                <td><div align="center">Division</div></td>
                <td><div align="center">Home</div></td>
                <td><div align="center">Score</div></td>
                <td><div align="center">Away</div></td>
                <td><div align="center">Kick-Off</div></td>
                <td><div align="center">Report</div></td>
                <td><div align="center"></div></td>
              </tr>
              <tr>
                <td><div align="center"><?php echo $row_match_fixtures['date']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['division']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['team1_name']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['score1'].' v '.$row_match_fixtures['score2']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['team2_name']; ?></div></td>
                <td><div align="center"><?php echo $row_match_fixtures['time']; ?></div></td>
                <td><div align="center"></div></td>
                <td><div align="center"></div></td>
              </tr>
            </table></td>
          </tr>
        </table>
?>

Open in new window

0
 
cataleptic_stateAuthor Commented:
Hi that works great :-)

Just need to figure out how to do the results grid section and i am done :-)
0
 
cataleptic_stateAuthor Commented:
just a quick question..what if I needed to put matches into lists so

list 1
the teams that were playing at home would be in the first list

list 2
the teams that were playing at home that were not playing home from list 1 would be in the second list

Say if Aston Villa 1 week were at home (list 1)
Then week 2 they were playing away (list 2)

0
 
ollyatstithiansCommented:
You would need to somehow identify which was the home team, or perhaps which team's ground the match was at. Maybe you could assign a team to the match record as 'location'. This would allow for the irregular event of a match with both teams playing away.
0
 
cataleptic_stateAuthor Commented:
Hi,
I need to add the player match info so I can display who scored what for what team, and the how many yellow cards etc

$query_player_match = "SELECT *,
SUM(Goals) AS myGoals,
SUM(YC) AS myYC,
SUM(RC) AS myRC,
SUM(MOM) AS myMOM
FROM match_player WHERE player_id = $playerID
GROUP BY player_id
ORDER BY myGoals DESC";

0
 
ollyatstithiansCommented:
You would do well to draw a big picture of all the different data that you want to store in your database and designnn your tables accordingly, instead of adding things on. It would make the whole project much easier.

What is wrong with the query you have posted?
0
 
ollyatstithiansCommented:
You will also draw the attention of more experts if you post this as a new question.
0
 
cataleptic_stateAuthor Commented:
Hi,
I have uploaded the accepted solution to the webserver but the problem is it will not create the match fixtures.

Can you help?
0
 
ollyatstithiansCommented:
The questions you have asked have all been about retrieving and displaying data. The solution will not create matches. You need to insert records into the database.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 23
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now