Best practice for search and replace - Module or direct SQL for Drupal.I guess I probably should have posted this before doing the work :) Basically I wanted to change all strings (in title, body content, CCk's etc from "product1" to say "product2".
At first, after a backup, I tried to do it via phpMyAdmin SQL tab with:
SELECT REPLACE(’product1’, ‘product1’, ‘product2’);
but this did not work at all, even though it said the query executed correctly, so I assume I was running the right syntax?
I then came accross a great little Search and replace module at drupal.org/project/search_and_replace by btopro which worked a treat for me. However, I was left wondering if the best way for a task like this is via a module as its bound to happen regularly for people.
How does everyone else carry out a search and replace on strings within Drupal?
Another option I thought of was downloading the database, carrying out the search and replace there with a third party tool, and then reuploading but the module was much much quicker!
Comments
Just keep in mind that
Just keep in mind that Drupal isn't always used with a MySQL database, though it seems to be the most popular. It could be used with PostgreSQL, MSQL, etc. So whatever solution you decide on for your module, please ensure it is ANSI SQL compliant.
It depends on the use case
It depends on the use case really.
Will you need to repeat the process regularly?
Will others [non admins] ever need to do the task?
If no to both, then I'd prefer direct SQL replacement.
But thats fine if it just refers to text within fields.
What about node titles, aliases, node-references etc.
You could be opening up a can of worms!
Alan
hi i thinks this help for
hi
i thinks this help for your work :)
Search and Replace Scanner
http://drupal.org/project/scanner
this is what I do
Hi,
I use the SQL replace function when we move a project from staging to production environment. The staging database contains anchor tags containing full paths like 'http://staging.myproject.com/...' (caused by tinymce editor). In the production environment I want to change all these paths to 'http://www.myproject.com/...'.
using PhpMyAdmin I search the complete database on 'http://staging.myproject.com'. PhpMyAdmin is the only tool I know which allows me to do this search with one click. I make a list of all tables detected.
For each one of the tables from step 1, I run the following query:
UPDATE table SET column = replace(column, 'http://staging.myproject.com', 'http://www.myproject.com' ) ;
I do this for each relevant column of the table.
In a standard setup we are talking about 5 to 10 queries in total, cache tables and search-indexes not included.
Typical columns are node_revisions.body, node_revisions.teaser, etc.
Replace all on database level is a scary process, just make sure you take the necessary backups.
Hope this helps someone who comes across the same problem. Suggestions on how to handle this in a more intelligent way are welcome ;-)
Pascal
Helps me
Thanks for that, it worked for what I needed.
Though I too would be open to a better way of doing things too.