Ben Ben - 3 months ago 15
MySQL Question

Round Robin Scheduler Filling in "byes"

I am using a Round Robin to schedule games. I would like each team to play "n" games, and I have "x" teams. Everything is great if "x" is even, but if "x" is odd I have to add another team, "bye" to get it to work.

When I have 11 teams playing 4 games each, with the byes it works out so that 4 teams are getting byes. This is inefficient, as they could just play each other so that every team plays 4 games.

After scheduling all the games including the games where one team is "bye", I am trying to make all the teams that have had a "bye week" and make them play one game against each other. I need to make sure that the teams haven't played before.

Since I am using a small number of teams, I am just using a randomizer to try combinations until one works. However, I cannot get that to work.

Here is my code that schedules the teams that had by rounds to play one another. I would like to go through and check to see if they have played before, and if they have, randomize the list so that we can try a different combo. I don't think the checking whether or not the teams have played so far is working, as it never shuffles the teams even if they have overlap.

while (true){
$needshuffled = "no";
for ($j=0; $j < $maxgames/2; $j++){
$k = $j*2;
$round[$maxgames][$j]["Home"]=$byeteams[$k];
$round[$maxgames][$j]["Away"]=$byeteams[$k+1];

$home = $round[$maxgames][$j]["Home"];
$away = $round[$maxgames][$j]["Away"];

$sql = "SELECT * FROM `temp`
WHERE (Home = '$home' and Away = '$away')
or (Home = '$away' and Away = '$home')";
if ($conn->query($sql) === TRUE) {
if ($result=mysqli_query($conn,$sql))
{
// Return the number of rows in result set
$rowcount=mysqli_num_rows($result);
if ($rowcount > 0)
{
$needshuffled = "yes";
}
}

}
}

if ($needshuffled == "no"){
break;
}
}

Answer

if ($conn->query($sql) === TRUE) is not true if you use it for a select statement, see documentation:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

Since you are doing it twice anyway, you can complete remove this line and just use

$sql = "SELECT * FROM `temp` 
        WHERE (Home = '$home' and Away = '$away') 
        or (Home = '$away' and Away = '$home')";
if ($result=mysqli_query($conn,$sql)) {
    ...

Alternatively, since you have your full schedule in your variable $round already, you could use this to check for existing matches.

And you can try a different strategy:

For round robin with an uneven number of teams and an even number of days, there will be exactly one day in the future where the subset of teams that have had a bye already will play against a team that had a bye already, and all teams that have had no bye yet will either have a bye that day or play against a team that have had no bye yet.

All you need to do is keep generating the round robin table until you find this day and use the matches of that day.