I currently have the following row in my table:
course_data: user_id days <-- This is a varchar column. 405 1,3,5
and I am trying to implement the following SELECT statement:
SELECT usrID, usrFirst, usrLast, usrEmail FROM tblUsers WHERE usrID NOT IN ( SELECT users.usrID FROM `course_data` courses, `tblUsers` users WHERE days IN ('$day') ) GROUP BY usrID ORDER BY usrID
Basically, I want that row (with user 405) to be omitted if the $day variable includes a '1, 3, or 5'.
For example, if
$day = "1", it should return an empty query (because the number "1" is in the column "1,3,5").
However, I have not found this to be the case. Even though
$day = "1", it still returns that row.
The only way that it won't return the row is if
$day= "1,3,5." Yet, I thought that the IN() clause would take any part of my variable and apply it to that column.
Any insights on what I'm doing wrong here? Thanks.
You should use the
like keyword to do a partial match of the char field:
where days like '%1%'
Edit: VolkerK and Lukas Lalinsky both suggest MySQL's
find_in_set, which is probably better than
like for what you want to do. However, the following recommendation about normalizing your database still applies.
However, you should not store multiple values in a single database field. Instead, consider using two tables:
course_data: user_id course_days: user_id day_number
Then, you would have the following data:
course_data: user_id 405 course_days user_id day_number 405 1 405 3 405 5
You can then correctly query this schema. For example:
select cd.user_id from course_data as cd where cd.user_id not in ( select course_days.user_id from course_days where course_days.user_id = cd.user_id and course_days.day_number = 1 )
(or, that should be close; I'm not exactly sure what you're trying to accomplish or what the rest of your schema looks like, so this is a best guess).