Archives

How To Manually Merge WordPress Tags

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:

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:

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:

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:

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:

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:

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.

Calling A Stored Procedure In Java With A Blob Field

Here is the situation. You are writing a program in Java. You need to access data in a Microsoft SQL Server via stored procedure and the data has an XML field. A what field? SQL Server 2005 introduces a new datatype for XML. It is essentially a blob field that you can perform XML operations on. For the Java program though, it doesn’t really care about that functionality and can just access it to write it out to a file (or process it inline).

JDBC Support

First off you are going to need to check your JDBC driver documentation thoroughly for which operations it supports. There also seems to be some variation in the actual implementation. For example, I tried used Microsoft’s own JDBC driver in my project but it caused a strange SQL Server error to be thrown when writing back to an XML field. I could not get around it (at least not in a timely fashion) so I switched to using the JTDS JDBC driver (go open-source!).

The Stored Procedure

Let us say that the stored procedure in SQL Server looks something like this:

Nothing major going on here, it just executes a SELECT statement against a table and returns four fields. The “LargeXMLField” column is the one we are looking at as a Blob (actually a Clob).

Writing The Code

The actual code to access the stored procedure should be nothing new and is not complicated at all. First we need to connect to the database in the standard fashion.

You will want to substitute real values in where the [host], [port] and [database] placeholders are in the database url.

Next we will use the CallableStatement object to execute the stored procedure.

So you see we use the proc.getMoreResults() to iterate through multiple ResultSet objects that may be returned from a stored procedure. We then call proc.getResultSet() to retrieve the ResultSet that has our data. We fetch our data field as a Clob object and use it’s getAsciiStream() method to get an input stream to read from. After that reading from the stream and writing out to another stream is a straightforward task.

Where Did My Categories Go?

Organizing Your BlogTo tag heaven. Ah, yes… tags. The teacher’s pet of Web 2.0. The category list on this blog had become rather unwieldy as I just plugged in new categories whenever I did not feel like putting any thought into organization. I never really liked that method and so when WordPress implemented tags I figured I’d make the move. The only problem was converting all of my old categories into tags. This was quite an undertaking and it actually took some MySQL DB tweaking… muahahaha!

Converting Categories To Tags

Fear not, though! There is a Category to Tag converter in the WordPress admin under Imports. This came in quite handy. However, if you did like me and waited too long you may have some terms that are both a category and a term. For instance I used Apple as a category for a while and then started tagging posts with Apple instead. The Category To Tag converter will not convert your category in this instance… bummer. If you need to solve this problem go back and remove all of the Apple tags from posts and mark them with the equivalent category. Once that tag is completely gone from all posts you can delete the tag in your system. How do you do that?

Well, using phpMyAdmin or another similar SQL tool for accessing your MySQL database issue the following query:

This will get the term_id for you which you will need in the next query. So write down the term_id and move to the next step:

OK, if you truly do have both a category and a tag in your system there should be two entries showing. One will have a taxonomy = ‘post_tag’ and the other will have a taxonomy = ‘category’. In phpMyAdmin you can just click the red X next to the ‘post_tag’ row and DELETE it. Otherwise you can issue a query like this:

Now that the tag is completely gone the converter should allow you to convert this category to a tag… and then your laughing (as the British say).