MIKESTOWE.COM

you are here

“Undo” Find and Replace PHP Script

While at work today, I ran into a situation where someone had accidentally done a “find and replace” on a spreadsheet, meaning that when I imported the data there were a TON of mispelled words (in this case they replace the letters “ea” with “each”). So rather than go through the thousands of records by hand, I whipped up this quick (and unpolished) script to do the work for me.

Just add your MySQL connection information, and update the config section to include the table, the column(s) to check, and then what to find and replace it with. Then the script will identify the records that match your criteria, and if the word(s) it finds is not an actual word, it will replace “find” with “replace,” attempting to keep the same case. That’s really all there is to it :) Oh, backup your database BEFORE running this script… just in case 8)

<?php

/*  Undo Find & Replace Script
------------------------------------------
Goes through the database to find instances of a word and then sets it 
back to its original version as long as it is not actually a real word.
-------------------------------------------
Author: Michael Stowe
Author URI: http://www.mikestowe.com
-------------------------------------------*/

#############################################
#                                           #
#    INSERT MYSQL CONNECTION DATA HERE      #
#                                           #
#############################################

#################\
## CONFIG DATA ###\
########################################################################
$find ''// example: each                                           #
$replace ''// example: ea                                          #
$table ''// table to use, example: products                        #
$table_key ''// primary id, example: product_id                    #
$column[] = ''// column name, example: product_name                  #
// optional, additional columns (each a new $column[] = ''; )          #
// $column[] = ''; // column name, example: product_text               #
########################################################################

$pspell pspell_new('en');
$t 0;
$q 0;

foreach(
$column as $c) {
    
$sql mysql_query('select * from '.$table.' WHERE '.$c.' LIKE "%'.$find.'%"');
    while(
$i mysql_fetch_array($sql)) {
        
$q++;
        
preg_match_all('/[^\s]*[^0-9]each[^\s]+/i',$i[$c],$pn_array);
        foreach(
$pn_array[0] as $pn) {
            if(!
pspell_check($pspell,$pn)) {
                
$t++;
                if(
preg_match('/[a-z]\b/',str_replace($find,'',strip_tags($pn)))) {
                    
$replacewith $replace;
                } else {
                    
$replacewith strtoupper($replace);
                }
                
                
$replacewith str_replace($find,$replacewith,$pn);
                
$i[$c] = str_replace($pn,$replacewith,$i[$c]);
            }
        }
        
mysql_query('UPDATE '.$table.' SET '.$c.' = "'.mysql_real_escape_string($i[$c]).'" WHERE '.$table_key.' = "'.$i[$table_key].'" LIMIT 1');
    }
}

echo 
'Scanned '.number_format($q).' Columns and Updated '.number_format($t).' Instances';

?>
Share this Page:
Facebook Twitter Linkedin Reddit Tumblr Email

Leave a Reply

Your email address will not be published. Required fields are marked *