Remove Unwanted Characters from WordPress Post Content after WordPress MySQL Migration

Today I learned… how to remove unwanted characters like â€ and Á from WordPress post content using a MySQL query.mysql_logo

Backstory: I recently migrated a blog from shared hosting to a VPS. This experience alone could fuel TILCode content into 2024, but this particular “TIL” article is about a curious character encoding artifact that occurred as I exported the WordPress MySQL data and imported it elsewhere: Á and â€ symbols everywhere!

Removing these unwanted symbols from my WordPress posts required running a few queries on my MySQL databases. Here are the steps I used.

(And if there’s a better/easier way to do this, please let me know in the comments. This was my first foray into MySQL queries, but it got the job done.)

Step 1: Log into PhpMyAdmin

Access phpmyadmin through your cPanel or yourdomain.com/phpmyadmin – access varies by host and setup

Step 2: Navigate to the SQL tab and change collation

Click on the “PhpMyAdmin” logo so that you’re at the “root” level (and not inside a database – this might work from inside a database, but I did it from outside).

Click on the SQL tab.

mysql_button

Here, you’ll get a large window in which you can type queries – operations you perform on your data. 

mysql_queries

The first query to run is one that will set the character encoding type. 

ALTER DATABASE your_db_name_here DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I learned this from JesseWeb’s helpful tutorial on resolving WordPress character issues, but his code didn’t work for me as-is (I suppose that’s not surprising considering his guide is nearly 5 years old). I had to remove the quotes from the database name.

Your query will look like the image below. Be sure to replace your_db_name_here with your database’s actual name (look in the left column for your db names).

Click the “Go” button on the right to run the query.

mysql_queries_in_place

Step 3: Remove unwanted characters from existing posts

For this query, you need to navigate to the actual database itself. Click on its name on the left.

navigate_to_mysql_database

Click on the SQL tab. Now the helper text says that queries are run on the database you selected.

queries_inside_db

Enter this query string to replace all instances of â€ with an empty space:

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '');

Repeat the query for every unwanted character. Note also that you can stack ’em up in the query window like so and run ’em all at once:

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, 'foo', 'bar');

You may also need to clean up comments. You can do that with:

UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'Á', '');

See shadez’s question on WordPress.org for more examples – his example was instrumental in helping me understand this issue.

And with that, all the unwanted characters are gone. Phew!

WordPress Genesis Framework – Showing a List of Post Titles in Category View

Today I learned… how to customize the “Category” view in WordPress (Genesis framework) to show just the post titles as ordinary links in a list.

I really like the way posts of a category are displayed on DailyBlogTips. Each post gets a bullet in a simple unordered list.

posts_as_list_items
DailyBlogTips’s category view shows just the post titles in an unordered list.

By default, WordPress (and Genesis) gives you two options for displaying posts by category: either their full form one after another (do not want), or a truncated version with the post title and excerpt (also do not want).

A solution in which the titles were rendered as <a hrefs> between <li></li> tags required modifying the loop when on a Category page.

Please note that this code requires a theme built on the Genesis framework, though it should not be hard to modify the hooks to suit your framework if you know your way around a bit.

Copy the contents of functions.php into your child theme’s functions.php file.

Here’s what this is doing, in English:

When the current page is ‘Categories’ (Line 9), don’t do the usual genesis_loop (Line 13). Instead, do this custom loop (Line 14) called mjg_custom_loop.

Over in mjg_custom_loop, create a new unordered list (Line 21) and for each post (Line 22), echo its permalink and its name into a set of <li> </li> tags (Line 24).

The contents of style.css will probably need to be customized to fit your site’s design, but hopefully this is enough to get you through the hardest part which is customizing the loop on the category page.

Here is how it looks on the site:

category_links_only
Titles-only Category view on TowerSecrets.com