Project in Detail: CoN World Cup

Submitted by Josh on
The Leaderboard I've been a soccer player for the vast majority of my life, but my path to being a soccer fan was a slower one. That's something for some other blog, probably. But over at the Caves of Narshe, still my primary online community, there have been over the years a lot of folks who have been soccer fans just like me, and a fair amount of banter around the Beautiful Game, as they say.
There's actually more crossover between Final Fantasy fans and sports fans than you might expect at first. At CoN, we've run a NCAA men's basketball tournament competition every year since 2002 or so, and while it's petered out, we also had official Fantasy Football leagues for a lot of years and Fantasy English Premier League for a couple seasons as well. But every four years, there's a World Cup. And the biggest sporting event on earth deserves a special game, so several years ago I built one based on a game I used to play in another online community. We've run it for the World Cup twice now, in 2010 and 2014, and for two UEFA European Cup tournaments in 2012 and 2016.

The Game

The game is a fairly simple affair at its core. The game starts with each player making a prediction for the nation winning the tournament, with points for that prediction pro-rated for how far in the tournament that nation goes.
Then, each player makes a prediction, before match kickoff, for the final score of a particular match in the tournament, and takes a stab at who will score the first goal. Points are given based on how accurate the prediction is; while some things like scoreless draws, own goals, or penalty shootouts can go to make individual predictions a bit hairy, here are the general scoring rules:
  • Pick the score correctly for the home team: one point.
  • Pick the score correctly for the away team: one point.
  • Pick the winner correctly: one point.
  • Pick all three of the above correctly: one bonus point.
  • Pick the first scorer correctly by name: one bonus point.

So, for each match, a player can score up to five for a perfect prediction. This is how the scoring works during "group play," which for the uninitiated is the first stage of a tournament where all the teams are split up into groups and play round-robin, with the best teams moving on to the "knockout stage," which becomes a winner-take-all, no-draws-allowed stage until one winner is crowned. Once the knockout stage begins, the points per match double for each correct prediction as listed above. Players can make predictions in the group stage all at once, or match-by-match, as long as they predict before that match kicks off - no opening the box to peek at the gift. Once teams are settled into their spots for the knockout round, players can again make predictions from the time the team is assigned to the time the match kicks off.

At the end of the tournament, all the points for match predictions and overall winners are tallied, and champions crowned.

When I first started playing this game, and even the first time or two we did it at CoN, it was a pen-and-paper setup. At CoN, players would send me predictions via email, I'd tally them and report back scores at the end of each day. It took too long and wasn't all that fun for me, even once a friend helped me develop a complicated spreadsheet to autoscore predictions in one central place. After doing a tournament with that spreadsheet, I realized that all of the logic in the spreadsheet had direct analogues in a LAMP-based website, and as such the entire thing could become self-service on the web, with near-live scoring updates after matches and simple administration.

Requirements for a Web App

An Example Prediction with Score To work, this app needed to be able to do the following:
  1. Allow admins to add team rosters, tournament schedules, and crest images to the system for later use by the code.
  2. Allow users to register for the tournament and make predictions for overall winner and individual match results, and store those predictions to the user's records.
    • User login and logout should be handled by API methods within the discussion forums system.
    • If the user has several matches available to predict, allow the user to store their selections in batches as desired.
    • Where possible, show the user match kickoff times in their local time zone to avoid confusion. Show also a countdown where appropriate for matches that are close to kickoff.
    • Provide error handling and messaging via the client and the server so that users can detect mistakes in their predictions before their time expires.
  3. Allow for timing of events within the tournament, such as the cutoff time to make predictions for the winner (before the first match kicks off) and before each match (before that match kicks off). 
    • Late arrivals should still be able to predict any matches that have not yet started, but will lose the opportunity to predict the overall winner and any matches they missed.
  4. Create a UI to allow admin users to record the results of individual matches, thereby triggering score updates for users.
  5. Create a UI to allow admin users to "knock teams out" of the competition as they are eliminated, thereby triggering users to get the alloted number of pro-rated "winner's" points.
  6. Create a scoreboard that will show all users and their "live" ranking in the competition, pulling the most recent data available based on data entry after each match concludes.
  7. Give users a tournament archive view in which they can see each match that they predicted, with visual indicators showing where they scored points, as well as how many were scored for each category.


