Mysql search querry

[vibrant]

I have a table called 'games' for a football website. This table contains information, results and statistics about several football matches.

Among the fields in this table are 'goals' which contains the goalscorers in each match. Each goalscorer in this field are separated with a comma, like this


Code:
Player 1,Player 2,Player 3

Now, what I would like to do is to count all the goals that 'Player 1' has scored. In other words, count all the games where field 'goals' contains 'Player 1'. I've tried something like the below, but that obviously didn't work, as it only returns the games where field 'goals' ONLY contains 'Player 1' and no other names.


Code:
SELECT FROM games WHERE goals = 'Player 1'

Has anyone got a clue on how to do this? And, is it even possible to do it this way, or do I have to make a separate table with all the goalscorers in separate rows? -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[uruguay]

Hey vibrant

Yes what you would do is normalize your table instead of doing it the way you are. Do not store multiple values in a single column.

Create a table with say gameid and playerid in it and list a single row for each playerid and the gameid he scores a goal in. If Ronaldo scores three times in a match he would have rows in the table for instance.

If you show the layout of your table(s) I or someone else can take a look and see if you need to normalize anything else.

hope it helps -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --