Updating a serialized array in mysql (without unserialising?)

Question

All I read says storing serialized arrays in Mysql is a bad idea - I already know that;) Unfortunately, I am working with an open source script that uses this method, and restructuring is not an option in this scenario .

Can I update this url without first unserializing?

I initially tried to use replace, however it throws an error:

$rssquery = "UPDATE config SET `array` = replace(`array`, \"http://www.oldurl.com\", \"http://www.newurl.com\") WHERE name='config'"; $insert = $db->insert($rssquery); 

Failed to update UPDATE config SET array = replace ('array', ' http://www.oldurl.com ', ' http://www.newurl.com ') because the variable to be transmitted must be an array.

Table Name: config
Columns: name | an array
Required line Updated named: config
Required cell Updated named: array

Any other ideas or approaches would be appreciated :) Thanks!

Answer 1

If you simply perform a search and replace in this way, you will not transmit serialized data. Here is what you need to do:

 $old = 'http://www.google.com'; $new = 'http://www.someplace.com'; $search = 's:' . strlen($old) .':"' . $old . '"'; $replace = 's:' . strlen($new) .':"' . $new . '"'; $query = "UPDATE config SET array=REPLACE(array, '{$search}', '{$replace}');"; 

Replace $old and $new your current and destination URL, run the script, and execute the generated $query .

Here's a clean SQL solution:

 SET @search := 'http://www.original.com'; SET @replace := 'http://www.target.com'; UPDATE config SET array=REPLACE(array, CONCAT('s:', LENGTH(@search), ':"', @search, '"'), CONCAT('s:', LENGTH(@replace), ':"', @replace, '"')); 

Note that this will replace EVERY appearance of the search string in your serialized array. If you want to replace a specific key, you need to be more specific.

Answer 2

I think I would share a tool written in PHP (script) that you can download, which performs a search and replace the database. To quote the author from the site:

Search Replace DB version 3.0.0 (currently BETA version) allows you to perform large-scale database searches / replacements that do not harm serialized PHP strings or objects with a convenient interface and experience.

You can find it here: Search and replace the Script database in PHP

I transferred e-commerce, WordPress and other databases to a different domain, and I found this invaluable. I did not look into PHP Script itself to look under the hood, but if they do not obscure the code, you can find a gold mine of ideas and methods ... lol