How to replace text or URL in entire Mysql Database Easily?

Want to change or replace any text or string in entire Mysql database but don’t have query to do it or don’t want to do it manually then this will help you.

If you have changed your site URL or any string in your script, you will need to change it in your database too or your site will break. Its hard to change all strings manually, although you can replace text using mysql queries but you will need to go table by table this way which is also time consuming. The best and easiest way I have found for my site is using this php code. Once you give your database credentials to this script, it will automatically replace string everywhere in database. This make process mcuh easier and simpler.

How to use it?

1. Copy the code below.
2. Save it in PHP extension on your site.
3. Add credentials and strings you want to replace.
4. Run the file in browser and see the magic.
5. Its done. You can check it.

 

<?php

// Connect to your MySQL database.
$hostname = "localhost";
$username = "db_username";
$password = "db_password";
$database = "db_name";

mysql_connect($hostname, $username, $password);
 // The find and replace strings.
$find = "find_this_text";
$replace = "replace_with_this_text";

$loop = mysql_query("
    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cant loop through dbfields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
        mysql_query($query['s']);
}

?>

 

This script is quite helpful if you are migrating from HTTP to https or changing your site URL. There are many other ways too, but I find this way the easiest one, if you have any other method then don’t forget to share it with me.