Select data when an ID is in an array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
webbox
Forum Newbie
Posts: 3
Joined: Sat Jul 17, 2010 8:40 pm

Select data when an ID is in an array

Post 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:
Gargoyle
Forum Contributor
Posts: 130
Joined: Wed Jul 14, 2010 12:25 am

Re: Select data when an ID is in an array

Post 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'
Last edited by Gargoyle on Sat Jul 17, 2010 9:34 pm, edited 1 time in total.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Select data when an ID is in an array

Post 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?
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
webbox
Forum Newbie
Posts: 3
Joined: Sat Jul 17, 2010 8:40 pm

Re: Select data when an ID is in an array

Post 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.
Post Reply