Herewith a short cautionary tale concerning the dangers of editing your MySQL database in phpMyAdmin when you don't have much idea what you're doing. Note to self: do your research before—not after—trying something new. For my future benefit (should I ignore the aforementioned note) here's a record detailing how I dug myself out of a hole.
Step 1
Backup. At least I got that part right. In WordPress the easiest way to do this is from the Dashboard via Manage > Backup.
Updating an author e-mail
The author e-mail in WordPress is the one that gets notified when someone comments on the post. It can also be used to indicate when a particular comment belongs to the author of the post (e.g. on this blog, author comments are currently styled with a grey hash). If you change your e-mail address in WordPress this association will be lost, so any comments made when you logged in with your old e-mail address will no longer be attributed to the blog administrator.
In order to turn off a heavily-spammed admin e-mail address, I therefore needed to re-associate author comments with my new address. The author e-mail data resides in the comment_author_email field in WordPress, inside the wp_comments table. To effect a search-and-replace, open phpMyAdmin and select the database to edit, then click on the SQL tab to reveal a text entry box:
In this box type the following, then click Go (substituting the correct e-mail addresses):
UPDATE wp_comments SET comment_author_email = REPLACE (
comment_author_email,
'old_email@domain.com',
'new_email@domain.com');It worked for me, but you can try it at your own risk.
Removing 404 URLs from comments
My early posts were imported from iBlog; this meant that the author URL was no longer valid. I could have ignored it, but fate decided I would try to fix it. Through a series of misguided actions I accidentally chopped off the http:// prefix from every URL. So instead of "http://www.bioneural.net" I got "www.bioneural.net"—for every URL—not just the invalid ones. I needed to add the http:// back to comment_author_url without overwriting the data already present. On my first attempt I made the situation worse, resulting in "http://0" replacing all data in comment_author_url. I wasn't able to make an SSH connection to my server for some reason, so my attempt to load my backup database failed failed. Here's what I did:
- Opened the backup output.sql file in BBEdit;
- Stripped out everything except the INSERT INTO lines within wp_comments;
- Did a search-and-replace on the data to change;
- In phpMyAdmin, emptied the wp_comments table;
- Selected all of the INSERT INTO lines opened in BBEdit;
- In phpMyAdmin opened the SQL box and pasted from BBEdit;
- Hit Go.
All my comments seem to be there, my author e-mail is changed, and those 404-causing URLs pointing to my old blog are replaced. A lucky escape.
Scott responded to my call for help and suggested the following syntax, which I'll file here for safe keeping:
UPDATE wp_comments SET comment_author_url = concat (
"http://",
comment_author_url);Thanks for this Scott!
Bruce, you might also look at this great plugin for mass search & replace.
It essentially does the same things, but with a more familiar WP interface and IMO less learning curve.
Fair warning though, in its current version, I'm unable to get two of its search features to work on my blog. It's still worth keeping activated though.
Funny about your missing "http://" prefixes. I too had this problem when I imported from iBlog. I wonder if that was a side effect of using Agitprop.
Thanks for that Rob (nice little demo movie); that looks like a really useful plugin. Doesn't look half as scary as phpMyAdmin!
No the prefix issue wasn't caused by Agitprop; I did a search on "http://" was was occupying a number comment_author_url fields, replacing it with "". But that removed it from every field, including those with complete URLs.