CODEWITHSUNDEEP
phpmysql
G-J
G-J

Reputation: 1068

mysql search a column containing an array

As I search for results I find a lot of answers were the mysql contains the needle and PHP provides the haystack... any array.

I need it the other way around. My table has a column called supervisor_ids containing an array such as this...

a:2:{i:0;i:9999;i:1;i:1;}

In my PHP code I have a value saved in a variable... $supervisor

How do I search the 'supervisor_ids' column for $supervisor?

I would assume something like...

SELECT * FROM my_table WHERE $supervisor IN 'supervisor_ids' but that doesn't work

UPDATE

I just found out that if the table column contains an list like 9999,1,5,678 as opposed to an array... a:2:{i:0;i:9999;i:1;i:1;}

this would work...

SELECT * FROM my_table WHERE FIND_IN_SET($supervisor,supervisor_ids)

What is the equivalent to search it when it is an array?

Upvotes: 2

Views: 2918

Answers (1)

Dominick Navarro
Dominick Navarro

Reputation: 752

You can do it with something like this:

SELECT * FROM my_table WHERE supervisor_ids LIKE "%i:$supervisor;%";

if the value of $supervisor in PHP is 9, the actual query will be:

SELECT * FROM my_table WHERE supervisor_ids LIKE "%i:9;%";

and will match all serialized arrays that are like this:

a:...:{...;i:9;...;}

Assuming supervisor_ids column contains only PHP serialized arrays, this should work fine.

Upvotes: 1

Related Questions