Create records based on values from other tables

[UltimateNova]

Hi,

 

I am a bit of a MYSQL newbie so please bear with me. I have a database created about football/soccer stats. The database at the moment contains the following tables but more maybe added:

 

Players

Clubs

Seasons

Competitions

 

I will create php pages for these tables that add, edit and delete records for each. But the problem I can't get my head around is how to add a record that uses data from other tables, for example we would add a...

 

player -> club-season-competition

 

Lets say we want create a player called 'Joe Bloggs' and want to add his details, we need somehow for the page to display an option for club, season and competition. Maybe they can be drop down boxes but how does one fill those drop down boxes with records that are already added in the database for each table?

 

Thanks

[Frank_b]

Hello,

 

This is a common question for new mysql users.

 

So a player can be member of a club right? If we inverse that than we would say that a club has multiple players. We call this a one-to-many relation. (try to google it)

 

 

The storage in the database is simple:

Since a player can only be member of one club we just have to make a column 'club_id' that where we will store the unique id of the club where the player belongs to.

players:
----------------------------------
id | name   | born       | club_id
----------------------------------
1  | Bloggs | 1996-05-29 | 33

clubs:
-----------------------
id | name | city      |
-----------------------
33 | Ajax | Amsterdam |

[Frank_b]

The id is the 'primary key' we often like to use the autoincrement option so that it counts up automaticly if we insert a new record.

The club_id is a foreign key. To optimize your database add an index key to this column.

 

besides one-to-many relations there are some other ones. the one-to-one relation is straightforwards and i will not discuss it here. Than there is a many-to-many as well.

 

Lets take persons and teams. Imagine that we have a player in team A that besides of playing football in team A also coaches a younger team B.

 

So one person can be connected to more than one team. And one team exists about multiple persons. That is a many-to-many relation.

 

But how do we store many to many relations?

persons:
----------------------------------
id | name   | born       | team_id
----------------------------------
1  | Bloggs | 1996-05-29 | 12

teams:
----------------------
id | name  | club_id |
-----------------------
11 | team A| 33      |
12 | team B| 33      |

person_team
----------------------
 person_id | team_id |
----------------------
 1         | 11      |
 1         | 12      |
 

The last table we call it a join table. As you can see in this table there are two records for person nr 1. Person nr 1 is active in two teams.

 

to SELECT data from more than one table, google on mysql JOIN.

[UltimateNova]

Thanks for the reply!

 

You have answered part of my problem but not fully. What I want is add player php page, which would have the following:

 

Name, Date of Birth, Height, Weight, etc

 

But then I need to add a career to the player, you say above that a player can only have one club, that is not necessarily true, one player could play for 2/3 clubs in one season, so I would need some solution to the second part of the player php page that adds a season and a club to the player with their appearances and goals as well.

 

Season: [2014/15 Premier League] Drop Down Select from seasons table

Club: [Arsenal] Drop Down Select from clubs table

Apps: [10] Manual Input

Gls: [5] Manual Input

 

Then we submit and it is displayed on the page something like this, lets say other seasons were added.

 

2014/15 Premier League - Arsenal - 10 - 5

2013/14 Premier League - Arsenal - 37- 3

2012/13 Premier League - Arsenal - 14- 2

2012/13 Premier League - Chelsea - 11- 2

 

Thanks

[Frank_b]

Well if you want to be possible to enter more than one club you will get a many-to-many relation as well. That means that you will get a join-table player_club.

 

I am not sure about the seasons.

 

Season: [2014/15 Premier League] Drop Down Select from seasons table

 

That will make no sense: adding fields to seasons from seasons..

 

I think i should make no seasons table. seasons are dynamic. you can use a extra field in the join table to retrieve the season.

 

What kind of thing is a Premier league? That is a competition i think.

 

so make a table competitions and a table player

Then make a join table with one extra column: a season field with type integer. Enter 2014 for the season 2014/15. With a small function you can change 2014 to 2014/2015 when you retrieve information from the database

 

competition:

id

name

 

 

competition_player

comp_id

player_id

season (type: int)

 

 

player

id

name

born

height

weight

 

Now you will be able to use every competition like PREMIER LEAGUE multiple times with the same player where you can add a season for every entry

 

like this:

 

Player season competiton

1         2012     5

1         2013     5

1         2014     5

 

Edited October 20, 2014 by Frank_b

[Barand]

I'd go with something like this below. Players can change clubs so hold the start and end dates that they are at the club. (The end date for player at current club would be 9999-12-31)