Search and Replace with WordPress

by El Plumber (admin) on November 15, 2009

logo mysql 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!

Related Posts:

{ 2 comments… read them below or add one }

Bob January 2, 2010 at 8:13 pm

Hey El Plumber,

What would you recommend. . . . . .

If you have a lot of products that you have bought and tried and know that they work and you would like to share your reviews on them (and putting them all into one website would be bad because they are in very different niches) would you recommend to just make a few word press blogs with great reviews or really take the time to set up a website for each one?

Also…. If you are promoting a product(that you have bought of course) would you make your own website for each product you are promoting, or just make a few dozen blogs – one for each of the products that you are promoting.

Smity January 2, 2010 at 8:15 pm

This blog/website that you have here is great!!! I just wish that you would answer comments more often when possible, I know that many of your viewers would be more willing to subscribe to you or comment more often.

Leave a Comment

Previous post:

Next post: