NYCPHP Meetup

[Lynn, Michael (DCS)]

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
> 
> 
> 
> 
> 
> 

-- 
Andrew Yochum
Digital Pulp, Inc.
212.679.0676x255
andrew at digitalpulp.com



--- Unsubscribe at http://nyphp.org/list/ ---