Basketball fixture database – advice required

[Sparky]

Hi there everyone,

I am in the process of making a website for our basketball club. One of the goals of the website is to get as much information into MySQL databases, and created dynamically using PHP scripts. Not just news items, but fixtures, results and player statistics.

I am trying to tackle the fixtures database first. On various area’s of the site, we would need a complete fixture list (both upcoming, and played fixtures), which would include:-

Date
Opponent
Home/away
Venue address
Type of fixture (league game/cup game/friendly)
Result
League points (3 for a league victory, 1 for a league defeat).

To do this, I have created a database, with the following fields/types, which is displayed using a PHP script:-

ID - int(11) - Primary
Date - timestamp(10)
Opponent - char(255)
HA - char(255)
Venue - Game(255)
ResultUs - int(11)
ResultThem - int(11)
Points - int(11)

Now that in itself works fine (see here - http://www.guildfordbasketball.com/matches/#fixtures). What I would like to do now is create a little table that shows our current standings:-

Games played
Games won
Games lost
Points for
Points against
Difference
League points

I have created half the script to display these numbers (see here - http://www.guildfordbasketball.com/matches/). I can work out the points for, points against and league points using “SELECT SUM(ResultUs) AS points_for” and so on. The difference is done by the subtraction of the sum of ResultThem from ResultUs.

Now, for the games played, won and lost. This is where I start to get unsure of things - should I just create three new columns in the table, for:-

GamePlayed
GamesWon
GamesLost

Then I can work out the Games played, Games won and Games lost just by more SUM() selects? Should I have a point’s column in the table, is this something that could, or even should be worked out dynamically? What about the fixture type? I don’t want friendly games or cup games going into the league standings. Should I create an int value, and assigning a separate value for the different types of games, and then using a switch statement to distinguish between the two?

There are two other simple things I would like to do with the fixtures table. On the front page of the site, I would like to have a simple script that displays the results from the last match, and then on a separate part of the page, to display the details of the next match (see RHS here http://www.guildfordbasketball.com/, along with another league standings table).

Could this be done using the GamePlayed field, finding the first 0 value and taking that as the upcoming fixture, and then the last 1 value, and displaying that as the last fixture? Or could I just do this from the timestamp field?

Thanks in advance for any help with this!

[Velox Letum]

If the Games Won and Games Lost is simply 0 or 1, you could use SUM(). The fixture type should be a numeric value, and then use a switch statement to determine the value. For the league standings, if you don't want the other types, just use a WHERE clause. You can easily get the last game with the latest date and a value for "already played", then you can just ORDER BY date DESC LIMIT 1. As for the future game, use a WHERE clause: WHERE date > NOW() LIMIT 1

[Sparky]

Hi there Velox. I have appended the tables, and modfied the scripts and everything seems to work great. Now all of the standings table is created via the scripts, and the !next game/last game" features are done via scripts from the fixtures database. Many thanks for your help.