Page 1 of 1

Select data when an ID is in an array

Posted: Sat Jul 17, 2010 9:05 pm
by webbox
Hi guys, here is my question:

I want to make a query like these (and don't worry about the sintax of the next query... I know is not the right one, this is just to give the idea):

Code: Select all

SELECT * FROM this_table WHERE category_field = 3;
Now, my problem is that the "category_field" contains an array like "1,3,15,43,...", which means that this registry apply for all those categories. I can't just use a

Code: Select all

SELECT * FROM this_table WHERE category_field LIKE %3%;
sentence since the array might have a 43 and therefore the registry would be selected and that would be incorrect, because this registry do not below to category "3"...

Also, I can't use

Code: Select all

SELECT * FROM this_table WHERE category_field LIKE %",3,"%;
since the 3 might be the first or last value of the array in the "category_field" and therefore it won't have a "," before or after the value...

Any ideas about how to make this? I would really appreciate your help! :banghead:

Re: Select data when an ID is in an array

Posted: Sat Jul 17, 2010 9:19 pm
by Gargoyle
you should properly normalize your data. put the ids in an extra table or use enum/set.

all other approaches will be totally useless if mysql has to evaluate many rows.

nevertheless, here's a solution based on what you got:

Code: Select all

SELECT * FROM this_table WHERE category_field LIKE %,3,% OR SUBSTRING(category_field,0,2)=='3,' OR  SUBSTRING(category_field,-2)==',3'

Re: Select data when an ID is in an array

Posted: Sat Jul 17, 2010 9:22 pm
by AbraCadaver
Well, I could give you a clever hack maybe, but the simple fact is that this is horrible database design. You need a related table with one category id per row, not multiple categories in one column. Are you open to changing the database?

Re: Select data when an ID is in an array

Posted: Sat Jul 17, 2010 9:28 pm
by webbox
Thanks Gargoyle for your help.
I agree with you both... about the DB design. I always use additional relation table to manage this kind of relations, but even it sucks, I need this to work in this way for an specific functionality.

Anyway, I appreciate your help I will work around you advice, Gargoyle.