WordPress: Change Post Category By The Post Date

So, I’ve got a challenge. I’ve got a WordPress blog with 875 posts categorized as ‘Uncategorized’. This is rather unwieldy for our friendly web visitors. I’ve created post categories for the individual years 2009-2024, but I really don’t want to go through and change the post categories manually. I can’t find a plugin that does this, so instead we’re going to use SQL commands to find the posts by year and update them to the correct category.

So, what do we need to have to do this?

• phpMyAdmin – a handy GUI tool for working with databases that is surely available through your hosting provider. These commands can also be issued at the MySQL command line. This post assumes you have some ability to use phpMyAdmin, or are prepared to learn as you go.
• I recommend testing these commands on a copy of your website, or a test site first to make sure you’re getting the results you’re looking for before you touch your live site.
• MAKE. A. BACKUP. Before you make any changes, always run a quick backup of your WordPress database. That way if you make a mistake, you can simply roll back to where you were. There’s numerous ways to do this, I often use the WP-DBManager plugin.

You’re looking at the tables in your WordPress database and you’ll see changing the post category isn’t as simple as you’d think. The reason this is a bit complicated is because the posts themselves are in one table, each one identified by ‘id’. The categories themselves are stored in a separate table called ‘wp_terms’, each identified by ‘term_id’. The ‘wp_term_relationships’ table links the post to the category, which as we’ve seen are in two separate tables.

Here is the template code that gets it done:

UPDATE wp_term_relationships
SET term_taxonomy_id = (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = 'TERM-ID-GOES-HERE')
WHERE object_id IN (
    SELECT p.ID FROM wp_posts p
    WHERE p.post_date LIKE 'DATE-GOES-HERE%' AND p.post_status = 'publish'
)
AND term_taxonomy_id = (
    SELECT term_taxonomy_id FROM wp_term_taxonomy
    WHERE term_id = (SELECT term_id FROM wp_terms WHERE name = 'Uncategorized')
);

How does this work? We need to know two things: the date of the posts we want to change, and the ‘term_id’ of the category we want to set for those posts.

WHERE p.post_date LIKE '2011%' AND p.post_status = 'publish'

The line above will pull all posts with the date of 2011. To grab posts from 2012, we simply change that line to this:

WHERE p.post_date LIKE '2012%' AND p.post_status = 'publish'

The dates are formated like this: ‘2012-02-19 02:41:35’ so with wildcards you can do something like grabbing only the posts from February 2012 like so:

WHERE p.post_date LIKE '2012-02%' AND p.post_status = 'publish'

Now we also need to know the ‘term_id’ of the category we want to assign to those posts. Look inside your ‘wp_terms’ table and find the one for the category in question. Using phpMyAdmin, I see this:

As you see, my WordPress database is named ‘wp_1’ and I’ve selected the ‘wp_terms’ table. On the right I can see that the ‘term_id’ for category ‘2012’ is ‘5’.

So, in order to assign posts dated as 2012 with the category ‘2012’ the code looks like this:

UPDATE wp_term_relationships
SET term_taxonomy_id = (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = '5')
WHERE object_id IN (
SELECT p.ID FROM wp_posts p
WHERE p.post_date LIKE '2012%' AND p.post_status = 'publish'
)
AND term_taxonomy_id = (
SELECT term_taxonomy_id FROM wp_term_taxonomy
WHERE term_id = (SELECT term_id FROM wp_terms WHERE name = 'Uncategorized')
);

And that should do it! Note: at the time of this writing I’m using WordPress 6.4.2.

While we’re at it… I discovered numerous garbage characters like ‘•’ and ‘Â’. Why not get rid of those? Here’s how to do it:

UPDATE wp_posts SET post_title = REPLACE(post_title, 'Â', '');
UPDATE wp_posts SET post_title = REPLACE(post_title, '“', '“');
UPDATE wp_posts SET post_title = REPLACE(post_title, '”', '”');
UPDATE wp_posts SET post_title = REPLACE(post_title, '’', '’');
UPDATE wp_posts SET post_title = REPLACE(post_title, '‘', '‘');
UPDATE wp_posts SET post_title = REPLACE(post_title, '—', '–');
UPDATE wp_posts SET post_title = REPLACE(post_title, '–', '—');
UPDATE wp_posts SET post_title = REPLACE(post_title, '•', '-');
UPDATE wp_posts SET post_title = REPLACE(post_title, '…', '...');

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, '‘', '‘');
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, '…', '...');

This info courtesy of https://www.realbasics.com/note-to-self-fixing-weird-unicode-characters-in-a-wordpress-sql-database/. If you wish to get deep into the weeds regarding WordPress and character sets… perhaps you’ll enjoy this: https://codex.wordpress.org/Converting_Database_Character_Sets