Searching string split by commas in mysql

[Sharkadder]

Hi there,I currently have a mysql database which i would like to use to get values from a database. One of the columns in the database holds a value and if more than one value exists for an entry, the values are split up by commas for that row under the ClubID column. Here is a copy of my current query:

$query="SELECT * FROM FootballPlayers WHERE ClubID LIKE '%$squad' COLLATE utf8_bin ORDER BY CASE WHEN Positions LIKE 'GK%' THEN 1 WHEN Positions LIKE 'RB%' THEN 2 WHEN Positions LIKE 'LB%' THEN 3 WHEN Positions LIKE 'SW%' THEN 4 WHEN Positions LIKE 'CD%' THEN 5 WHEN Positions = 'D' THEN 6 WHEN Positions LIKE 'RW%' THEN 7 WHEN Positions LIKE 'LW%' THEN 8 WHEN Positions LIKE 'DM%' THEN 9 WHEN Positions LIKE 'CM%' THEN 10 WHEN Positions LIKE 'AM%' THEN 11 WHEN Positions = 'M' THEN 12 WHEN Positions LIKE 'FW%' THEN 13 WHEN Positions LIKE 'ST%' THEN 14 WHEN Positions = 'A' THEN 15 ELSE 16 END";

Ok i would like the query above to search the database and to show items where ClubID is like $squad. $squad is always a number or more than one number split up by commas, e.g. 100 or 1,2,3,4 etc.The rest of the query is fine where it displays the rows depending on what playing position they equal. The problem is that if $squad is equal to a range of numbers split up by commas they it will not find anything and just show blank. e.g. if $squad = "2" and i am looking at ClubID column for a ClubID which contains "2" then nothing seems to get displayed as ClubID might say "1,2" or "2,8" etc. My question is, how can i search the database so that after the WHERE clause i can see if part of the string $squad is in the column ClubID for the current row.I have tried using LOCATE and FIND_IN_SET but i cannot appear to get them to work, maybe the right syntax is all i need :-). I am using mysql version 5.0 and the variable i search the database with is created using PHP. Any help is apprechiated.Thanks,Mark

[justsomeguy]

If you have comma-separated values, you need to use four conditions to check:WHERE ClubID = '$squad' OR ClubID LIKE '$squad,%' OR ClubID LIKE '%,$squad,%' OR ClubID LIKE '%,$squad'That's one of the reasons why people usually use a lookup table to store relationships like that instead of storing multiple values in one field.

[Sharkadder]

thanks dude it worked a charm...always helpful and full of the answers.I did contemplate a lookup table but this way works ok :-).

[CCZ]

You actually only need one condition to check, if you concat a comma to both sides of ClubID, like this:

WHERE concat(',',replace(ClubID,' ',''),',') like '%,$squad,%'

That way it won't matter at what position $squad is in the row of values in ClubID. BTW, I also removed spaces from ClubID, if any.