find and replace all in database modify script to return affected rows

Question

I found this really cool script here: http://www.brilliantsheep.com/replacing-a-string-in-all-tables-of-a-database-in-mysql/

I have tried to modify the script so that it displays the rows affected using: mysql_affected_rows() == 1. Is this the correct way to test if the UPDATE performed any updates?

<?php

    // Setup the associative array for replacing the old string with new string
    $replace_array = array(
        'test' => 'foo'
    );

    $mysql_link = mysql_connect( 'localhost', 'root', '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 );
    $results = array();
    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 );
                        if(mysql_affected_rows() == 1){
                            $results[] = $replace_query;
                        }
                    }
                }
            }
        }
    }

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

    echo 'Rows affected!';

    echo '<ul>';
    foreach($results as $i){
        echo '<li>' . $i . '</li>';
    }
    echo '</ul>';

?>

Tieran · Accepted Answer · 2012-08-25 11:59:45Z

1

mysql_affected_rows() will return the number of rows affected, so you probably want to be looking for > 0 rather than == 1. I would be really really careful with this script, it's almost inevitably going to do something you don't want it to.

answered Aug 25, 2012 at 11:59

Tieran

1,0767 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Havelock · Accepted Answer · 2012-08-25 12:23:58Z

0

In addition to what Tieran said you could also do the UPDATE in a simpler manner

$replace_query = 'UPDATE ' . $table .
                 ' SET ' .  $column . " = '$new_string'" ;

answered Aug 25, 2012 at 12:23

Havelock

7,0044 gold badges37 silver badges42 bronze badges

Collectives™ on Stack Overflow

find and replace all in database modify script to return affected rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related