Kyle’s Pile

Only a few days ago I was moved by my web server from their old vDeck platform to their newer, um, vDeck platform? Well, the actual platform is besides the point. The problem that arose was actually caused by MySQL’s faulty exporting function which may have been combined with some charset irregularities between my database tables and wordpress. All I’m trying to say, really, is that one day my blog was working great and the next it was filled with lots of “’” characters. Big problem.

Well I looked really closely at the pattern of these characters and realized that they pretty much represented either apostrophes, quotation marks or hyphens. There as an entirely different problem with the one essay I’ve posted in French – I just reposted it. I wasn’t about to sit down and hash out all the different foreign characters I used that text. However, I had enough other posts that I didn’t want to find all my old Word documents and repost each one – what a hassle!

I did some research and found lots of people who wanted to teach me how to re-export my MySQL databases or some other headache filled process that, in my opinion, was not actually going to do the trick. Instead, I used a very simple approach – search and replace. We’ve all done it folks, sometimes just inside a Word document when you realized that you’ve misspelled the name of the main character in your latest Shakespeare paper, but this time I needed to use some sql code to achieve the same thing, no CTRL + F GUI.

The way you want to go about doing this is to open up a mysql manager (usually written in php) like phpMyAdmin. Click on the database you want to make the changes to (usually includes the name wordpress in it somewhere) and then click on the “SQL” tab. This page gives you the chance to execute SQL statements on the database. One important note: BE SURE TO BACKUP YOUR DATABASE BEFORE EXECUTING ANY QUERIES OR TRYING TO ALTER IT IN ANYWAY. From this page, insert the following code and execute it:

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content` , "’", "'" );
UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content` , "…", "..." );
UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content` , "–", "-" );
UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content` , "“", "\"" );
UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content` , "â€", "\"" );

Let me quickly explain the syntax. UPDATE just means that the query isn’t going to add or delete any data, only modify or update it. The next word inbetween the odd quote set is the table name. This is the table typically used by wordpress to hold the post content. SET is another sql keyword which is followed by the field within the table that you’d like to perform the replace function on. Next, REPLACE is the name of the function we are using (remember, we are performing a search and replace). Its parameters are the field name (yes again), the set of letters we are looking for in the field, and finally the set of letters we’d like to actually do the replacing (that is, be inserted into the field).

So, just simply execute those lines of code (you can place them all in the SQL text area at one time as long as you remember the semi-colons as separation) and presto, your content should be good as new.

Popularity: 34% [?]

Trackback URI |