Archives

Dump A MySQL Database To YAML Using PHP

The Problem

I was recently working on a project that is built on WordPress / BuddyPress. I built up some content using the CMS features of WordPress in my development server and then I transfered the WordPress content to a beta server using the export/import functionality. That kind of stinks having to do that every time I want to push updates to my beta server. So I looked into using Doctrine to generate data fixtures which I could repeatedly run against the different database. Doctrine supports YAML files, which are extremely easy to edit by hand, so it seemed like a good idea.

So what’s the problem? Well, I didn’t currently have any of my data in YAML files. I didn’t want to had edit them or do some sort of copy and paste madness. So, I wrote a script in PHP to dump my data into a YAML file.

Continue Reading

Upgrade Time : WordPress 2.9 and MySQL 5

WordPress 2.9 was release somewhat recently and so like a good WP minion I logged into my site and clicked the update button. Error… uh oh. Apparently WordPress 2.9 requires a more recent version of MySQL (>= 4.1.2). My host installed MySQL 5 ages ago but I’ve never bothered with migrating to the new version because I didn’t have a reason to. Now I do. So here’s how my upgrade went.

First I made a full backup of both the web content and the MySQL database. The MySQL database backup was just a mysqldump command like so:

Continue Reading

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.

How Do I Get Meta Data From MySQL Using PHP?

I know you’ve all asked that question before, right? Well maybe not, but I recently had the need to detect what the DATATYPE for arbitrary columns in arbitrary tables were inside a MySQL database using PHP. Turns out that it isn’t all that hard to get.

The main thing about using this method is that we are simply sending SQL queries to MySQL which instruct it to give us the required information. The first one is to retrieve a list of tables for the currently selected database:

The next time is when we retrieve the columns for the table and the column definitions:

Notice that I looped through the results of the first query (the table names). I then use the value of the table names returned from the query to build the query for extracting the column definitions.

Finally, you might ask why I decided to throw all of the results into an array? I could just as easily (probably more easily) have output the results to the console as I ran through the queries. Well, in my situation I needed to actually store and compare the results at a later time. So that’s what I did! Following is an entire code sample that should work; just fill in your login credentials and have fun!

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).