Site icon Nabtron

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.

Exit mobile version