Find any or all in a set?

we5inelgr

Hi all,

I'm trying to write a query that will find any or all in a set in a MySQL table.

The table set contains (0,1,2,3,4,5,6,7,8,9,10) as possibilities.

The web page has checkboxes with values that coorespond to each of the possibilities.

If the user checks boxes with values 0 and 1 (for example), I would like to return records that contain 0 and/or 1 (but not necessarily 10, unless of course the record also has a 1 in it).

What I've got now is

LIKE'0%1%'

But that also returns records that have 10, but not 1.

The same results occur if I do this

LIKE'0%1'

How can I accomplish the above question? Is there some sort of "IN" statement that can be used? Like ("0", "1") IN COLUMN type thing?

Thanks.

Derokorian

You already know the answer... use the IN function.

SELECT <columns> FROM <table> WHERE <column> IN (0,1,...,n)

johanafm

It seems you are storing those numbers as a comma separated string… then you are doing it wrong. The solution would be to break that column out into a related table with (id-of-other-table, value), and then join the two tables as needed. And suddenly the solution would be back to where column IN ().

For the reasoning behind this, read about database normalization.

we5inelgr

The column is of data type "set" for those. So when numbers are stored, they are automatically stored with commas between them.

I hadn't thought about breaking the column out into it's own table. Actually, there are three columns like this one in the same table and the table itself is somewhat large. Perhaps I'll look into this approach more.

Thanks all!

traq

The SET datatype is nonstandard; it is basically the same idea as breaking the info out into a separate table and using foreign keys. I would recommend doing things the "standard" way, but if you want to keep the SET, I would recommend using the FIND_IN_SET function instead of LIKE.

we5inelgr

Thanks for the additional suggestion. When I query with:

FIND_IN_SET(`The_Column` ,'1,5')

It will return records with only 1 or only 5 and not records that contain both or records that contain one/both as well as other digits.

Looks like I'm going to be going with breaking those 3 columns out into their own tables. Seems like the best solution long term. Thanks again.

Derokorian

try, FIND_IN_SET(col, 1) AND FIND_IN_SET(col, 5)... for example. You want to find both numbers, so you need a separate check for each number.

we5inelgr

Hmm, if I use FIND_IN_SET(col, 1) AND FIND_IN_SET(col, 5), it returns zero records even though I know many records exist with both in the set.
If I use FIND_IN_SET(col, 1) OR FIND_IN_SET(col, 5), it returns records that have only 1 or only 5 in them (similar to FIND_IN_SET(The_Column ,'1,5') above) and not records that have 1 and others or 5 and others in the set.

Thanks for the suggestion though.

Derokorian

Hmm, weird... I guess this is why I (and many others) don't use the SET column type. Sorry I'm less than helpful....

bradgrafelman

Appears that you've got the parameter order for FIND_IN_SET() reversed; should be more like FIND_IN_SET(val, col).

we5inelgr

No worries, thanks for your help. I do appreciated it!

I've learned something though, and that is SET column types are not easy to work with and are probably limited to a very narrow/specific need (at best).

Cheers! 😃

mywish1

i need some help in my mp3 search engine site if any body can help me