Pages

Wednesday, March 21, 2012

MySQL search and replace in whole database

<?php

function generate_queries($database, $user, $password, $host, $search, $replace) {

    $conn = mysql_connect($host, $user, $password);
    if (!$conn) {
        die('Unable to connect ' . mysql_error());
    }
  
    if (!mysql_select_db('INFORMATION_SCHEMA', $conn)) {
        die('Cannot use INFORMATION_SCHEMA');
    }
  
    $database_sql = mysql_real_escape_string($database, $conn);
    $query_tables = "select TABLE_NAME from TABLES where TABLE_SCHEMA = '$database_sql'";
    $tables_res = mysql_query($query_tables);
  
    $queries = '';
    $search_sql = mysql_real_escape_string($search, $conn);
    $replace_sql = mysql_real_escape_string($replace, $conn);
   echo '<pre>';
   
    while($tables_row = mysql_fetch_assoc($tables_res)) {
   
        $table_sql = mysql_real_escape_string($tables_row['TABLE_NAME'], $conn);
      
        $query_columns = "select COLUMN_NAME from COLUMNS where TABLE_SCHEMA = '$database_sql' and TABLE_NAME = '$table_sql' and DATA_TYPE in ('varchar', 'text', 'longtext')";
        $columns_res = mysql_query($query_columns);
        $columns = array();
        while ($column_row = mysql_fetch_assoc($columns_res)) {
            $columns[] = $column_row['COLUMN_NAME'];
        }
       
        if (!empty($columns)) {
            $queries .= "update `{$tables_row['TABLE_NAME']}` set ";
            foreach ($columns as $i => $column) {
                if ($i) {
                    $queries .= ", ";
                }
                $queries .= "`$column` = replace(`$column`, '$search_sql', '$replace_sql')";
            }
            $queries .= ";\n";
        }
    }
  
    return $queries;
}

echo $res = generate_queries('aus_sqt','root','','localhost','softqube','sqt');die();

?>

No comments: