Correct syntax to search a set?

Find if record's set of categories contains a '2'?

[mlkarie]

This is such a basic question that one feels silly to ask it, but many hours of work has now made me unable to think:

The database has a field called "category"; the values in "category" are '1','2','3' etc up to 30

An actual record in the database has a category entered as 2

What is the SELECT statement to search for all records that contain a 2 as a category? (records can contain more than one number in their categories, i.e. a record can have categories 2,4,7)

SELECT * FROM mytable WHERE '2' IN CATEGORY

?

[eaden]

if they have quotes around the numbers ( as you have above ) then you could use

SELECT * FROM mytable WHERE CATEGORY LIKE "%'2'%"

Thats how I'd do it, but I've never used the "IN" thing before. I'd be interested to see it done that way.

[jatar_k]

Maybe take a look at this as well
MySQL String Comparison Functions [mysql.com]

[mlkarie]

The categories were entered into the database in a field of type SET, and I used single inverted commas, because PHPMyAdmin tells you to enter it like that.

From PHPMyAdmin: enter as follows: 'a','b','c'

and so on

All I want are categories that are represented by numbers.

The IN operator is one of those that I teach my school kids everyday when we program in Pascal or Delphi. It's a fairly universal operator to look whether a certain value is found in a set.

Normally I would say:

if (myvalue IN myset) { blah-blah }
and myset would be declared previously as ['a','f','k']

So the whole point of using a set in the MySQL database is that it's MySQL's job to see if the value the user entered is in that set or not.

I checked the MySQL documentation; it gives good examples for SELECT, but it's examples under WHERE are not comprehensive enough.

Have to solve this problem, though, as it's crucial to the entire set of PHP files that I'm writing for a client's website.

I'll have a look at the string functions; still, MySQL is supposed to search the set and return the applicable records.

[mlkarie]

Thanks, Eaden, your syntax worked in my script. I'm happily onto the next part of the job.

:-)