Creating a WordPress Plugin to Export MySQL data as CSV

Recently at work, a client requested the ability to export user browsing information as a Comma-separated values (CSV) file. I was asked if I could make this happen, and I was pretty sure I could figure it out. It took a couple of hours of research, but It was a fun and educational experience that lead me to new understandings of PHP, WordPress, and MySQL.

I decided to share what I learned with as much concise detail as possible and with the same process I took, because without the extremely helpful guides from the programming communities online, I would not be as strong a programmer as I am today. So thank you everyone, and read ahead for the guide.

Summary

This guide will help you write a WordPress plugin that has an Admin Page which is accessible from the Tools menu on the WordPress Dashboard. It will use PHP to retrieve data from a MySQL database, format it as a CSV file, and send it to the browser to be downloaded without storing a file on the server.

The Steps and The Skills

Step 1 – Send a CSV file to the Browser: PHP
Step 2 – Build the Database Query: MySQL, WordPress Database
Step 3 – Access the WordPress Database: WordPress Development, PHP
Step 4 – Make it a Plugin: WordPress Development, PHP, minimal HTML
Step 5 – Activate and Use the Plugin: End results
Update 1 – Add Content-Length Header: PHP

Step 1 – Send a CSV file to the Browser

The first hurdle I had to jump was figuring out how to create a CSV file in PHP and send it to the browser without storing it on the server. A few short google searches later and I came accross fputcsv() and “php://output“. The fputcsv() function accepts two parameters, a file pointer and an array, and writes the contents of the array into the file. “php://output” isn’t a function, but can be passed into the fopen() function in order to open the PHP output buffer as a file, which is how I avoided saving CSV files to the server before sending them to the browser. So I wrote a (not unit) test:

csv-test.php

[fz_snippet snippet_number=1]

• Lines 5 – 19: On these lines, I’m building the data that we’ll pretend we received from a database query (Make sure that when you build proofs of concepts, that you make everything as close to the real deal as possible). The information that we’ll be grabbing from the database includes “first_name”, “last_name”, “product_viewed”, and “time_viewed”.
• Lines 20 – 25: On these lines, we create a filename and append a timestamp (this is to reduce, if not eliminate, the possibility of duplicate filenames). Then, through http headers, we tell the browser that we are sending a CSV file that should be downloaded with the filename provided on line 25.
• Line 27: This was new for me. When you pass “php://output” into the fopen() function, you get the PHP output buffer wrapped up as a file pointer. In this state, you can perform file writes as usual, but instead of being stored in a file, the file writes are sent to the browser. This is nice, because I don’t want to store unnecessary files on the site.
• Lines 28 – 32: These lines write a CSV header to the CSV file being sent to the browser. First we put the first record in a temporary variable, then we use two functions at once: fputcsv() and array_keys(). array_keys() gives us an array containing the keys of the array passed in as an argument. Finally, fputcsv() is the function that does our heavy lifting. Once again, it accepts a file pointer and an array, as arguments respectively, and writes the contents of the array into the file as data formatted for CSV.
• Lines 33 – 36: Here we loop through the array of data and write it to the output buffer with fputcsv().
• Line 38: Always close what you open! In some languages, not doing this can cause spooky behavior. In PHP, it frees up memory, so do it!
• Lines 4 & 41: Since we’re putting this into a WordPress plugin, we want this code to be easy to use. So we encapsulate the code in a function and call it at the end of the PHP file. To keep things simple, I called my function “export”, but quickly remembered that it’s possible to break other people’s plugins (OPP) if your functions are named the same as their’s. So I added a prefix for my function: “fz_csv_” (my initials and CSV). Try to make your prefixes make sense to your code.

Finally, I put the PHP file on my local server, pointed my browser to the PHP file, and my browser told me to download a CSV file. After downloading, I opened the CSV file with LibreOffice Calc (aka, free Microsoft Excel) and had proof of success:

Step 2 – Build the Database Query

I have a database table, called wp_fz_csv_tracking_info, that stores data tracking what product a user viewed and the time they viewed it.

The SQL query for getting that information is simple enough:

[fz_snippet snippet_number=2]

When it comes to storing data in the database, WordPress does some interesting things. For the CSV I’m exporting, I needed to get the user’s first name and last name, but it’s not in the users table.

I forget how I found this, but the first and last name is stored in the WordPress usermeta table.

Look at how the first name is stored: user_id = 1, meta_key = first_name, and meta_value = Faison. Instead of having a dedicated place in the WordPress database for the first name of a user, WordPress stores the data as a chunck of data associated with both a user_id and a key that could be anything. If we wanted, we could store something like user_id = 1, meta_key = watch_preference, and meta_value = pocket watch; the possibilities are intriguingly endless.

Back on topic though, my first thought was that a simple “LEFT JOIN” wouldn’t do it.

[fz_snippet snippet_number=3]

If we ran that, we would get multiple records returned for each user. I don’t know about you, but I’d rather have one record with all the information I need. To do that, I had to come up with some scary looking subquery with aliasing. Here’s how I got the first name:

[fz_snippet snippet_number=4]

In the subquery (the part between the parenthesis), we’re basically creating a quick one use table, with only user ids and first names, and naming the table “a”. By doing this, we can LEFT JOIN the subquery to the actual table and use a normal SELECT statement. We can do this for the rest of the information stored in the usermeta table, just be sure to use different alias names for each subquery.

[fz_snippet snippet_number=5]
Which Results in the following:

Step 3 – Access the WordPress Database

I could have used the mysql functions, I could have used the PHP Data Object, but WordPress already has a global database object that knows the database credentials and location. With some Googling, I found this post in the WordPress support forums which says we just need to include the wp-load.php file. So I renamed my PHP file and updated the code as follows:

