Solved

auto generate team matches

Posted on 2010-08-20
40
385 Views
Last Modified: 2013-12-13
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
Comment
Question by:cataleptic_state
  • 23
  • 17
40 Comments
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33482753
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33482883
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33482949
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33483000
Hi,
I had this originally, but someone suggested the above design so I can get a results table system working.
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33483179
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33483260
:'(

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
 
LVL 10

Accepted Solution

by:
ollyatstithians earned 500 total points
ID: 33483721
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33484190
Great, I will give this a try.
My season_id is "2010"
I forgot the make it an auto incrementing primary key
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33484224
Have fun!
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33484967
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33485139
Add:

" or die(mysql_error());"

to the end of each of the mysql_query() lines. There is probably an error in the query.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33485184
Parse error: syntax error, unexpected T_LOGICAL_OR
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33485201
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33485289
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33485322
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33485471
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33485505
<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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33485526
I do not know how I can extract two teams from the table that played in the same match
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33485575
Here is the screen dump from phpmyadmin
Untitled-1.jpg
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33486383
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33486595
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33486609
Sorry, you will have to check the fieldnames as I think yours may be different from mine.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33486650
is this for the listing page or the page that creates the auto tables?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33486773
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33486861
Hi,
I figured out what you are doing with the SQL.

But the result comes out like this:


Untitled-1.jpg
0
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33499050
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33499219
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33499728
It works on my test data. Can you post the data you are using for the team, matches and matchscores tables?
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33499741
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33499812
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33499891
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33500050
Hi that works great :-)

Just need to figure out how to do the results grid section and i am done :-)
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33500243
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33501495
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33501609
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
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33574844
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33575116
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33575120
You will also draw the attention of more experts if you post this as a new question.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33699388
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
 
LVL 10

Expert Comment

by:ollyatstithians
ID: 33714776
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now