One of the single best plugins I installed was subscribe-to-comments, which allows visitors to request an e-mail whenever a further comment is added. It’s made some of my posts more active places, and some fascinating and intriguing discussions have emerged.
Unfortunately, after merging in my old ‘techie musings’ weblog here, I realised that the data was missing. This is because the wordpress import/export feature just moves the raw post and comment data, not the extra columns that are set by the various plugins folk might be using.
So, here’s how to fix that…
First thing to stress is that my separate weblogs were running off of the same database server (in fact, the same database), ie. different $table_prefix setting in wp-config.php. If you’re merging two weblogs from different mysql servers you will need to load the data from your old blog, into your database after completing the Wordpress import. This should be straightforward enough. So long as the names of the tables are different, it doesn’t really matter. If you’ve a mysqldump output, simply edit the file to your hearts content to change the table names.
My blogs were prefixed with ‘diary‘ and ‘techie‘. Thus ‘diaryposts’ contained diary posts, and ‘techposts’ techie posts(!). I wanted the subscription settings for commentators moved to diary_comments. So we’re talking about four tables in total, two ‘posts’ tables, and two ‘comments’ tables.
First up, what will change. This first query shows the differing subscriptions side by side… Remember, you will need to change the names of the tables in the ‘from’ statement to match whatever your tables are called. Note the short-cut prefixes in use too. They feature in the select and where clauses too.
select dp.ID,tp.ID,dp.postname, dc.commentID,tc.commentID,dc.commentauthoremail,tc.commentauthoremail,dc.commentsubscribe,tc.commentsubscribe from diarycomments dc, techcomments tc, diaryposts dp, techposts tp where dp.postname=tp.postname and dp.postname!=”" and dc.commentpostID=dp.ID and tc.commentpostID=tp.ID and tc.commentauthoremail=dc.commentauthoremail and tc.commentsubscribe!=dc.commentsubscribe
Now the actual change. I should stress I have changed the underlying tables to make use of innodb storage engine, so I first issued:
begin;
… to start a transaction. That way I could check that the change had worked, and roll it back if it was wrong… If you’re using MyISAM, either consider changing the comments table over (’alter table blah_comments engine=innodb‘), or skip the ‘begin’ and ‘commit’s.
So:
update diaryposts dp, diarycomments dc, techcomments tc, techposts tp set dc.commentsubscribe=tc.commentsubscribe where dp.postname=tp.postname and dp.postname!=”" and dc.commentpostID=dp.ID and tc.commentpostID=tp.ID and tc.commentauthoremail=dc.commentauthoremail and tc.commentsubscribe!=dc.comment_subscribe
With ‘commit‘ afterwards to commit innodb transactions, although you probably want to run the first SQL block to check there are no differing subscriptions. Check any warnings (’show warnings‘), and check that the numbers changed match your expectations.
et voila! Subscriptions copied over.
Note this doesn’t bother with the “Subscribe without commenting” subscriptions. I’ve experienced nothing but problems with that (ie. it’s mainly populated by spammers for no doubt malicious ends), and have frequently cleared out the records. It shouldn’t be too tricky to come up with something, but that’s left as an exercise for the reader. Feel free to drop a comment with the SQL here if you do.
Entries (RSS)