Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Location
    Richmond, CA
    Posts
    201
    Thanks
    3
    Thanked 10 Times in 9 Posts

    Tournament Bracket Control

    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:

    Code:
    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:

    Code:
    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.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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
    Code:
    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:
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,437
    Thanks
    75
    Thanked 4,372 Times in 4,337 Posts
    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):
    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.
    Last edited by Old Pedant; 12-07-2012 at 01:29 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    Aug 2006
    Location
    Richmond, CA
    Posts
    201
    Thanks
    3
    Thanked 10 Times in 9 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

    Quote Originally Posted by Old Pedant View Post
    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):
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •