How to search for an ID in an array stored in a MySQL Database with PHP

Question

So I have an array stored in a database (serialized) with ID's from users.

array1 = serialize(array(1,2,3,4,5));

Let's say we have this like 100 times in the database with different ID's and array sizes (so 100 records)

array2 = serialize(array(6,2,8,1,3,10,12,60));
...

The arrays are stored in a database with the table name items and the row 'lookup'.

table 'items': (id,itemId,lookup)

Now I want to search those serialized arrays that match one ID (for example ID=2). For those that match, I want the itemId.

I could try a SQL query with "like %2%", but that would also match %22% etc. I could select everything and do some foreach() looping in every array, but that sounds very time-comsuming.

Any thoughts, idea's how to do this?

It doens't matter, whichever format is the best for the solution. — Nicolas., Mar 06 '13 at 12:42
@Jack What do you mean exactly with normalizing the database (in this situation)? Can you give an exanple? — Nicolas., Mar 06 '13 at 14:37
Storing comma separated data (or akin) inside a column onto which you need to query later is an anti-pattern. — Ja͢ck, Mar 06 '13 at 14:40
Ok, so you mean many to many relationships instead of storing an array. Good to know, thanks. — Nicolas., Mar 06 '13 at 15:16

score 0 · Answer 1 · answered Mar 06 '13 at 13:52

0

I'm not sure if this is useful but have you tried using the

if(in_array("yourObject",$yourArray)){

?

answered Mar 06 '13 at 13:52

Antoni Tescaro

19
2

Yes, this is better than the foreach looping I meant but still looks very time-consuming. – Nicolas. Mar 06 '13 at 14:36

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

Use like operator.

As you have written you could use "like %2%", but that would also select "22". The key is to insert your separation character in the string too.

Consider your serialized string looks like this:

";1;2;3;4;5;"

You can search for appearance of 2 for example with this column LIKE "%;2;%", this will not match 22. Don't forget to enter separation character at the very end and start of your serialized string.

Note: This technique could be pretty slow with long serialized strings. If you expect your table to have many rows, you should consider another scheme. Searching many-many relationship could be better

edited May 23 '17 at 10:32

Community

1
1

answered Mar 06 '13 at 14:02

Buksy

11,571
9
62
69

Thanks for the explenation about the like operator. They will be many rows and the array might get big, so the many to many relationship might be a solution. I'm going to read more about it. – Nicolas. Mar 06 '13 at 14:38

How to search for an ID in an array stored in a MySQL Database with PHP

2 Answers2