Recently I needed to produce an export of some data from a client’s WordPress site. Unfortunately the data they needed was stored in the wp_postmeta table as a serialized array.
Of course, I could use PHP to query the database and then unserialize the data and grab what I needed, but this was a quick request which I needed to spend minimal time on.
I managed to query the data exactly how I needed it directly in MySQL. Using the power of SUBSTRING_INDEX. Let’s take a look at how.
Here’s a small example, for WordPress attachments. The attachment data array is serialized in PHP then saved to the wp_postmeta table with the meta key of ‘_wp_attachment_metadata’.
When I’m writing complex SQL code, I try and break things down into stages that I can then combine when I know every part is working as it should. There’s 3 stages to this process:
1. Find the array key position
Using SUBSTRING I can string off all the earlier part of the meta_value string before the occurrence of my array key (as well as the array key itself):
SUBSTRING(pm.meta_value, ( INSTR( pm.meta_value, CONCAT( 'file', '";' ) ) + CHAR_LENGTH( 'file') + 1 ) )
I’m searching for ‘file”;’ here to ensure I find the right position in the string of the key, not just any instance of ‘file’ in the serialized string.
The substring is starting after ‘file’ and the proceeding double quote, so we can search for quotes surrounding the actual array value later.
2. Parse the array value before the trailing quote
The SUBSTRING function above is then wrapped in a call to `SUBSTRING_INDEX`:
SUBSTRING_INDEX( [step 1], '"', 2 )
The first argument is the search string which we formulated at step 1. The second argument is the delimiter of a double quote, and the third argument of ‘2’ instructs the function to return all of the string up to the second occurrence of the delimiter. I didn’t want to rely on using the string length in the serialized array in case it wasn’t right.
3. Trim off the string up to and including the first quote
Now we have a string that has our value but a lot of preceding serialized cruft (eg. ‘;s:23:”‘ ). The easiest way to remove that is with another SUBSTRING_INDEX call:
SUBSTRING_INDEX( [step 2], '"', -1 )
This time we want to search for the first occurrence of the delimiter, but using a negative number we can tell the function to return all the string after the delimiter, instead of before it.
That’s it, a quick approach to selecting data from a serialized array using MySQL. Do you use an alternative method? Let me know in the comments.
