footballplayers
hello, im quite new at mysql but i want to maken a website where visitors can search for players.
the trouble is, i dont know what kind of criteria mysql uses to search.
its like this:
player 1, played for ajax, feyenoord, psv
for ajax from (july)1970-(july)74, for psv from 74-75, for feyenoord 75-77.
but: theres about 40 or 50 clubs.
there's about 55 years, competition started in 1955 uptill now.
there's for sure thousands and thousands of players.
so, what the best way to put up this database, concerning that:
-visitors can search on keyword: name of player, for exampe : Wim van Hanegem.
search for wim van hanegem. well, thats not too difficult.because he has an ID.
but more difficult: how to set this up if he played for 5 clubs?
make columns: club1, club2, club3, club4, club5?
but if so, you would not be able to search on clubs, would you? or can mysql recognize content? the content of a field i mean. if thats so i WOULD be able to say: search on ajax and show players.
-search for clubs and show all the players between , for example, 1960-1970.
so, do i need another table here?
-search on year: look for all players that played in 1960.
too bad i cant put in an image here, i could make it more clear. but ill try to point it out what i want
column1=id c2=name c3=club1 c4=club3 c5=club4 etc
id-1 hanegem ajax feyenoord psv
id_2 cruijff haarlem utrecht fc amsterdam
but where to put the years?
next table? ok, if thats so, can i use the ID again.
column 1 colum2 column3 column 4
id_1 1955-56 1956-57 1957-58
and a blank field in the years he did not play.
am still not able to see in which year wim van hanegem played for ajax, am i?
ok, i know i can do it much more simple.
for example 1 table with a texfield.
wim van hanegem
textfield: played for ajax 1955-1958 and for feyenoord 1960-1967.
and than : look for wim van hanegem.
but i would never be able this way to combine years and players.
how to set up this database?