0

I'm wanting to create a sql select statement that will grab rows if a given value is in a comma separated list in one of the columns of the database table.

Example Table...

id | courses
1   | 5, 8, 15, 19


I want to do something like this
$course_num = 5;
$sql = "SELECT * FROM courses WHERE $course_num IS IN courses";

1.) I don't think the "IS IN courses" part is legit. How can I do this?
2.) For my code above, I would want to return the row because of the "5" in courses, not because of the "15". So, if $course_num = 9 no rows should be returned.

Thanks for your help.

gtilflm
  • 1,389
  • 1
  • 21
  • 51
  • 1
    This is a good example of why storing multiple values in one column is difficult to deal with. – Don't Panic Dec 22 '15 at 21:55
  • 2
    You're right, `IS IN` isn't the right syntax in MySQL, just `IN()` http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in - You might find that `FIND_IN_SET()` may also work for you http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set - Unless you're using MSSQL, where `IS IN` is valid syntax. – Funk Forty Niner Dec 22 '15 at 21:57
  • new table: id,courses, 1|5, 1|8, 1|15 etc –  Dec 22 '15 at 21:57
  • FIND_IN_SET() is not a good alternative, cause it can match any occurrence in set of column data. that can get a row containing 33 when you search only 3. personally i'll looking to restruct schema of data, of using "like '%,1,%'" is more accurate – MTroy Dec 22 '15 at 22:00
  • "FIND_IN_SET()" ended up being what I needed. Thanks @Fred-ii-! Want to add a solution so I can mark this as answered? – gtilflm Dec 22 '15 at 23:24
  • @gtilflm you're welcome. Barmar did find a duplicate question, so all's good, *cheers* – Funk Forty Niner Dec 22 '15 at 23:30

1 Answers1

0

By adding comma in searched occurrence

SELECT * 
FROM courses 
WHERE concat(', ',course,',') like '%, 5,%'
MTroy
  • 897
  • 9
  • 20