Tournament Bracket Control

[Daniel Israel]

Hi,

I'm working on a site that maintains tournament brackets. When the bracket is created, it creates a table of match details that pairs up the players/teams. When the game is played, the score is entered and based on who wins, the row has data as to which match they go to next.

Now what I'm trying to do is get a list of brackets and find how many total matchups there are (given the cases where there's not a number of contestants that is a power of two, the seeding algorithm will give a bye to some of the contestants) and how many matchups are left to complete the bracket. The table looks like this:

CREATE TABLE IF NOT EXISTS `match_detail` (
  `bracket` int(11) NOT NULL,
  `round` int(11) NOT NULL,
  `match_number` int(11) NOT NULL,
  `top_contestant` varchar(100) NOT NULL,
  `bottom_contestant` varchar(100) NOT NULL,
  `top_score` int(11) NOT NULL,
  `bottom_score` int(11) NOT NULL,
  `winner` varchar(100) NOT NULL,
  `winner_dest` varchar(20) NOT NULL,
  `loser_dest` varchar(20) NOT NULL,
  `completed` datetime NOT NULL
);

so each row is a matchup on the bracket. The match number is the absolute match number (corresponding to the placement on the template used to display), and when the template is generated, it numbers the matches (for humans to read) skipping over the bye matches (if a contestant has a bye, his opponent is labeled "[bye]" and he's automatically advanced to the next match during bracket creation).

I'm trying to figure out if there's an easy way do this in a single query, and what I've come up with is this:

SELECT bracket, SUM(IF(top_contestant='[bye]' OR bottom_contestant='[bye]', 0, 1)) AS matches, SUM(IF(winner = '', 1, 0)) AS pending FROM `match_detail` GROUP BY bracket

Is there a more efficient way to do this? I'm not committed to the table structure, it can be changed (we're still doing POC).

Thanks for any suggestions.

[Old Pedant]

Ummm...I'm a little confused.

Why would you ever have a top_contestent that is a bye??

I would think you would only put ACTUAL contestants into the table.

And then it's simply

SELECT bracket, COUNT(*) FROM match_detail GROUP BY bracket

(or add in your "as PENDING" as well).

Having said that...If you insist on having top_contestants named bye then you r query looks fine.

(p.s.: If you use NULL instead of NOT NULL for most of those fields, it will simplify your life when creating new match-ups. I'd suggest:

CREATE TABLE IF NOT EXISTS `match_detail` (
  `bracket` int(11) NOT NULL,
  `round` int(11) NOT NULL,
  `match_number` int(11) NOT NULL, /* maybe this NULL as well? */
  `top_contestant` varchar(100) NOT NULL,
  `bottom_contestant` varchar(100) NULL,
  `top_score` int(11) NULL,
  `bottom_score` int(11) NULL,
  `winner` varchar(100)  NULL,
  `winner_dest` varchar(20) NULL,
  `loser_dest` varchar(20) NULL,
  `completed` datetime NULL
);

And, incidentally, a non-null COMPLETED match with NULL for bottom_opponent is thus a BYE.

[Old Pedant]

In other words, when the list of entrants is complete, you find the power of 2 that is next higher than the number of entrants, and that's the number of matches for that round. You seed everybody.

Then you create the matches in this way (pseudo code):

' say maxseeding (same as number of entrants) is 23.
' so you will need 16 matches
For seeding = 1 To numberOfMatches /* 32 */
    p1 = player[seeding]
    p2seed = (numberOfMatches * 2 ) + 1  - seeding  /* 1 plays 32, 2 plays 32, etc. */
    If p2seed > maxseeding Then p2 = "bye" Else p2 = player[p2seed]
    sql = "INSERT INTO match_details ( bracket, round, top_opponent, bottom_opponent ) 
              VALUE( $bracket, 1, $p1, $p2 )"
    execute( sql )
Next

And the code for the next round is nearly identical, esp. if this is single elimination. A bit more complex if double, but not bad. (Easiest way is probably to simply give new seeding numbers based on how the player did in the match. Have to play with the algorithm. Depends on wither double, triple, etc., elimination. Or round robin?)

By not giving values to any of the other fields, you wait to fill them in until the match is complete.

[Daniel Israel]

Originally Posted by Old Pedant

Ummm...I'm a little confused.

Why would you ever have a top_contestent that is a bye??

Excellent question. One of the requirements is that the user/admin needs to be able to manipulate the bracket so they can place the player in the exact spot they wish. It's actually feasible to have a bye in BOTH categories. Sounds crazy, I know, but without getting too deep into it, there's a business need for it.

Originally Posted by Old Pedant

In other words, when the list of entrants is complete, you find the power of 2 that is next higher than the number of entrants, and that's the number of matches for that round. You seed everybody.

Then you create the matches in this way (pseudo code):

' say maxseeding (same as number of entrants) is 23.
' so you will need 16 matches
For seeding = 1 To numberOfMatches /* 32 */
    p1 = player[seeding]
    p2seed = (numberOfMatches * 2 ) + 1  - seeding  /* 1 plays 32, 2 plays 32, etc. */
    If p2seed > maxseeding Then p2 = "bye" Else p2 = player[p2seed]
    sql = "INSERT INTO match_details ( bracket, round, top_opponent, bottom_opponent ) 
              VALUE( $bracket, 1, $p1, $p2 )"
    execute( sql )
Next

And the code for the next round is nearly identical, esp. if this is single elimination. A bit more complex if double, but not bad. (Easiest way is probably to simply give new seeding numbers based on how the player did in the match. Have to play with the algorithm. Depends on wither double, triple, etc., elimination. Or round robin?)

By not giving values to any of the other fields, you wait to fill them in until the match is complete.

The code for this (populating match details) is already done. Actually, it uses the template to populate the database in a pretty keen way. You're correct about the single/double elimination... The templates for that are actually not that bad. I'm just PRAYING they don't ask me to do round robin. LOL

Thanks for your input.