By now most people have figured out what is the difference between categories and tags, and which ones to use where. But that doesn’t necessarily mean that the problems are over. Once in a while there is a need to convert from one to another.
WordPress provides the tool to convert from categories to tags. But I needed something for the reverse process. In fact, I had a slightly more complicated situation – I already had both a tag and category with exactly the same name, and a whole lot of posts which were tagged, or categorized, or both. Messy! I was looking for a quick way to add all posts tagged with specific tag to the category with the same name. Once that is done, I could easily delete the tag. After a few Google searches, nothing straight-forward came up, so I decided to not waste anymore time and wrote my own little script.
Below are the pros and cons of my approach.
Pros:
- Standalone script, not a WordPress plugin. It is not utilizing any of the WordPress configuration or functionality, but raw PHP and direct connection to the database. You can do a dump of your database, restore it on a totally different machine and play with this script until you are sure you are getting the expected results. No worries about screwing up the live database.
- Requires both the tag and the category to be present. It will not proceed unless it will find both.
- Safe. While there is no undo for the effects of the script, it still works pretty safe. Nothing is ever deleted or updated in the database. Only the new records are added – for posts to appear under the selected category. Nothing else!
Cons:
- Standalone script, not a WordPress plugin. It is not utilizing any of the WordPress configuration or functionality. You will have to specify your database credentials by editing the script. You will also might need to change SQL queries to reflect your WordPress tables prefix.
- Runs from the command line. If you don’t have access to or don’t know how to use the command line – tough luck!
- Code quality. I wrote this as a very quick and dirty solution to my problem. It is not intended to be executed frequently. Neither it is not intended to be executed by someone other than myself. Yeah, I know, this will probably change, but I don’t care at this stage.
Now, you’ve been warned, so I won’t be holding you off from the source code any longer.
#!/usr/bin/php
<?php
$db_host = 'localhost';
$db_user = 'user';
$db_pass = 'password';
$db_name = 'wordpress_db';
$conn = mysql_connect($db_host,$db_user,$db_pass) or die("Failed to connect to db");
mysql_select_db($db_name, $conn) or die("Failed to select db");
$tag = $argv[1];
echo "Looking for tag '$tag'\n";
$sql = "SELECT * FROM wp_terms WHERE slug = '$tag'";
$term_id = null;
$sth = mysql_query($sql);
$result = mysql_fetch_assoc($sth);
if (!empty($result['term_id'])) {
$term_id = $result['term_id'];
echo "Found term_id [$term_id]\n";
}
else {
echo "No term_id found for specified tag [$tag]\n";
exit;
}
echo "Looking for tag taxonomies\n";
$sql = "SELECT * FROM wp_term_taxonomy WHERE term_id = $term_id";
$cat_taxonomy_id = null;
$tag_taxonomy_id = null;
$sth = mysql_query($sql);
while ($result = mysql_fetch_assoc($sth)) {
if (!empty($result['term_taxonomy_id']) && !empty($result['taxonomy'])) {
if ($result['taxonomy'] == 'category') {
$cat_taxonomy_id = $result['term_taxonomy_id'];
echo "Found category taxonomy with id [$cat_taxonomy_id]\n";
}
elseif ($result['taxonomy'] == 'post_tag') {
$tag_taxonomy_id = $result['term_taxonomy_id'];
echo "Found post_tag taxonomy with id [$tag_taxonomy_id]\n";
}
else {
echo "Ignoring unknown taxonomy [" . $result['taxonomy'] . "]\n";
}
}
}
if (!$cat_taxonomy_id || !$tag_taxonomy_id) {
echo "Missing either category or post_tag taxonomy ID\n";
exit;
}
echo "Looking for all posts tagged with tag [$tag]\n";
$sql = "SELECT * FROM wp_term_relationships WHERE term_taxonomy_id = $tag_taxonomy_id";
$sth = mysql_query($sql);
$tagged_objects = array();
while ($result = mysql_fetch_assoc($sth)) {
if (!empty($result['object_id'])) {
$tagged_objects[] = $result['object_id'];
}
}
echo "Found " . count($tagged_objects) . " posts\n";
echo "Moving posts tagged with [$tag] to category [$tag]\n";
$stats = array();
$stats['all'] = 0;
$stats['ok'] = 0;
$stats['failed'] = 0;
foreach ($tagged_objects as $object_id) {
echo "Checking if post [$object_id] is already in category [$tag]\n";
$sql = "SELECT * FROM wp_term_relationships WHERE object_id = $object_id AND term_taxonomy_id = $cat_taxonomy_id";
$in_category = false;
$sth = mysql_query($sql);
$result = mysql_fetch_assoc($sth);
if (!empty($result)) {
$in_category = true;
echo "Already in category. Skipping\n";
}
else {
echo "Not yet in category. Processing\n";
$stats['all']++;
}
if (!$in_category) {
$sql = "INSERT INTO wp_term_relationships SET object_id = $object_id, term_taxonomy_id = $cat_taxonomy_id";
$sth = mysql_query($sql);
$affected = mysql_affected_rows();
if ($affected > 0) {
echo "Added post [$object_id] to category [$tag]\n";
$stats['ok']++;
}
else {
echo "Failed to add post [$object_id] to category [$tag]\n";
$stats['failed']++;
}
}
}
print_r($stats);
?>
Save the source code to wp_tag2cat.php file, edit with appropriate database credentials and location, make it executable and run it as:
$ ./wp_tag2cat.php some-tag-slug
As you can see from the source code, the script is based on precise match of the tag slug. If you don’t have both the tag and the category with the specified slug, the script will complain and abort.
Enjoy!