With the advent of “tags” in the WordPress blogging platform there have been several very good plugins introduced which can handle management of your tags. This includes merging tags.
Why Would I Want To Merge Tags?
One word… duplicates. In my case I created a tag for my Friday Song Of The Day posts. The original post slug was /friday-sotd/ however as time went on something happened and I noticed that somehow I had created a second tag with the same title but with /friday-song-of-the-day/ as the slug. Well, that’s a problem if I want to link to all of the posts in that tag, because there are really two different tags.
Ok, so you’d figure I would go out and get one of these shiny new tag plugins and be done with it in a few minutes, right? Heck no! I’m an under-the-hood kind of guy. I like to know how and why stuff works. So I set out to discover the changes that would be needed in my database to perform the merge. Why? Well, now I have the capability to write my own plugin if I so desire… or I could write a blog post to teach other people how to do it. 🙂
So How Do I Do It?
Well, as it turns out this is really quite simple. There are just a few queries that you’ll need to run using the command line MySQL client or a graphical client such as phpMyAdmin.
Step 1) Backup Your Database!
This should go without saying… but I’ll say it. Before making any major modifications to any live system you should make a backup. I wrote an article on creating a Bash Script To Backup Your Website which you can use or one of any other numerous methods.
Step 2) Get The Tag IDs
Next you are going to need to know the IDs of the tags which you are going to be merging. The “tags” are stored in the wp_terms table. In my case I performed a SQL search in the table such as this:
SELECT * FROM `wp_terms`
WHERE `name` LIKE 'Friday Song%'
As you can see in the following screenshot of the query results, the two IDs that I am interested in merging are 47 and 91. I note this and move to the next step.
Step 3) What Posts Are Affected?
This step is not really required. You could simply head off to the UPDATE step and trust that everything will be fine. I myself like to know what I will be affecting though, so this query will return some information about all of the posts that are tagged with our two tags:
SELECT
`wp_posts`.`ID`,
`wp_posts`.`post_date`,
`wp_posts`.`post_title`,
`wp_terms`.`name`,
`wp_terms`.`slug`
FROM
`wp_posts` INNER JOIN
`wp_term_relationships` ON (`wp_term_relationships`.`object_id` = `wp_posts`.`ID`) INNER JOIN
`wp_terms` ON (`wp_terms`.`term_id` = `wp_term_relationships`.`term_taxonomy_id`)
WHERE
`wp_term_relationships`.`term_taxonomy_id` IN (47, 91)
Here is a screenshot of the results:
Yeah, that gives us somewhat of a picture but there are just too many results to see all at once. Let’s refine that query a bit to just give us a count of the posts related to each tag. Now the query has changed to:
SELECT
COUNT(*),
`wp_terms`.`slug`
FROM
`wp_posts` INNER JOIN
`wp_term_relationships` ON (`wp_term_relationships`.`object_id` = `wp_posts`.`ID`) INNER JOIN
`wp_terms` ON (`wp_terms`.`term_id` = `wp_term_relationships`.`term_taxonomy_id`)
WHERE
`wp_term_relationships`.`term_taxonomy_id` IN (47, 91)
GROUP BY `wp_terms`.`slug`
And here is the screenshot of those results. As you can see now I have a nice count. 116 records are tagged with the /friday-song-of-the-day/ slug and 25 are tagged with the /friday-sotd/ slug.
Step 4) Merging Tags… Commence!
Well, you may be disappointed, but the query to perform the merge is actually a simple UPDATE query. Here it is:
UPDATE `wp_term_relationships`
SET `term_taxonomy_id` = 91
WHERE `term_taxonomy_id` = 47
Simple, right? All we are doing is changing the listings in the relationships table that are ID 47 to 91. So now there should be no posts that are tagged with the “tag ID” 47. Below is a screenshot after running the query and you can see that 25 rows were affected. This is the same number that you can see above when we counted the posts under each tag.
Now if we run our query to count the number of posts under each tag we get 141, which is the sum of the original two counts 116 + 25. Great!
Step 5) Cleanup
Well, we could stop here, but there are still a few leftover artifacts from the old tag (ID 47). Let’s go ahead and get rid of them:
DELETE FROM `wp_terms` WHERE `term_id` = 47
DELETE FROM `wp_term_taxonomy` WHERE `term_id` = 47
Step 6) Redirect
What! We’re not done yet? Almost. After making the above changes I went to double check my work by visiting the actual blog. I pulled up the tag archive page for the old tag by going to http://www.franzone.com/tag/friday-sotd/. It works! Now I’m presented with a “No Posts Found” page and a search box. But… that’s not very appealing. I believe I’ve actually linked to this URL in past posts, which could be a problem.
htaccess file to the rescue! This is actually a pretty simple fix. I just opened up my .htaccess file in a text editor and added a file like this:
Redirect 301 /tag/friday-sotd/ http://www.franzone.com/tag/friday-song-of-the-day/
Now if you visit that URL you get redirected to http://www.franzone.com/tag/friday-song-of-the-day/ instead. Perfect!
Final Notes
So there you have it. You can go about merging your own tags and hacking up your WordPress database. Of course you could take a scripted approach to the above steps. Or you could create your own tag merging WordPress Plugin. Or you could leave a comment below if you were so inclined! Thanks for stopping by.