To move this to the web, a lot of the usual suspects came into play.
User Interface/User Experience: Running this through CoN, it was simplest to build the UI within the bespoke templating system built for the mother site. This made it easy to roll out pages with navigation and overall design inherited, with minimal additions needed for styling. A Javascript layer in jQuery was necessary for some ease-of-use applications, such as client-side data validation and error handling, and asynchronous server calls to make batch updates to user predictions. Other design elements would include crests for every team involved, sourced mainly from Wikipedia, and some small images to help the user see what pieces of a prediction they got right and wrong after a match (here, just a simple but obvious checkmark and 'x', respectively).
Middleware/Back End: As a site running on the LAMP stack, interactions between the user-facing layer and the underlying data would happen in PHP.
Data: Again leveraging the overall architecture already existing for CoN, userdata for the players would be pulled from the mySQL database running the CoN discussion forums. This same userdata is in use not just for the forums, but also to power news posts and comments, art submission and comments, other competitions and quizzes, and other features as well. CoN has a separate database to maintain contest data to segregate it properly from other site data, and this database hosts the game-specific tables.
This database contains a number of tables:
  • A teams table, to list all of the teams participating and map their names to a machine ID for use elsewhere.
  • A rosters table to store the names of every player on the roster for the tournament for every country, assigning them a machine ID to be used when the name is displayed to the user.
  • A matches table, to list every match in the tournament, its kickoff time, and the machine IDs for each team playing, as well as the group or round of the tournament.
  • A match results table, to record against the match's machine ID the final score for the home team, away team, and first scorer.
  • A participants table to pull in user data from the forums database after a successful authentication, and store it locally to avoid cross-database joins wherever possible. This table also stores the prediction for the tournament winner, and later, how many points that prediction was worth.
  • A participant predictions table, which stores the prediction data itself per match as well as the timestamp of the last update to that prediction for validation purposes. This table will have (players * tournament matches) rows; in the case of a World Cup, tournament matches = 64, for instance.
  • Participant scores, which maps the match IDs in the user predictions table to a calculated score.
  • A sorting table to provide a custom sort data set, so that matches can be correctly grouped in their hierarchy, i.e. "Group A, Group B... Group H, Round of 16, Round of 8..."
All of these data points work together on a series of discrete pages.
  • A landing page, which also serves as the page for users to sign up for the tournament and pick (and later change, if the timing is right) their choice for tournament champion. The button for signup is tied to a webform that includes the champion selector, and when clicked, creates records for the user in a few tables to confirm the user's data and create placeholders for future input. The landing page also contains a few of the next upcoming matches for users who want to be able to put in a few predictions a day without traveling around the subsite, and the most recently completed match for a quick glance at the user's most recent scoring opportunity.
  • A rules page, which is maintained by hand, explaining the scoring and timing systems at play.
  • A leaderboard, which will show the usernames and avatars for each player in a table by their current score rank.
  • An "open matches" page which lists more upcoming and active matches than the landing page, allowing users to see and predict far more matches at once.
  • A "closed matches" page, which serves the same purpose but for matches that have been completed and each participant's score registered. This page gives the user his or her detailed scoring, relative to the leaderboard which shows only the aggregate.
  • A couple admin pages:
    • A page to record match scores and trigger score updates in the participant scores table.
    • A page to remove teams from the tournament and apply participants' points for their champion selector.
On pages that have the ability to solicit predictions for upcoming matches, an additional layer of Javascript lies atop the page to support some ease-of-use features.
  • Selecting a winner that is not possible with the scores predicted is blocked by a validator. For instance, picking Germany to score 0 goals and the United States to score 2, but then selecting Germany as the winner will encounter an error.
  • Selecting a first scorer when also selecting a goalless draw will also encounter an error.
  • For users who are not familiar with soccer but still want to play, a randomizer button will use Javascript to randomly select scores for each team within a limited range, and randomly select a first scorer as well. For obvious reasons of probability this won't often give the user a chance at a great score, but it does give players who only know a few big names a more fun way of guessing.
  • An asynchronous function allows for predictions to be updated in batches, without having to use a HTTP POST form for each individual match.


For a niche sport on a site that a) is not about sports to begin with and b) mostly comprised of American users, this game has gotten a lot of traffic over the years. The first and largest tournament took on 30 players, while others had fewer; the decline in participation over time tracks pretty accurately to the overall decline in community traffic overall on the site over that span.


There's not really a single point of post-mortem for this project, as it has undergone significant upgrades after each round of tournaments over the years. For instance, the original iteration did not have batch prediction functionality, which dissuaded some less-invested players when faced with the choice between making a couple predictions every day to stay abreast, or to manually have to pick as many as 32 matches at once, with full pageloads after each pick. That said, the current iteration can still use some upgrades for the future, to wit:
  1. Disassociate the game somewhat from its CoN underpinnings. While I intend to run these games as long as CoN is a going concern, the future of the game is likely better as an open-sourced project in many ways. A forked version on GitHub or Bitbucket without CoN attachment might be a useful mini-project.
  2. Improve handling of non-standard alphabetical characters. The database is currently misconfigured to handle letters present in a great number of soccer players' names, such as umlauts and circumflexes. This requires data standardization as a manual process, and it should be unnecessary.
  3. Improve the UX of asynchronous predictions, and in fact aim to remove synchronous predictions entirely (or better develop the synchronous processor so that it can handle multiple predictions at once, which it currently can not do). The async portion should have better-looking and better-displaying failure and success messages, so that users are more aware of what they've just done.
  4. Create a standardized list of crests for the teams. Currently, each time I run a tournament, I have to manually source crests that I don't already have (a recent example was Iceland in Euro 2016, a nation that had never participated in a major international tournament until that time - and they were great, too!). Crests are also stored by their machine ID, as well, and not their country name, which means that even for teams that have been in past tournaments, the odds are that the file name to reference their crest needs to change from tournament to tournament. This would be critical to make a maintainable codebase that would support goal number one above.
  5. General styling upgrades, to both improve the UX and again help to satisfy #1.
Given the confluence of several of my interests here, this has long been one of my favorite projects, and will likely be one of the first that I look to improve at some point in the future.