CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
user2779986
user2779986

Reputation: 53

SQL WHERE if field is serialized

I've got a problem which needs to be solved... I have table where one of the fields is serialized (field: s_field). It's just an array of id's. But now I need to get all data entries WHERE s_field = 5.. So I have entries where s_field is serialized(array(4, 5, 6)); and there are tons of these entries... so how do I get - using sql - all entries where one of the s_field id's are id == 5;

Hope you understand what I mean

And I don't think that getting all entries and each of them unserialize and check is not the best thing to do, because there are LOTS of them

Upvotes: 1

Views: 318

Answers (2)

Sébastien
Sébastien

Reputation: 12139

Assuming your entry really looks like serialized(array(4, 5, 6)); you could find a '5' with this query:

a:3:{i:0;i:4;i:1;i:5;i:2;i:6;} 

SELECT some_field WHERE s_field LIKE '%, 5,%'

However the better solution would be to unserialize all that data and put it in relational tables.

EDIT: As @kyra pointed, this won't work for the first and last item of the array.

So if you get no result with the first query you could then try:

SELECT some_field WHERE s_field LIKE '%(5,%'

And then finally:

SELECT some_field WHERE s_field LIKE '%, 5)%'

If you have mostly fields with only one element you should start with the second query.

EDIT:

Of course if the serialized data actually looks like this: a:3:{i:0;i:4;i:1;i:5;i:2;i:6;} you can locate the '5' like this:

SELECT some_field WHERE s_field LIKE '%i:5;%'

And this way it should work even for the fisrt and last items.

Upvotes: 0

Mina
Mina

Reputation: 1516

You can try adding the zerofill attribute to the ID field so you will have, say, 0000005 rather than 5, then using LIKE to match the for 0000005

Upvotes: 1

Related Questions