0

I have a table which contains a field with ID numbers. If the ID number is on it's own then it's simply shown as 1 or 43 etc

If there are multiple ID numbers then they are separated by a comma eg. 4,67,123

If I wanted to get the results where the ID contained a number what would the best way to go about this be apart from selecting all the results, turning the field into an array and using in_array.

I've tried WHERE userId LIKE '%?%' but if I am searching for 4 and the ID is say 41, it returns a match which isn't correct. Nor can I use ',?,' as single numbers have no commas.

James
  • 23
  • 1
  • 6
  • Your column is a CSV? You should split it up so each record is separate. http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – chris85 Oct 09 '15 at 20:01
  • essentially yes, it's just a field of ID numbers and commas if required. I can filter results easily as I said above simply by getting all results, turning the column into an array by exploding with , and then checking for the ID in the array but I wondered if there was an easier way to do this from the base line – James Oct 09 '15 at 20:03
  • This is a common issue, and the short term solution is to use `WHERE FIND_IN_SET(4, userId)`. The correct long-term solution is to store the data with normaliztion instead of as a comma-separated list. https://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set – Michael Berkowski Oct 09 '15 at 20:03

0 Answers0