Rather than pulling the data and unserializing each record in php, I wanted to do this in mysql. It seems to be a common problem for developers.
After hunting through all the mysql functions I found SUBSTRING_INDEX.
For example if you have a table like this (data has been modified to protect the innocent!)
| old_data |
| a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:5;s:14:"broughtforward";i:3;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} |
| a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:8;s:14:"broughtforward";i:5;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} |
| a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:10;s:14:"broughtforward";i:8;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} |
| a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:11;s:14:"broughtforward";i:10;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} |
| a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:13;s:14:"broughtforward";i:11;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} |
| a:5:{s:9:"invoiceid";s:1:"8";s:8:"balance";i:14;s:14:"broughtforward";i:13;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"3";} |
| a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:101;s:14:"broughtforward";i:100;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";} |
| a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:102;s:14:"broughtforward";i:101;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";} |
| a:5:{s:9:"invoiceid";s:3:"387";s:8:"balance";i:103;s:14:"broughtforward";i:102;s:6:"userid";s:5:"13908";s:10:"customerid";s:1:"4";} |
| a:5:{s:9:"invoiceid";s:3:"492";s:8:"balance";i:3;s:14:"broughtforward";i:2;s:6:"userid";s:5:"13908";s:10:"customerid";s:3:"179";} |
Use this query, the a:5 above tells me that there are 5 columns. It does depend on the developer knowing how many columns are in the serialized data and the structure being the same. But you get the idea.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM audit
WHERE object_type=42
Which gives me this
| fieldname1 | fieldvalue1 | fieldname2 | fieldvalue2 | fieldname3 | fieldvalue3 | fieldname4 | fieldvalue4 | fieldname5 | fieldvalue5 |
| "invoiceid" | "8" | "balance" | 5 | "broughtforward" | 3 | "userid" | "13908" | "customerid" | "3" |
| "invoiceid" | "8" | "balance" | 8 | "broughtforward" | 5 | "userid" | "13908" | "customerid" | "3" |
| "invoiceid" | "8" | "balance" | 10 | "broughtforward" | 8 | "userid" | "13908" | "customerid" | "3" |
| "invoiceid" | "8" | "balance" | 11 | "broughtforward" | 10 | "userid" | "13908" | "customerid" | "3" |
| "invoiceid" | "8" | "balance" | 13 | "broughtforward" | 11 | "userid" | "13908" | "customerid" | "3" |
| "invoiceid" | "8" | "balance" | 14 | "broughtforward" | 13 | "userid" | "13908" | "customerid" | "3" |
| "invoiceid" | "387" | "balance" | 101 | "broughtforward" | 100 | "userid" | "13908" | "customerid" | "4" |
| "invoiceid" | "387" | "balance" | 102 | "broughtforward" | 101 | "userid" | "13908" | "customerid" | "4" |
| "invoiceid" | "387" | "balance" | 103 | "broughtforward" | 102 | "userid" | "13908" | "customerid" | "4" |
| "invoiceid" | "492" | "balance" | 3 | "broughtforward" | 2 | "userid" | "13908" | "customerid" | "179" |
Then all I need to do is use some column aliases
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS invoiceid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS balance,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS broughtforward,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS userid,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS customerid
FROM audit
WHERE object_type=42
Simples!
