mysql select query within a serialized array

As GWW says in the comments, if you need to query things this way, you really ought to be considering storing this data as something other than a big-ole-string (which is what your serialized array is).

If that's not possible (or you're just lazy), you can use the fact that the serialized array is just a big-ole-string, and figure out a LIKE clause to find matching records. The way PHP serializes data is pretty easy to figure out (hint: those numbers indicate lengths of things).

Now, if your serialized array is fairly complex, this will break down fast. But if it's a flat array, you should be able to do it.

Of course, you'll be using LIKE '%...%', so you'll get no help from any indicies, and performance will be very poor.

Which is why folks are suggesting you store that data in some normalized fashion, if you need to query "inside" it.

If you have control of the data model, stuffing serialized data in the database will bite you in the long run just about always. However, oftentimes one does not have control over the data model, for example when working with certain open source content management systems. Drupal sticks a lot of serialized data in dumpster columns in lieu of a proper model. For example, ubercart has a 'data' column for all of its orders. Contributed modules need to attach data to the main order entity, so out of convenience they tack it onto the serialized blob. As a third party to this, I still need a way to get at some of the data stuffed in there to answer some questions.

a:4:{s:7:"cc_data";s:112:"6"CrIPY2IsMS1?blpMkwRj[XwCosb]gl<Dw_L(,Tq[xE)~(!$C"9Wn]bKYlAnS{[Kv[&Cq$xN-Jkr1qq<z](td]ve+{Xi!G0x:.O-"=yy*2KP0@z";s:7:"cc_txns";a:1:{s:10:"references";a:1:{i:0;a:2:{s:4:"card";s:4:"3092";s:7:"created";i:1296325512;}}}s:13:"recurring_fee";b:1;s:12:"old_order_id";s:2:"25";}

see that 'old_order_id'? thats the key I need to find out where this recurring order came from, but since not everybody uses the recurring orders module, there isnt a proper place to store it in the database, so the module developer opted to stuff it in that dumpster table.

My solution is to use a few targeted SUBSTRING_INDEX's to chisel off insignificant data until I've sculpted the resultant string into the data gemstone of my desires. Then I tack on a HAVING clause to find all that match, like so:

SELECT uo.*,
SUBSTRING_INDEX(
 SUBSTRING_INDEX(
  SUBSTRING_INDEX( uo.data, 'old_order_id' , -1 ),
 '";}', 1),
'"',-1) 
AS `old order id`
FROM `uc_orders AS `uo`
HAVING `old order id` = 25

The innermost SUBSTRING_INDEX gives me everything past the old_order_id, and the outer two clean up the remainder.

This complicated hackery is not something you want in code that runs more than once, more of a tool to get the data out of a table without having to resort to writing a php script.

Note that this could be simplified to merely

SELECT uo.*,
SUBSTRING_INDEX(
  SUBSTRING_INDEX( uo.data, '";}' , 1 ),
'"',-1) 
AS `old order id`
FROM `uc_orders` AS `uo`
HAVING `old order id` = 25

but that would only work in this specific case (the value I want is at the end of the data blob)

You can do it like this:

SELECT * FROM table_name WHERE some_field REGEXP '.*"item_key";s:[0-9]+:"item_value".*'

But anyway you should consider storing that data in a separate table.

Working with php serialized data is obviously quite ugly, but I've got this one liner mix of MySQL functions that help to sort that out:

select REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(searchColumn, 'fieldNameToExtract', -1), ';', 2), ':', -1), '"', '') AS extractedFieldName
from tableName as t 
having extractedFieldName = 'expressionFilter';

How about you serialize the value you're searching for?

$sql = sprintf("select * from tbl WHERE serialized_col like  '%%%s%%'", serialize($n));

or

$sql = sprintf("select * from tbl WHERE serialized_col like  '%s%s%s'", '%', serialize($n), '%');

Simply use the IN statement, but put the field itself as array! Example:

SELECT id, title, page FROM pages WHERE 2 IN (child_of)

~ where '2' is the value i'm looking for inside the field 'child_of' that is a serialized array.

This serialized array was necessary because I cannot duplicate the records just for storing what id they were children of.

If I have attribute_dump field in log table and the value in one of its row has

a:69:{s:9:"status_id";s:1:"2";s:2:"id";s:5:"10215"}

If I want to fetch all rows having status_id is equal to 2, then the query would be

SELECT * FROM log WHERE attribute_dump REGEXP '.*"status_id";s:[0-9]+:"2".*'

There is a good REGEX answer above, but it assumes a key and value implementation. If you just have values in your serialized array, this worked for me:

value only

SELECT * FROM table WHERE your_field_here REGEXP '.*;s:[0-9]+:"your_value_here".*'

key and value

SELECT * FROM table WHERE your_field_here REGEXP '.*"array_key_here";s:[0-9]+:"your_value_here".*'

I was looking for the same and found that this query gives me the desired result. Although, I used Ruby on Rails to serialize the column product_ids

SELECT * FROM coupons WHERE product_ids LIKE '%---\\n- \\'71591\\'\\n%'

Select * from table where table_field like '%"enter_your_value"%'

You may be looking for an SQL IN statement.

http://www.w3schools.com/sql/sql_in.asp

You'll have to break your array out a bit first, though. You can't just hand an array off to MySQL and expect it will know what to do with it. For that, you may try serializing it out with PHP's explode.

http://php.net/manual/en/function.explode.php

select * from postmeta where meta_key = 'your_key'  and meta_value REGEXP  ('6')

foreach( $result as $value ) {
   $hour = unserialize( $value->meta_value );
   if( $hour['date'] < $data['from'] ) {
     $sum = $sum + $hour['hours'];
   }
 }