Linking to table together?

[HeinekenBeer]

Hey

im trying to make a stat site for football(soccer) players. I have a stat archive where players from the season(just examples) 2005 is stored in 2005 and players from 2004 is stored in 2004 and so on. And that works fine. I also want a "best team of the season" in for example 2005. This "best team of the season" contains 11 players for each season and we are talking season way back, to like 1955. So in my database i made a table contaning this for my "best team of the season":

table: wof_best
rows: wof_best_id, wof_best_pos
in wof_best_pos i created the 11 postions on the football field, goalkeeper, defence1, defence2 and so on like this:

gk, d1, d2, d3, d4, m1, m2, m3, m4, a1, a2.

Then i have a table containing all the player stats, where i have a row that should indicate if the player have been selected for the team and what position he have been selected in.

table: wof_pl
row: wof_pl_best

And here is where i go blank cause i cant figure out how to link these to tables together, so if i write for example a1 in wof_pl_best the players is showed as attacker1 in "best team of the season".

Long thread, but i really hope someone can help me.

[WDPEjoe]

Here's one way to do it:

In your table that contains all of the stats, there should be a player ID column that is a primary key.

Then you could have another table where the columns are
year, gk, d1, etc...

Each row is a different year and then the player IDs of the players that were selected for each position.

Let's look at an example:

playerID, name, statCol1, statCol2, etc...
1, Adam Acosta, ...
2, Freddy Adu, ...
3, Nelson Akwari, ..
4, Chris Albright, ...

And so on...

Now, if we put these into a hypothetical "best team" row for 2006, that table would look like:

year, gk, d1, d2, etc
2006, 2, 4, 1, 3, ...

This would mean that Adu is gk, Albright is d1, Acosta is d2, etc.

Hope this helps.

[ahundiak]

HeinkenBeer,

You might want to post your table schemas with a bit more detail. I'm having trouble understanding your question. On the other hand, I always tell my players that when the other team has the ball then everyone is a defender.