How to replace a word in whole database using php

In this post you will learn how to change or replace a word in the database using php.

To replace a word in whole db using php, follow these steps (php code file attached too):

How to replace something in mysql database using php

1. Copy this code and save it as a .php file

<?php

    // edit this line to add old and new terms which you want to be replaced
    $search_replace = array( 'old-1' => 'new-1', 'old-2' => 'new-2', 'old-3' => 'new-3' );

    //change the localhost,username,password and database-name according to your db
    mysql_connect("localhost", "username", "password") or die(mysql_error());
    mysql_select_db("database-name") or die(mysql_error());

    $show_tables = mysql_query( 'SHOW TABLES' );
    while( $st_rows = mysql_fetch_row( $show_tables ) ) {
        foreach( $st_rows as $cur_table ) {
            $show_columns = mysql_query( 'SHOW COLUMNS FROM ' . $cur_table );
            while( $cc_row = mysql_fetch_assoc( $show_columns ) ) {
                $column = $cc_row['Field'];
                $type = $cc_row['Type'];
                if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {
                    foreach( $search_replace as $old_string => $new_string ) {
                        $replace_query = 'UPDATE ' . $cur_table .
                            ' SET ' .  $column . ' = REPLACE(' . $column .
                            ', \'' . $old_string . '\', \'' . $new_string . '\')';
                        mysql_query( $replace_query );
                    }
                }
            }
        }
    }
    echo 'replaced';
    mysql_free_result( $show_columns );
    mysql_free_result( $show_tables );
    mysql_close( $mysql_link );

?>

(download the code file here – change the name from search-replace.txt to search-replace.php)

2. edit the file by adding your own terms and database host, login and password

3. upload the file on your server and run it.

hire best wordpress php css developer
Need Help? Hire me now!
I can help you in anything related to webdevelopment, including: HTML, CSS, PHP & MySql, WordPress (plugins & themes development, others), regular expressions, server management, etc.

Leave a Reply

Your email address will not be published.