PHP/Mysql: Searching serialized data for a string [duplicate]

Question

I have started work on a project that someone else created, this is what he has done:
In a mySql table he has a TEXT field called "allData", in that field he has serialized records that look like this

a:13:{s:12:"currencyCode";s:3:"SEK";s:11:"senderEmail";s:18:”[email protected]";
s:4:"HASH";s:32:"ebdeb85d094cefd102b630fa9e69d9ca";}

(There's a lot more data but I cut it down by around 95% so it would not mess up this page)

Any idea how I can search the above data for senderEmail or HASH?

Are you trying to search out the email from multiple rows or do you just need to extract the data from one row? @Ryan — SalientGreen
– SalientGreen, Commented Sep 25, 2015 at 21:06
O would consider changing the table structure to have a filed for each information — Julio Soares
– Julio Soares, Commented Sep 25, 2015 at 21:07
If you want to search for those specific fields in the database, you'll be better off in the long run storing them in their own proper columns (perhaps additionally to the serialised data, though that'll depend on the overall design.) Otherwise you'll end up with not-entirely-great kludges. Certainly any other way will be slower, especially for TEXT fields with lots of data in them. — Matt Gibson
– Matt Gibson, Commented Sep 25, 2015 at 21:07
If it was just one row it wouldnt be a problem, there are a few hundred rows :( — Ryan
– Ryan, Commented Sep 25, 2015 at 21:15

Community · Accepted Answer · 2017-05-23 12:29:33Z

3

SELECT * FROM table WHERE allData LIKE '%"[email protected]"%' AND allData LIKE '%"ebdeb85d094cefd102b630fa9e69d9ca"%' LIMIT 1;;

This example should allow you to search for a specific HASH and senderEmail in the table and return that entry if a match of both fields exist in the serialized data. This would be useful to put into a view so that it can be retrieved quickly and easily, and you of course can see where you would want to change the email and hash in the query. In this example it will return that exact entry.

More information:

MySQL Like Function

Search inside serialize data with MYSQL

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Sep 25, 2015 at 21:13

Robert Mennell

2,05214 silver badges25 bronze badges

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

7 Comments

Ryan Over a year ago

If it was just one row it wouldnt be a problem, there are a few hundred rows :(

Robert Mennell Over a year ago

@Ryan are you looking for a specific email or do you just want entries with that pattern in it?

Ryan Over a year ago

Specific email or hash.

Robert Mennell Over a year ago

then let me modify my answer for you.

Ryan Over a year ago

Thanks, selected your answer :) I think I'm going to try to convert that into normal fields though, this seems like a pain in the behind... any suggestions?

|

Cjmarkham · Accepted Answer · 2015-09-25 21:03:44Z

0

You can retrieve the data normally. Then, use unserialize to turn it into an array that you can search.

answered Sep 25, 2015 at 21:03

Cjmarkham

9,7336 gold badges52 silver badges85 bronze badges

3 Comments

Cjmarkham Over a year ago

Any explanation for the downvote?

VolkerK Over a year ago

Not my down-vote, but: If you do that, you don't need a (relational) database as you'd transfer all the data "into" the php instance anyway.

Ryan Over a year ago

There are hundreds of rows, and new ones being added nearly every day... just ding one lookup would take ages, wouldnt it?

Collectives™ on Stack Overflow

PHP/Mysql: Searching serialized data for a string [duplicate]

2 Answers 2

7 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Linked

Related