MainelyDesign.com Blog

Global Mysql Find and Replace with CakePHP

Posted on 08/31/2010 at 09:03 am by Kevin Wentworth
Viewed 1,734 times | 0 comments

I'm moving my Content Management System over to CakePHP 1.3. I'll post an article about my experiences later. Today I will focus on one and only one issue with the migration from Cake 1.2 to 1.3: the change of webroot/theme/ to webroot/theme/ to serve static content. At first I was a little disapointed/scared at the prospects of making this change, but have since come to see the absolute brilliance in this approach.

Files, easy. Content (Mysql data), hard?

Moving all of my webroot/theme/ files to views/theme/themename/webroot/ was easy (and much appreciated). Now all my CSS, JavaScript and Image files were loading, except the ones that were hardcoded in the mysql database. I'd always wanted to build in a search and replace function into Site Avenger, now was my chance.

MySQL Find and Replace SQL Statement

From the MySQL website, here is the code for search and replace in a mysql table (wait, the manual is so convoluted, try this instead):

Show Plain Text

UPDATE [your_table_name] SET [your_table_field] = REPLACE([your_table_field], '[string_to_find]' , '[string_to_be_replaced]');

Best Way to Search and Replace MySQL Data in CakePHP

The following code ended up being the best way to do a global search and replace. What do I mean by global? Well, I needed to search every table and every column (in each table) and look for '/theme/' and replace it with '/theme/'. Here's how I loop through every table in the database, and then search through each column in the table, replacing each occurance.

Table-based search and replace in CakePHP:

Show Plain Text

$db = ConnectionManager::getDataSource(bootServerName());
$tables = $db->listSources();
foreach($tables as $table) {
    $result = $db->query('DESCRIBE '. $table);
    $fields = Set::extract($result, '/COLUMNS/Field');
    foreach($fields as $field) {
        $query = 'UPDATE '. $table .' SET `'. $field .'` = REPLACE(`'. $field ."`, '/theme/' , '/theme/')";
        if($db->query($query)) {
            $delta = $db->lastAffected();
            if($delta > 0) {
                echo $table .'.'.$field.' replaced '. $delta .' times.'. "\n";
            }
        }
    }

}

Another way to Search and Replace in CakePHP

This was the first method I tried. It works (I think) but once I started running the search/replace the application started throwing missing table errors. This is because not every site has all the tables, yet each site knows about all the models. Plus, this method would need to take into account plugin models as well... not very DRY. This is why I choose to go with the first/best method above.

With that being said, here is the model-based search and replace in CakePHP:

Show Plain Text

$models = App::objects('model');
foreach($models as $model) {
    $this->loadModel($model);
    $fields = array_keys($this->$model->_schema);
    foreach($fields as $field) {
        $query = 'UPDATE '. $this->$model->tablePrefix.$this->$model->useTable .' SET `'. $field .'` = REPLACE(`'. $field ."`, '/theme/' , '/theme/')";
        if($this->$model->query($query)) {
            $delta = $this->$model->getAffectedRows();
            if($delta > 0) {
                echo $model .'.'.$field.' replaced '. $delta .' times.'. "\n";
            }
        }
    }
}

Warning/Disclaimer

I wrote these functions for a specific purpose. I *think* they work, at least I hope so. I plan on updating the table-based function with more features, like only looking in certain tables and the ability to specify the search and the replace parameters. Plus, I will add a view.ctp!. So, in less convoluted language: these functions are rough drafts, i.e. proof of concepts, to be used at your own risk.

Sorry for not posting in a while. Cheers,
-Kevin Wentworth

Tags for Global Mysql Find and Replace with CakePHP

Recent Posts

Subscribe to my feed

MainelyDesign.com Blog

Global Mysql Find and Replace with CakePHP

Files, easy. Content (Mysql data), hard?

MySQL Find and Replace SQL Statement

Best Way to Search and Replace MySQL Data in CakePHP

Another way to Search and Replace in CakePHP

Warning/Disclaimer

Tags for Global Mysql Find and Replace with CakePHP

Comments for this Posting

No comments. Be the first to post a reply.

Leave a Comment