Quickly change multiple posts in WordPress

There are a few things that I find so useful and valuable I recommend them whenever I have the chance. Occasionally those services provide opportunities for an affiliate link, like ScreenCastsOnline. Because I may earn a few cents and would recommend them anyway, I take up the offer.

Then, once in a while, they change things around and I have to change the link I use. That happened today.

First I went to my blog’s theme and updated the sidebar. That took only a few seconds, but I also knew I’d mentioned the link in an unknown number of my 2177 posts to date. Changing those links was another matter entirely.

Some people would go to PHPAdmin, in their web host’s Control Panel, but instead I started up a wonderful piece of free software called CocoaMySQL-SBG (for Macs). I’ve used this before so everything was all set up. I connected to my blog, selected the wp_posts table and clicked on the content tab. Then I selected post_content and contains from the pop-ups, entered a search term and clicked the search button. After a few moments 11 posts were identified as potential candidates for link changes.

To change the links I then selected each post in turn and double-clicked on the post_content field. Then I could scan the text, make any necessary edits, and close. When I clicked on the next row the edit was written back to the database on the server.

It took only a few minutes to change all the relevant links. Of course, if I felt more confident about MySQL queries, I’m sure I could have written a query to do it. But this method worked well for me.

Interestingly enough, this process threw an error for one particular post, advising me that I needed to repair the database. I backed up the database, then clicked the Optimize Table button on the CocoaMySQL-SBG toolbar. That seemed to fix things.

For full details of how to set up CocoaMySQL-SBG see Chapter 10 of the WordPress 2 Visual Quickstart Guide that Maria Langer and I wrote last year. In short:

1. Enable access to the database.
2. Open CocoaMySQL-SBG. The New Connection sheet appears.
3. Enter the same Host, User and Password details as you entered in the wp‑config file when you installed your blog.

For Windows users — we explain in Chapter 10 how to work with a similar program, SQLyog for Windows.