MainelyDesign.com Blog

Global Mysql Find and Replace with CakePHP

Posted on 08/31/2010 at 09:03 am by Kevin Wentworth
Viewed 14,945 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
  1. 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
  1. $db = ConnectionManager::getDataSource(bootServerName());
  2. $tables = $db->listSources();
  3. foreach($tables as $table) {
  4.     $result = $db->query('DESCRIBE '. $table);
  5.     $fields = Set::extract($result, '/COLUMNS/Field');
  6.     foreach($fields as $field) {
  7.         $query = 'UPDATE '. $table .' SET `'. $field .'` = REPLACE(`'. $field ."`, '/theme/' , '/theme/')";
  8.         if($db->query($query)) {
  9.             $delta = $db->lastAffected();
  10.             if($delta > 0) {
  11.                 echo $table .'.'.$field.' replaced '. $delta .' times.'. "\n";
  12.             }
  13.         }
  14.     }

}

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
  1. $models = App::objects('model');
  2. foreach($models as $model) {
  3.     $this->loadModel($model);
  4.     $fields = array_keys($this->$model->_schema);
  5.     foreach($fields as $field) {
  6.         $query = 'UPDATE '. $this->$model->tablePrefix.$this->$model->useTable .' SET `'. $field .'` = REPLACE(`'. $field ."`, '/theme/' , '/theme/')";
  7.         if($this->$model->query($query)) {
  8.             $delta = $this->$model->getAffectedRows();
  9.             if($delta > 0) {
  10.                 echo $model .'.'.$field.' replaced '. $delta .' times.'. "\n";
  11.             }
  12.         }
  13.     }
  14. }

 

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

Bookmark and Share

3 Related Articles

Tags for Global Mysql Find and Replace with CakePHP

Cakephp | Database | Mysql | Example | Site Avenger | Tutorial | Web Programming

Comments for this Posting

No comments. Be the first to post a reply.

Sorry, comments are closed for this posting.

Please Email Kevin if you have any questions. Thanks!

Meet Site Avenger - Hosted Content Management System

Powered By: Site Avenger | Site Production: Saco Design