export.php

[fz_snippet snippet_number=6]

• Line 3: This line gives you all the functionality of WordPress. Side Note – If you have your WordPress installed in a subdirectory of your server’s document root, make sure to put the directory name before “/wp-load.php”. So on my computer, it reads “/wp/wp-load.php”
• Line 8: This is how you get WordPress’s global database object
• Lines 9 – 18: In these lines, I write the query as multiple Strings in an array. By doing this, I can do what I did in…
• Line 21: Here, we use the database object’s get_results() function. The first required parameter is a string, so I use the implode() function on the array with the query and specify a space as the “glue”. By doing that, the implode() function returns one string with all parts of the array. The next parameter is a constant that tells the function to return the data as an associative array.
• Line 23: This line is to make sure the database query returned data. If it didn’t and we let the code in the following block run, bad stuff would probably happen.
• Line 49: This discards anything currently in the PHP output buffer. I had to add this line because the WordPress installation I was working with added several lines to the output buffer, causing my CSV file to have 3 blank lines before the CSV file’s header line.

Once again, I put export.php into my local server, direct my browser to the PHP file, and my browser has me download a CSV file. From the browser’s point of view, nothing new happened, but back on the server, our PHP script is getting access to the WordPress database and grabbing data to be returned as a CSV file.

Step 4 – Make it a Plugin

There are many great guides for making plugins for WordPress, but I learned from using this net tuts guide along with the Writing a Plugin page on the WordPress Codex. I’ll sum up what I did here, but if you need more details feel free to ask me or check out those two links.

First, I created a plugin folder in the “/wp-content/plugins” directory. I called mine “/fz_csv_exporter” which will also be the same name as my plugin. If you read guides for writing WordPress plugins, you’ll find that you can put your plugin directly in the “/wp-content/plugins” directory and everything will work well. This plugin is going to have more than one file, so I put it in a subdirectory of “/wp-content/plugins”.

Then, we create the main plugin PHP file. This file goes into the directory we just made and gets called the same thing. So in “/wp-content/plugins/fz_csv_exporter” we now have a file called “fz_csv_exporter.php”. Here are the contents of that file:

fz_csv_exporter.php

[fz_snippet snippet_number=7]

• Lines 2 – 7: This is the standard plugin information header.
• Line 10: We provide this function for WordPress so that when the action specified on line 20 occurs, WordPress will call this function.
• Line 11 – 17: The add_management_page() function allows us to add a link to our plugin page under the Tools menu. The first parameter is the Page Title, the second is the text displayed in the tools menu, the third is the capability required to access this page (‘export’ is exclusive to administrator role), the fourth is the menu slug and needs to be unique to the Tools menu, and the fifth is the name of the function that is called to display this page.
• Line 20: This line uses the add_action() function in order to have WordPress call the function at line 8 when WordPress is building the admin panel.
• Lines 23 – 27: This function is called when a user goes to the page specified in lines 13 – 16. Instead of putting the HTML and PHP right in this function, I use the require() function to get the admin_page from in the same directory as the plugin file.

Now we need the admin_page.php from the code above. This is also in the directory “/wp-content/plugins/fz_csv_exporter”.

admin_page.php

[fz_snippet snippet_number=8]

• Line 1: I nest my entire admin page in a div with the class wrap, because I lose some default styling of elements if I don’t.
• Lines 3 – 4: This is the probably the most interesting part of this file. First, I use a form, because I like the default styling. Then, I specify the action to point to the same directory these files are in, using the plugin_dir_url() function so that I get a url rather than a directory path. Then, I specified the filename “export.php”

Then, I moved the export.php file, from step 3, into the plugin directory. Afterwards, I had a directory containing three files: “fz_csv_exporter.php”, “admin_page.php”, and “export.php”.

Step 5 – Activate and Use the Plugin

I assume if you made it this far, you probably know how to activate a plugin. If you don’t, WordPress has instructions for Manually installing plugins which goes over activating plugins.

After it’s activated, you should see the link to the plugin’s admin page in the Tools menu.

And here’s the admin page:

And when I press the button, I’m prompted to download a CSV file which contain all the data returned from the database query from step 2.

Conclusion

That concludes the process I took in order to create a WordPress plugin for exporting information from the database to a CSV file downloaded through the browser. If something didn’t work right for you, please post a comment and I’ll do my best to help you out.

Also I’m a lifelong learner and am still getting familiar with WordPress development, so I invite constructive criticism and suggestions on everything I write. Since this is my first guide, I would definitely appreciate feedback on everything.

Thanks much everyone!

Update 1 – Add Content-Length Header

As suggested by Joel Clermont in the comments, I looked into adding a Content-Length header to the script. Joel explained that if the file is very large, “the user’s browser would be able to show a proper progress bar.”

So I decided to introduce a loop in csv-test.php from step 1, that would execute lines 10 – 19 100,000 times. When I pointed my browser to it, I ended up downloading an 8.9MB file without ever knowing what percent was complete.

I never sent a Content-Length header in PHP before, so I ran to google and found a forum thread on devshed’s PHP Development section. After reading that a bit, I added this to export.php:

export.php

[fz_snippet snippet_number=9]

• Line 46 – 48: First I use the ob_get_length() function to get the length of the content in PHP’s output buffer, then I store it in a variable to make things look nice. Finally, through the http header “Content-Length”, I send the previously mentioned length to the user’s browser.

Since my database didn’t have many records in it, I put those same lines (46 – 48) into the modified csv-test.php from step 1. When I pointed my browser to the file this time, my browser displayed a proper progress “bar” as it downloaded.

Thanks again to Joel, and everyone else who comments and reads!