In my post about the Amazon WordPress plugin, I mentioned that I’d been editing loads of posts. At first, this seemed quite simple. Click on the inserted image, then click the big friendly X to delete it, and insert the new link using the plugin.
But this didn’t quite work – after doing that, some posts still showed up as not being entirely served up as HTTPS. A quick look in the WordPress text editor showed the problem. In a lot of posts, there was a bit of lurking HTML:
<img style="border: none !important; margin: 0px !important;" src="http://www.assoc-amazon.co.uk/e/ir?t=lesbessant&amp; l=as2&amp;o=2&amp;a=B005CW11AO" alt="" width="1" height="1" border="0">
Yes, it’s an invisible 1 pixel tracking image. I’m not sure if it was left behind by the images, or the old iframes I used to use, or indeed both, but they had to go, which made the editing job that little bit more entertaining.
To track down all the posts containing these things, I did a search of the MySQL database where all this stuff lives using PHPMyAdmin, which makes it nice and easy to download a CSV file listing what you need to fiddle with.
Now when you edit a post, WordPress helpfully creates a revision – this is effectively a copy of the post that you can revert to in the event of a major ooopsie. But I’d been editing literally hundreds of posts (sometimes more than once, as on occasion I’d missed a link the first time), the database starts getting stuffed with loads and loads of extra records that don’t really serve any purpose. The easiest way to get rid of these is with a quick bit of SQL:
DELETE FROM wp_posts WHERE post_type = "revision"
Now you do need to be careful with SQL – if you get the syntax wrong and accidentally tell it to delete a whole table, that’s exactly what it will do, and err, I do hope you’ve got a good backup, because there isn’t an easy undo option. But hey, if you read what you’ve typed, and always do a “select” before you do an undo (by which I mean do a query that just lists the affected records rather than doing something with them, you can check the output, then when you’re quite sure, you can edit the query and really delete the unwanted data.
Having done all that, I wanted to check for any remaining records containing raw links to Amazon, in case they were pulling in any non-secure items. So I carefully edited my last query to search for the text in question, and you can see where this is going, can’t you? Instead of running
SELECT * FROM wp_posts WHERE wp_content LIKE "%www.amazon.co.uk%"
I managed to run
DELETE FROM wp_posts WHERE wp_content LIKE "%www.amazon.co.uk%"
which resulted in a friendly response to the effect that six records had been deleted. Oh bother.
Now, given the enormous amount of rubbish on this site, you might think that the loss of six random posts wouldn’t be all that important, but I’m far too obsessive for that. I wanted my missing posts back. After a bit of muttering and experimenting, I came up with a solution. I created a new temporary database and restored the last backup of the live database to that. I did mention that having good backups is strongly advised, didn’t I?
Now, armed with a copy of my database, I did my search again (being very careful to make sure I was SELECTING and not DELETING this time. This returned rather more than the six records I was hoping for, as it also included all the posts I’d edited earlier in the day that included those annoying hidden images. But it was relatively easy to check each record, see if there was a post matching it on the actual site, then create new posts from the information in the database – pretty much a copy and paste, with minor edits for some characters that get stored with odd symbols.
And to make sure I’d caught them all, I did the same search (being very careful to SEARCH not DELETE) on the live database, which found the expected six records.
Now if it had been a lot more records, this wouldn’t have been a good plan. I’d probably have restored from backup and redone all the work I’d done that day, which would have been annoying.
So, once again, let me remind everyone (especially me):
 Funny – when I started blogging, this was the only kind of editor I’d use, but now I rarely visit it.