Search and Replace with WordPress

I absolutely love WordPress. It is by far the best basic site content management tool on the planet today and I challenge anyone to dispute that. Note that I said BASIC site tool. If you want to do anything far outside the “blog” type of site, it will likely not meet your needs. But for 95% of the people out there, it is perfect. If you can use MS Word or Excel, than you use WordPress. Setting it up, changing the design, and making it look really good is another story, but for the basics of putting stuff on the Internet, it is great.

That being said, it is severely lacking in some basic document management tools that you might be used to with something as feature rich as Word.  Part of it has to do with the WordPress editor being a web browser based application, the other part with the fact that it uses a MySQL database to store all the content.  But that is also part of its greatest strength.

I have started a number of new websites recently, all based around one single site that I started about 18 months ago that is really starting to pay off.  That one site started to take off near the end of the summer and I cloned the idea across a couple of related niches.  It only takes a few hours a week to maintain and update each one.  The first site made over $1000 last month and is on track to make $2000 this month, all using completely legal and ethical white hat methods.  I’m currently writing up a tutorial  to teach people to do the exact same thing with the only investment being a basic hosting account.  I plan on giving it away for no cost as long as you link back to the tutorial from any sites you create.  Stay tuned.

BUT, I screwed up a couple of them and had been using an incorrect affiliate tag all over the place and thus was not getting credit for anyone who clicked through the links on the pages.  I can certainly fix it on articles going forward on those sites, but needed to change all the old ones, especially since they were starting to get traffic and clicks.  However, the WordPress browser based UI is really clunky for changing things like that.  I would have to go to each individual post one at a time, wait for them to load, edit them, save them, etc.

Another option would be to export the entire site using the WordPress backup tools, then do a search and replace in the files manually in a text editor, then clear the site and reload them back in.  Another unappealing option.

SQL to the rescue!  Here is how to do it:

1. Go to you web hosting panel and open whatever tool it uses to connect to the MySQL database for the WordPress site you need to Find and Replace on.  Sorry I cannot be more help on this step, but the procedure and tools vary widely from hosting provider to hosting provider.  Just get there.
2. On most MySQL web interfaces, there will be a tab called “SQL” for the database, click on it.
3. Enter the following as your query.  Replace the ‘oldtext’ and ‘newtext’ below with what you want to search and replace.  This will work for any text, including the text inside URLs, which was what I screwed up.

UPDATE wp_posts set post_content = REPLACE (post_content, ‘oldtext’, ‘newtext’);

Bam.  Done.  Enjoy!