mysql search a column containing an array

Question

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?

You can try "SELECT * FROM my_table WHERE 'supervisor_ids' LIKE '%" . $supervisor . "%' ". The LIKE operator will search for a pattern and the % acts as a wildcard matching 0 or more characters. The REGEXP operator could also work — tshimkus
– tshimkus, Commented Mar 9, 2019 at 1:51
is this is a regular thing, you need to normalise the data, storing data this way leads to all sorts of difficulties en.wikipedia.org/wiki/First_normal_form — user10051234
– user10051234, Commented Mar 9, 2019 at 1:52

Dominick Navarro · Accepted Answer · 2019-03-09 02:22:55Z

1

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.

edited Mar 9, 2019 at 2:22

answered Mar 9, 2019 at 1:52

Dominick Navarro

7526 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dominick Navarro Over a year ago

"i" is static string, from serialized array, assuming $supervisor are all integers, your arrays will always have "i" before the actual id value as a hint that the value describes an integer.

Nick Over a year ago

If $supervisor is 9, your code will also match 94 or 967 or ...

Nick Over a year ago

That's good but your UPDATE code is incorrect, as it is a string value you need to use FIND_IN_SET as OP did in the edit to the question.

Collectives™ on Stack Overflow

mysql search a column containing an array

UPDATE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related