May 12, 2009 | Filed Under MySQL, Ubuntu, WordPress    120 views

WARNING: All of the recommendations in this post work off of a test database. The following commands could damage your live site if not tested first. Always work off a test site, then perform a database backup before doing any of these things.

I was faced with a challenge at work this week: I wanted to track how many downloads our forms were getting on the company intranet (built on WordPress). I had already uploaded over one thousand forms to the site. I needed an upload tool that would be able to look at all of the forms and other media that we have accumulated. Unfortunately, none of the download counter plugins for WordPress will look add existing media, and they all want you to upload your forms (or media) through their “Add download” button.

Well, if I’d uploaded only a couple of forms, this wouldn’t be a problem. But since I was dealing with one thousand, I needed a way to import existing uploads into the new download counter. This would have to be done in MySQL.

I looked at download counters on wordpress.org and found one that I liked: WP-DownloadManager. There seem to be quite a few of them, including WP-DownloadCounter (which I also liked) but I chose this one simply because it had been around longer and had a very high rating.

WP-DownloadManager has a way to import existing forms from the management page, but they have to be done one at a time, and you have to manually type all of your titles over again. This would take way too long. So I came up with a way to migrate all of my uploaded form titles and filenames to the WP-DownloadManager’s database.

I think this is a good time to spell out the configuration of my site (you can skip this page if you just want the code, but do it at your own risk - my configuration is different from the default):

All of our clinic forms are sub-pages of a page called “Forms”. Each of the “form pages” uses a custom built WordPress Template called “Forms”. Each of the form pages points to a file that was uploaded to our local server - it was not uploaded through the media library (making this work with the Media Library is possible, I found, but messy - if you need it done, and we’re talking about having more than 1,000 media files, leave a comment and I’ll post an update).

Since the pdf’s and doc’s are not “attached” like media uploaded via the Media Library, each page has a custom field called “FileName” and the value of the custom field is simply the filename, like “0423_employee_handbook.pdf”. The “Forms template” then points to the directory where the forms are stored (./wp-content/forms/) and the custom field adds the file name to complete the download url.

All of the download managers for WordPress put the title and filename of the download into a MySQL table. Since all of my forms have a custom field called “FileName”, I simply need to create a list of those posts. Then I needed to find the values stored in the custom field called “FileName” that links each form page to the download-able form.

Let’s translate all of that to MySQL terminology:

Here’s what we are doing, in terms of what MySQL knows:

The form pages are in the “wp_posts” table, and custom fields are stored in the “wp_postmeta” table in WordPress. The “wp_postmeta” table tracks the “post_id” that each custom field is connected to. The end result of our query should display the column called “post_title” from the “wp_posts” table, and it should display the value inside “meta_value” from the “wp_postmeta” table if the “meta_key” value is called “FileName”.

I know this sounds extremely confusing; that’s because it is.

So let’s get to the code. This shows me the output of all of my uploaded forms that have a value for the custom field “FileName”:

When I typed this in the MySQL Editor, I got a nice long list that showed Form Titles, then Form Filenames sorted by Filename (I sort by filename because all of our forms are prefaced with a four digit number).

Now that you have the list of forms and their filenames, we want to insert this into the WP-DownloadManager’s database. The first time I did this, I copied it all to an excel file, reformatted the data, saved as a CSV, uploaded to my server, and so on. But MySQL can handle all of that for you very easily.

But I should point out that WP-DownloadManager tracks things that WordPress doesn’t. Specifically, you won’t have the file size until you go into the manager and click “Edit”. Then PHP automatically calculates the filesize. (I don’t think there is a way to have MySQL calculate this as part of our import, but I could be wrong - let me know). (UPDATE: You can run another script to populate all the file sizes - see the end of this article). Permissions and Categories are not available either, but it wouldn’t be impossible to map your post categories over to the WP-DownloadManager categories. I just didn’t need this so I skipped it.

Executing the following code will take your results and map them to values inside the WP-DownloadManager’s database:

Executing the following code will take your results and map them to values inside the WP-DownloadManager’s database:

I should point out that I used the “Post Date” from the WordPress Page and copied this to all three dates inside WP-DownloadManager (file_date, file_updated_date & file_last_downloaded_date). And of course, the hit counter starts at zero. I should also point out (again) that the “FileName” custom field is unique to my configuration.

Just like that I have all of my 1,000 forms showing up in the Download Manager.

Now that they are in the WP-DownloadManager database, I need to find a way to link to the downloads from my template or add the “file_id” value to my wp_posts table. That’s because the WordPress pages that I’ve created for each form only know the file name, they don’t know anything about the Download Manager. The pages were created before the WP-DownloadManager created the “file_id” values.

MySQL allows a simple solution to this as well. Since I’ve already created a custom field called “FileName” and since this value is the same as the “file” value inside the wp_downloads table, I just need to query MySQL and pull in the associated “file_id” value.

To connect the “file” value to the “file_id” value, I just add the following function to the bottom of the wp-downloadmanager.php file:

Then I will pull this variable into each page or template where I want to track downloads. In my case, I add it to the top of the forms.php file:

Where I’ve pulled my file name into a variable called $FileName.

Then I stick the $downloadID variable on the end of the download url and I have tracking for all of my downloads.

That said, I’m aware that you probably don’t have a custom field for all of your file names. If you uploaded via the Media Library, you are in luck. The “file” field in the wp_downloads table matches the “post_title” value in the “wp_posts” table for your attachments uploaded through the media library. So you can match those up the same way that I do in this example, but you’ll have to pull in your “post_title” of the attachment and stick it into a variable first. This will vary depending on your template, theme, etc.

If you uploaded all of your Media using the Media Library, doing the bulk import will take more work than mine did above. That’s because WP-DownloadManager references files by their full file name (like “brockangelo.jpg”) but WordPress references them by title and mime type (like “brock_angelo” for the title and “image/jpeg” for the mime type). So for this to work, MySQL would have to conditionally concatenate file names to file types. Not pretty.

On the other hand, if you need to make this happen, you can perform the following query to output the results to the screen, then paste them into excel to do the cleanup. From there, you can pull the information back in through MySQL. This query shows you all of the media that is in your library, with all necessary info you would need to put into excel for a MySQL import back into the WP-DownloadManager database:

Leave a comment if you would need instructions on how to finish this up.

If you imported all of the forms, and want the file sizes to show up in the database, there is a script that you can put into a php file that will populate all of the file sizes into the database. Thanks Lester for helping this along!

I put this in my “single.php” file, but I would guess that you could throw it in any file. You’ll put this php code into one of the templates in your theme (single.php, page.php, etc), load the page once so that it will gather all of the info, then you’ll need to remove it from the php file. It will populate the database, but in the process, it will throw a warning for each file and display it in your browser. Trust me, it does not look pretty; you get a page full of warnings all over your website. Best to use an unused template or something that no one else will come across. But it works, or at least it did on my configuration (I’m running an Ubuntu dedicated server). Here you go: