Help with designing the perfect football/soccer player stats database

[JJohnsenDK]

Hey

I have a site about the local football club i support and i want to make a database for all the players who played in that club. The stats are many like Number of matches played for the club, number of goals, what position, when was his debut and so on, what season did he play in the club.

I allready have a database with the players, but it sucks. I store all the data in the same table. Cant really think if there is a smarter way so here is where you guys comes into the picture  :).

I hope someone can help me out.

[cmgmyr]

I would start off by doing something like this:

Players
-------
player_id
fname
lname
team_id

Teams
-------
team_id
name
coach

Games
-------
game_id
home_id
visitor_id

Stats
-------
stat_id
game_id
player_id
more stat information...

I hope this gets you going in the right direction. The main thing that you want to think about when working with databases is that you don't want duplicate information, like in the stats table you don't want the player name...you aleady have that in the players table.

Please let me know if you have any other questions

-Chris

[JJohnsenDK]

A bit late answer but here it goes anyways.

I tried to setup the database as you wrote, but now i come to the coding. What im trying atm is to setup a fixture for all the games.

Im thinking that i need to use this code to join team.name and game_id together:

[code]
<?php
include('config.php');
$get_sql = "SELECT
game.game_id,
game.home_id,
game.visitor_id,
team.team_id,
team.name
FROM
game
LEFT JOIN
team
ON
game.home_id = team.name" or die(mysql_error());

$get_que = mysql_query($get_sql);
$get_fet = mysql_fetch_array($get_que);

echo $get_fet['home_id'];
?>
[/code]

I havent really worked with LEFT JOIN before so maybe thats why it wont work.

Im using the tables from above and what i want is to show the two teamnames, home team(home_id) and visitor team(visitor_id) one at the time. You know a fixture where you see which teams are playing each ohter.

Hope someone can help

JJohnsenDK

[cmgmyr]

See what this does. I think you got a little mixed up  :)

[code=php:0]<?php
include('config.php');
$get_sql = "SELECT
game.game_id,
game.home_id,
game.visitor_id,
team.team_id,
team.name
FROM
game
LEFT JOIN
team
ON
game.home_id = team.id" or die(mysql_error());

$get_que = mysql_query($get_sql);
$get_fet = mysql_fetch_array($get_que);

echo $get_fet['name'];
?>[/code]