August 28, 2013 | Posted in: Programming

Every once in a while on the internet, you come across a useful resource that helps you solve a pressing problem. So what do you do? You bookmark it for future reference. But, as is the nature of the internet, things get broken, websites cease to exist, people stop publishing, and some helpful pages disappear.

One such helpful resource I once came across was a solution to perform a string replacement in all tables of a MySQL database, using PHP. The solution came in handy for me when I had to migrate a WordPress website from a local machine to a hosted environment. It definitely saved countless hours, and I thanked the author as netiquette demands, and stored it in my bookmarks at delicious.com.

However, some time early this year and several times after that, I revisited the bookmark, only to find a “HTTP Error 410 Gone” for the entire domain (brilliantsheep.com). This is not the first time I encounter this; I still miss the great content Maki used to post at doshdosh.com for example. Knowing how useful solution may be to someone else, I have taken the liberty to post the solution here, and hope you’ll find it useful. (Credits to brilliantsheep.com).

<?php

// Setup the associative array for replacing the old string with new string
$replace_array = array( ‘string1′ => ‘replacementstring1′, ‘string2′ => ‘replacementstring2′, ‘string3′ => ‘replacementstring3′, ‘string…n’ => ‘replacementstring…n’ );

$mysql_link = mysql_connect( ‘localhost’, ‘username’, ‘password’ );
if( ! $mysql_link) {
die( ‘Could not connect: ‘ . mysql_error() );
}

$mysql_db = mysql_select_db( ‘database’, $mysql_link );
if(! $mysql_db ) {
die( ‘Can\’t select database: ‘ . mysql_error() );
}

// Traverse all tables
$tables_query = ‘SHOW TABLES’;
$tables_result = mysql_query( $tables_query );
while( $tables_rows = mysql_fetch_row( $tables_result ) ) {
foreach( $tables_rows as $table ) {

// Traverse all columns
$columns_query = ‘SHOW COLUMNS FROM ‘ . $table;
$columns_result = mysql_query( $columns_query );
while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {

$column = $columns_row['Field'];
$type = $columns_row['Type'];

// Process only text-based columns
if( strpos( $type, ‘char’ ) !== false || strpos( $type, ‘text’ ) !== false ) {
// Process all replacements for the specific column
foreach( $replace_array as $old_string => $new_string ) {
$replace_query = ‘UPDATE ‘ . $table .
‘ SET ‘ .  $column . ‘ = REPLACE(‘ . $column .
‘, \” . $old_string . ‘\’, \” . $new_string . ‘\’)';
mysql_query( $replace_query );
}
}
}
}
}

mysql_free_result( $columns_result );
mysql_free_result( $tables_result );
mysql_close( $mysql_link );

echo ‘Done!’;

?>

PS: The Mysql extension is deprecated as of PHP 5.5.0 or higher, and may be completely removed in future. You are therefore advised to rewrite the code to use MySQLi extension instead. The code is provided as is, without any guarantee that it will work for your purpose. Use your discretion.

I have been involved in the web development and internet marketing business for the past seven years or so. I therefore like to think of myself as an internet marketing expert, even though the truth is that I am continually learning in this ever changing field.

Be the first to comment.

Leave a Reply

*



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>