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).
Remove the Quotes in the IN Statement. The Syntax is:
... WHERE column IN (1,2,3)
and not as you used it
... WHERE column IN ('1,2,3')
Also see the documentation on IN, there are more examples.