How to create a list of devices in PHP / MySQL?

Question

I am trying to create a list of fixtures for a football league. I managed to generate the actual list of devices without any problems, but now I'm trying to create a "week" for which the game should play. It only needs to be “1 to n,” no other date information, just a week later the number.

The table I created in MySQL looks like this:

Home Away GameID WeekID Team 2 Team 1 1 0 Team 3 Team 1 2 0 Team 3 Team 2 3 0 Team 4 Team 1 4 0 Team 4 Team 2 5 0 Team 4 Team 3 6 0 Team 1 Team 2 7 0 Team 1 Team 3 8 0 Team 2 Team 3 9 0 Team 1 Team 4 10 0 Team 2 Team 4 11 0 Team 3 Team 4 12 0 

Each team must play at home and away, therefore, duplication in some games. However, what I need to do, as mentioned earlier, is to assign the week number to the game, and the team can only play once a week.

I am trying to create:

 Home Away GameID WeekID Team 2 Team 1 1 1 Team 3 Team 1 2 2 Team 3 Team 2 3 3 Team 4 Team 1 4 4 Team 4 Team 2 5 5 Team 4 Team 3 6 6 Team 1 Team 2 7 6 Team 1 Team 3 8 5 Team 2 Team 3 9 4 Team 1 Team 4 10 3 Team 2 Team 4 11 2 Team 3 Team 4 12 1 

Any help on this would be appreciated.

Answer 1

The following solution works, but I don’t think it populates the schedule as efficiently as possible for groups of teams that do not have the privileges twice. Also, the code efficiency is n ^ 2 (maybe n ^ 3?) For all cases, so I hope you don't need to schedule more than several hundred commands at once .: P

 <?php $teams = $_GET['t']; $games = array(); //2D array tracking which week teams will be playing $weeks = array(); //2D array tracking which teams are playing in a given week // initialize for( $i=0; $i<$teams; $i++ ) { $games[$i] = array(); for( $j=0; $j<$teams; $j++ ) { if( $i == $j ) { $games[$i][$j] = -1; } //you can't play with yourself ;D else { $games[$i][$j] = NULL; } } } // do the work for( $w=1, $noblanks=false; !$noblanks; $w++) { if( !isset($weeks[$w]) ) { $weeks[$w] = array(); } $noblanks = true; //begin assuming there are no blank spots in the matrix for( $i=0; $i<$teams; $i++ ) { for( $j=0; $j<$teams; $j++ ) { if( $i == $j ) { continue; } //you can't play with yourself ;D if( is_null($games[$i][$j]) ) { if( !isset($weeks[$w][$i]) && !isset($weeks[$w][$j]) ) { $games[$i][$j] = $w; //game between team i and j in week w $weeks[$w][$i] = true; //mark that team i has game in week w $weeks[$w][$j] = true; //mark that team j has game in week w } else { $noblanks = false; } //this cell is blank, and will be left blank. } } } } // display echo '<pre>'; foreach($games as $row) { foreach($row as $col) { printf('%4d', is_null($col) ? -2 : $col); } echo "\n"; } printf("%d teams in %d weeks\n", $teams, count($weeks)); echo '</pre>'; 

Output Example:

  -1 1 2 3 4 -1 3 2 5 6 -1 1 6 5 4 -1 4 teams in 6 weeks -1 1 2 3 4 5 6 7 -1 3 2 5 4 8 8 6 -1 1 7 9 4 9 10 5 -1 6 7 11 10 9 11 8 -1 1 2 11 12 10 13 3 -1 14 12 13 15 16 17 18 -1 7 teams in 18 weeks 

Edit

I figured out a method that is more "weekly inefficient" for all cases, except when the number of teams is two. Essentially, the number of weeks is required to be 2 * number_of_teams .

Using pen-and-paper methods, I noticed that alternating numbers diagonally across a matrix is ​​pretty much perfect, and on my walk home I came up with a method in which you can simply feed 2 team identifiers and the number of teams, and this will return you week when this game is due to take place.

 <?php function getweek($home, $away, $num_teams) { if($home == $away) { return -1; } $week = $home+$away-2; if( $week > ($num_teams) ) { $week = $week-$num_teams; } if( $home>$away ) { $week += $num_teams; } return $week; } $teams = $_GET['t']; $games = array(); //2D array tracking which week teams will be playing // do the work for( $i=1; $i<=$teams; $i++ ) { $games[$i] = array(); for( $j=1; $j<=$teams; $j++ ) { $games[$i][$j] = getweek($i, $j, $teams); } } // display echo '<pre>'; $max=0; foreach($games as $row) { foreach($row as $col) { printf('%4d', is_null($col) ? -2 : $col); if( $col > $max ) { $max=$col; } } echo "\n"; } printf("%d teams in %d weeks, %.2f weeks per team\n", $teams, $max, $max/$teams); echo '</pre>'; 

Result:

  -1 1 2 3 5 -1 3 4 6 7 -1 1 7 8 5 -1 4 teams in 8 weeks, 2.00 weeks per team -1 1 2 3 4 5 6 8 -1 3 4 5 6 7 9 10 -1 5 6 7 1 10 11 12 -1 7 1 2 11 12 13 14 -1 2 3 12 13 14 8 9 -1 4 13 14 8 9 10 11 -1 7 teams in 14 weeks, 2.00 weeks per team 

Edit (April 2013)

I changed the getWeek() function to work for any number of commands. See New Feature below. Yovan

 function getWeek($home, $away, $num_teams) { if($home == $away){ return -1; } $week = $home+$away-2; if($week >= $num_teams){ $week = $week-$num_teams+1; } if($home>$away){ $week += $num_teams-1; } return $week; }