Now Hosted!

You can view my project here: My World Cup Project

Adding in the Goals

Now that I've loaded all the tournaments, the games are all in one user control, I've started to focus on adding some of the detail, the first area I've done this is Group F of South Africa 2010. As you can see, although the data is there, I need to adjust the formatting slightly, but it's coming along...

Charts and Reusable Controls

I've been working on the Main Tournament page over the past week, and trying to get some reusable controls to work propably, one is an expandable panel with JQuery, the other is a list of Games for that particular range. I've also been playing with the .NET 4.0 Charting, experimenting with different charts. Things are moving along, but at the same time, things are also slowing down as I fine tune the 19 tournaments.

1st Round of Data Uploads Complete!!

I finished the basic data upload today for all 19 World Cups, uploading all the teams and every game and result, along with the group tables and playoff brackets. I'm going to work with this data for a little while, before I go back to loading in the players and individual goal scorers.

This was quite challenging for some of the earlier tournaments, as all tournaments before 1994 you only got 2 points for a win, World Cup's 1950 and 1938 had some withdrawn teams, 1954 had group playoff games if teams finished with the same number of points,and the hardest problem, 1938 playoff games did not go to penalties, they replayed the game a few days later. I managed to solve most of the issues without changing the database, but I will have to spend some time on the website to show these exceptions correctly. Playoff replays are going to be the worst, mostly because there is just not that much room on the playoff table...

Updating the Tournament Format

As I mentioned in my last post, as I worked through the tournaments (now I have the basics of 1970-2010 loaded in the database), I realized that my simple tournament format table wasn't going to work. Without over engineering things, and after reviewing the formats of all the World Cups, I decided to create a new table that stored the content of each round, and then in the tournament_format table, I added a round_1_format_code, round_2_format_code, and round_3_format_code columns linking to this new table. The new table holds information about if the round is a group or playoff, and then if it's a group, the number of teams that advance from each group, and the overall number of teams that should progress from this stage. This helped to solve some of the 3rd place teams, (e.g. in Mexico 1986, there were 24 teams in 6 groups in the group stage, and the top 2 from each group went through, (12 teams), as well as 4 third place teams). This forced me to add a 'round_number' column (not to be confused with 'round_code') to the game table, to track what stage of the tournament we are in. So far it's all working great!!

Next time I'm going to talk about 2 other challenges I had, co-hosted tournaments (Korea & Japan), and the change in points (2 points for a win in all tournament from 1930-1990, 3 points for a win in all tournaments from 1994 - 2010.

Adding More Tournaments

Next I worked on importing some more tournaments. This was pretty straight forward. Using the Wikipedia pages, I was able to work backwards using my excel spreadsheets, making minor adjustments as the tournaments had less teams (1994 only had 24 teams, and then in 1982, I reached my first major round block, as the tournament had THREE rounds. At this stage I had to rework the group tables to allow multiple rounds, and then I had to rework the playoff stage so that it dynamically displayed the rounds as needed (as the 1982 final round only involved 4 teams). I finally got this all to work tonight. As you can see below, I also added a header and footers with 'Breadcrumbs' (with Master pages).


Next I'll be working on a Tournament summary, basic stats page.

Team Details

Following on from my last post, I made a team details page. This displays the team, it's flag, it's coach (and coach's nationality), Fifa ranking (if relevant) at the start time of the tournament, the teams summary at this world cup (it's games and results), and then a list of players. Getting the players for every team was interesting, but luckily there is a Wiki page for each tournaments squads, and I was able to save it into an Excel document and produce some formulas to insert in the players.

Next is producing some statistics. I would like to use some Chart controls (the 4.0 DotNet Framework have these built in now), and I'm starting to look at adding a master page to give me a header and navigation. For now I think I'm going to skip the player goals and cards, and come back to them later, so that I can get more than one tournament loaded (I'm torn between adding 1930 and 2006). Up until now I've had no trouble with the tables, but I can see that for the statistics, I need to create some views to summarize the data...

Playoffs

Following on from the group stages, I was able to import in the data required to build the playoffs. The complications with these games is that they can have extra time and penalty shootouts. Once these small problems were solved, the rest of the page started to fall together.


I'm almost ready to start building some stats, but next is a team page, (to display the teams results and stats), and importing in the players.

Importing Data

Over the weekend I imported in enough data into my database to start to build the actual World Cup website. I did this using Excel and some nifty formulas that imported in each team and their flag, and then, with some copying from Wikipedia and BBC.co.uk, I created another script to import in the details for each game.

Once this was created it was easy enough for me to throw together a simple page that shows each group's details. This page only has a dropdown list for the groups, a datagrid for the group table, and then another datagrid to display each game in the group.


Next: The playoff stages...

First Steps

I had a lot of ideas about how to display the results, how to import in the data and how I would store all the results. I had to make a couple decisions.

1. I'll use Visual Studio 2010 to create an ASP.NET website and SQL Server 2008.
2. I would start with the FIFA 2010 World Cup. The database would be designed to capture teams, scores, games, results, and in future, players, goal scorers, yellow/red cards and substitutions. I will be ignoring (at least for now) some stats such as passes, tackles, and assists.
3. I would import the data using a combination of various football websites (Wikipedia, BBC.co.uk, Soccernet.com, FIFA.com). I know that in the past there is some 'discussion' about facts, so I will be using official FIFA decisions (e.g. in the 1930 World Cup, there is still discussion between official FIFA documentation and independent researchers over who scored certain goals)

Taking this into account, I did a few rough sketches of what I thought the website would look like, and then I designed a rough database, loading it into my SQL database. The result of this is below.



What's next? Importing in some basic data...

A New Project Begins

As I was watching this years 2010 edition of the Fifa World Cup, I realized how much I love the statistics. I couldn't get enough and wanted more. I wanted to get at the raw data and process it to make my own charts, but it just doesn't seem to be available. After briefly (about 10 minutes) looking for jobs at a sports statistics company, I decided to throw together this project, a"World Cup Project". The idea is to compile all the results from all World Cups and produce some cool results. This blog will document the development process, and then I will hopefully have some interesting stats to show...

First interesting stat:
New Zealand was the only undefeated team at the 2010 World Cup, with 3 draws (even the winners Spain lost their first game).