NYCPHP Meetup

[Andrew M. Yochum]

Any reason not use the mysql replace function?  You should get a considerable
speed increase when dealing with a lot of rows, and should give you the same
behaviour.  The idea is to replace the select statement you have and the two
sql statements in the two inner-loops with a single update statement, leaving
the grunt work to the DB server.  I have done it below without a where clause
on the update, but that might also be something to add.

This is basically what I had in mind (haven't tested it, just reworked your
code):

$dbLink = mysql_pconnect("$_DB_host","$_DB_user","");
$tables = mysql_list_tables("$_DB_name",$dbLink);  // Get a list of tables
while ($row = mysql_fetch_row($tables)) {
    $table_name = $row[0];  // Get the current table name
    $fields = mysql_list_fields("$_DB_name","$table_name",$dbLink);  // Get a list of all columns in table
    $set_clause = array();
    for($i = 0; $i < mysql_num_fields($fields); $i++) {
        $column=mysql_field_name($fields,$i); // Get current column name
        $set_clause[] = "$column = replace($column," . mysql_escape_string($old_str) .
                        ", " . mysql_escape_string($new_str). ")";
    }
    $sql = "update $table_name set " . join(', ', $set_clause);
    $result=mysql_query($sql);
    if (! $result) print "Query failed: $sql\
";
}

Andrew

On Sat, 15 Feb 2003, Lynn, Michael  wrote:

> Here's the finished code - I've edited to remove some of my site specific stuff... But basically you need to assign:
> 
> $_DB_host
> $_DB_user
> $_DB_name
> $old_str
> $new_str
> 
> And away you go... Thanks for the great suggestions.  As always, suggestions for improved efficiency greatly appreciated.
> 
> An obvious change is checking for field_type and only searching fields that are char, varchar, string, (and possible blob).
> 
> ---8<---
> $dbLink = mysql_pconnect("$_DB_host","$_DB_user","");
> $tables = mysql_list_tables("$_DB_name",$dbLink);  // Get a list of tables
> while ($row = mysql_fetch_row($tables)) {
>         $table_name = $row[0];  // Get the current table name
>         $fields = mysql_list_fields("$_DB_name","$table_name",$dbLink);  // Get a list of all columns in table
>         for($i = 0; $i < mysql_num_fields($fields); $i++) {  
>                 $column=mysql_field_name($fields,$i); // Get current column name
>                 $sql="select * from $table_name where $column like '%$old_str%'";
>                 $result=mysql_query($sql);
>                 while($a=mysql_fetch_array($result)) {
>                         $old_text = $a["$column"]; // Get the current text value from the column
>                         $new_text=str_replace($old_str,$new_str,$old_text); // Assign new_text using replacement
>                         $sql = "update $table_name set $column='$new_text' where $column='$old_text'";  // Update the database with new
>                         $r=mysql_query($sql);  // do it.
>                 }
>         }
> }
> 
> ---8<---
> -----Original Message-----
> From: Andrew M. Yochum [mailto:andrew at digitalpulp.com] 
> Sent: Friday, February 14, 2003 11:09 AM
> To: NYPHP Talk
> Subject: Re: [nycphp-talk] Replacing a string within many tables, unknown columns
> 
> 
> Here's one way of accomplishing this...
> 
> 1. Use mysql_list_tables to grab a list of tables.
> 
> 2. Use mysql_list_fields + mysql_num_fields to grab a list of fields per table.
> 
> 3. Use mysql_field_type to find the fields of the type you want to manipulate (char, varchar, text, etc.)
> 
> 3. Compile an update statement using the mysql "replace" function to perform the search an replace. Something like:
>     update thetable set data = replace(data,'old_string','new_string');
> 
> Another way might be to do a mysqldump of the DB, replace strings in that file, and load the edited file into the production DB.
> 
> Hope that helps.
> 
> Andrew
> 
> On Fri, 14 Feb 2003, Lynn, Michael  wrote:
> 
> > Wondering if anyones come across a similar problem... Probably more of 
> > a mysql question - but I'm trying to solve the problem using php.
> > 
> > I need to be able to replace every occurance of a string in many 
> > tables, many columns.  The string appears in tables in various places 
> > and usually embedded in a string type or var type column... The string 
> > will not be the entire contents of the column so a replace won't work.
> > 
> > Eg: my development database contains url links to the development web 
> > site.  When I copy the tables to production, I want to search and 
> > replace every occurance of the development urls with production urls 
> > throughout the entire database.
> > 
> > Thanks in advance.
> > 
> > Regards,
> > Mike
> > 
> > 
> > 
> > 
> > 
> > 
> 
>