Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
mysql search and replace whole database... serialized data also!
<?php
// This script is to solve the problem of doing database search and replace
// when developers have only gone and used the non-relational concept of
// serializing PHP arrays into single database columns. It will search for all
// matching data on the database and change it, even if it's within a serialized
// PHP array.
// The big problem with serialised arrays is that if you do a normal DB
// style search and replace the lengths get mucked up. This search deals with
// the problem by unserializing and reserializing the entire contents of the
// database you're working on. It then carries out a search and replace on the
// data it finds, and dumps it back to the database. So far it appears to work
// very well. It was coded for our WordPress work where we often have to move
// large databases across servers, but I designed it to work with any database.
// Biggest worry for you is that you may not want to do a search and replace on
// every damn table - well, if you want, simply add some exclusions in the table
// loop and you'll be fine. If you don't know how, you possibly shouldn't be
// using this script anyway.
// To use, simply configure the settings below and off you go. I wouldn't
// expect the script to take more than a few seconds on most machines.
// BIG WARNING! Take a backup first, and carefully test the results of this code.
// If you don't, and you vape your data then you only have yourself to blame.
// Seriously. And if you're English is bad and you don't fully understand the
// instructions then STOP. Right there. Yes. Before you do any damage.
// USE OF THIS SCRIPT IS ENTIRELY AT YOUR OWN RISK. I/We accept no liability from its use.
// Written 20090525 by David Coveney of Interconnect IT Ltd (UK)
// http://www.davesgonemental.com or http://www.interconnectit.com or
// http://spectacu.la and released under the WTFPL
// ie, do what ever you want with the code, and I take no responsibility for it OK?
// If you don't wish to take responsibility, hire me through Interconnect IT Ltd
// on +44 (0)151 709 7977 and we will do the work for you, but at a cost, minimum 1hr
// To view the WTFPL go to http://sam.zoy.org/wtfpl/ (WARNING: it's a little rude, if you're sensitive)
// Credits: moz667 at gmail dot com for his recursive_array_replace posted at
// uk.php.net which saved me a little time - a perfect sample for me
// and seems to work in all cases.
// Start TIMER
// -----------
$stimer = explode( ' ', microtime() );
$stimer = $stimer[1] + $stimer[0];
// -----------
// Database Settings
$host = 'localhost'; // normally localhost, but not necessarily.
$usr = 'root'; // your db userid
$pwd = 'gorko321'; // your db password
$db = 'buljetest'; // your database
// Replace options
$search_for = 'buljetest-mk1.rhcloud.com'; // the value you want to search for
$replace_with = 'www.bulje.eu'; // the value to replace it with
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid) { echo("Connecting to DB Error: " . mysql_error() . "<br/>"); }
// First, get a list of tables
$SQL = "SHOW TABLES";
$tables_list = mysql_db_query($db, $SQL, $cid);
if (!$tables_list) {
echo("ERROR: " . mysql_error() . "<br/>$SQL<br/>"); }
// Loop through the tables
while ($table_rows = mysql_fetch_array($tables_list)) {
$count_tables_checked++;
$table = $table_rows['Tables_in_'.$db];
echo '<br/>Checking table: '.$table.'<br/>***************<br/>'; // we have tables!
$SQL = "DESCRIBE ".$table ; // fetch the table description so we know what to do with it
$fields_list = mysql_db_query($db, $SQL, $cid);
// Make a simple array of field column names
$index_fields = ""; // reset fields for each table.
$column_name = "";
$table_index = "";
$i = 0;
while ($field_rows = mysql_fetch_array($fields_list)) {
$column_name[$i++] = $field_rows['Field'];
if ($field_rows['Key'] == 'PRI') $table_index[$i] = true ;
}
// print_r ($column_name);
// print_r ($table_index);
// now let's get the data and do search and replaces on it...
$SQL = "SELECT * FROM ".$table; // fetch the table contents
$data = mysql_db_query($db, $SQL, $cid);
if (!$data) {
echo("ERROR: " . mysql_error() . "<br/>$SQL<br/>"); }
while ($row = mysql_fetch_array($data)) {
// Initialise the UPDATE string we're going to build, and we don't do an update for each damn column...
$need_to_update = false;
$UPDATE_SQL = 'UPDATE '.$table. ' SET ';
$WHERE_SQL = ' WHERE ';
$j = 0;
foreach ($column_name as $current_column) {
$j++;
$count_items_checked++;
// echo "<br/>Current Column = $current_column";
$data_to_fix = $row[$current_column];
$edited_data = $data_to_fix; // set the same now - if they're different later we know we need to update
// if ($current_column == $index_field) $index_value = $row[$current_column]; // if it's the index column, store it for use in the update
$unserialized = unserialize($data_to_fix); // unserialise - if false returned we don't try to process it as serialised
if ($unserialized) {
// echo "<br/>unserialize OK - now searching and replacing the following array:<br/>";
// echo "<br/>$data_to_fix";
//
// print_r($unserialized);
recursive_array_replace($search_for, $replace_with, $unserialized);
$edited_data = serialize($unserialized);
// echo "**Output of search and replace: <br/>";
// echo "$edited_data <br/>";
// print_r($unserialized);
// echo "---------------------------------<br/>";
}
else {
if (is_string($data_to_fix)) $edited_data = str_replace($search_for,$replace_with,$data_to_fix) ;
}
if ($data_to_fix != $edited_data) { // If they're not the same, we need to add them to the update string
$count_items_changed++;
if ($need_to_update != false) $UPDATE_SQL = $UPDATE_SQL.','; // if this isn't our first time here, add a comma
$UPDATE_SQL = $UPDATE_SQL.' '.$current_column.' = "'.mysql_real_escape_string($edited_data).'"' ;
$need_to_update = true; // only set if we need to update - avoids wasted UPDATE statements
}
if ($table_index[$j]){
$WHERE_SQL = $WHERE_SQL.$current_column.' = "'.$row[$current_column].'" AND ';
}
}
if ($need_to_update) {
$count_updates_run;
$WHERE_SQL = substr($WHERE_SQL,0,-4); // strip off the excess AND - the easiest way to code this without extra flags, etc.
$UPDATE_SQL = $UPDATE_SQL.$WHERE_SQL;
echo $UPDATE_SQL.'<br/><br/>';
$result = mysql_db_query($db,$UPDATE_SQL,$cid);
if (!$result) {
echo("ERROR: " . mysql_error() . "<br/>$UPDATE_SQL<br/>"); }
}
}
}
// Report
$report = $count_tables_checked." tables checked; ".$count_items_checked." items checked; ".$count_items_changed." items changed;";
echo '<p style="margin:auto; text-align:center">';
echo $report;
mysql_close($cid);
// End TIMER
// ---------
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
printf( "<br/>Script timer: <b>%f</b> seconds.", ($etimer-$stimer) );
echo '</p>';
// ---------
function recursive_array_replace($find, $replace, &$data) {
if (is_array($data)) {
foreach ($data as $key => $value) {
if (is_array($value)) {
recursive_array_replace($find, $replace, $data[$key]);
} else {
// have to check if it's string to ensure no switching to string for booleans/numbers/nulls - don't need any nasty conversions
if (is_string($value)) $data[$key] = str_replace($find, $replace, $value);
}
}
} else {
if (is_string($data)) $data = str_replace($find, $replace, $data);
}
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment