Menu
I have a column value stored in the database as:
a:2:{i:0;s:2:"US";i:1;s:2:"19";}
I want to unserialize it during the mysql query rather than using the php unserialize function after fetching the data. I want to do it this way so I can join another table with the serialized value. This would avoid executing a separate query after unserializing it with php, just for the joined data.
478
The best approach is to have normalized table structure (different fields or tables). The next approach is to save data as a delimited string (e.g.: 0,US,1,19 ). Then you can use MySQL's SUBSTRING() or to use standard serialization mechanisms like JSON encode. thats right but the value is not inserted by external app.
The unserialize() function converts serialized data back into actual data.
Unserialize An Array And we do this through the unserialize function. The PHP code serializes an array and then unserializes the serialized data back into an array. $colors=array (“red”,” pink”,” yellow”,” brown”); $colors_serialized=serialize($colors);
serialize is just a built-in, variable handling, PHP function. The counterpart of this is unserialize. You have to save the unserialized data in a variable -> $data=unserialize($serializedData) . After that you can access the data by using the index like $data["name"] for instance.
MySQL doesn't know what a PHP serialization is. You can't do it.
160
You can use SUBSTRING_INDEX
For example, if you have a record like this:
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";} You can use the below SELECT statement:
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 table; Check this for reference: How to unserialize data using mysql without using php
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
