If you are using WordPress for anything else than a simple blog, you might need at some point to export database records to CSV. In this tutorial I will show how you can export records to CSV, not just plain tables, but anything you can extract using Mysql and wp_query.
The code will work either in a plugin or in functions.php in a theme. But let’s do the plugin, as its easier to reuse on different sites.
Creating a WordPress plugin and create a dashboard menu
The steps of creating a plugin are the usual ones. We will also add a dashboard menu.
To create the plugin you should create a folder with the name of the plugin in WordPress plugins folder and then add a php file where we write the plugin’s code.
In my example, I have created a folder “Exporttocsv” and a file exporttocsv.php inside.
Let’s add the plugin header:
<?php /* Plugin Name: Exporttocsv Description: Exports SQL results to CSV Version: 0.0.1 Author: Cristian Dorobantescu Plugin URI: https://www.wp-starter.com Author URI: https://www.wp-starter.com */
Now, we should add the dashboard menu.
function Export_to_csv () {
add_menu_page(Export to CSV','Export to CSV','manage_options','export_csv', exporttocsv_options_page);
}
add_action('admin_menu','Export_to_csv');
If you want to understand more, head over to how to create the menu and plugin tutorial.
Export to CSV from Wp_query
For this example, we are going to export a CSV list of the posts titles published by the authors. This is done in 2 steps:
- create the SQL to extract the records from the database
- create the code that uses the wp_query results to build the CSV.
Wp_query to extract the results from the WordPress database
To see what posts are created by which WordPress user we need to look into 2 tables: wp_posts and wp_users and create the SQL.
"Select display_name,post_title from wp_users left join wp_posts on wp_users.ID = wp_posts.post_author"
I strongly recommend that you build and test the SQL in something like phpMyAdmin first. If it’s working, the rest of the plugin will work, if it’s not, the plugin will not work and you will not know why…
Now that we have the SQL, let’s create the wp_query:
$posts = $wpdb->get_results("Select display_name,post_title from wp_users left join wp_posts on wp_users.ID = wp_posts.post_author");
Code to export the results to CSV
When we created the dashboard menu we said that the content for the admin page will be created via a function called exporttocsv_options_page. Let’s build the function and add the code that generates the page content there. To keep things simple we just add a link with 2 parameters:
function exporttocsv_options_page () {
?>
<div id="wrapper"><p><a href="tools.php?page=export_csv&to_export=1">Export to csv</a></p></div>
<?php
}
Let’s create the CSV code
As with anything you want to do in WordPress, let’s start by adding an “Action” and the function that check if the parameter “to_export” is sent in the link, and if the user has the right to use it:
add_action('init', 'Exporttocsv_EXPORT');
function Exporttocsv_EXPORT(){
$export= isset($_REQUEST['to_export']) ? $_REQUEST['to_export'] : '';
if ($export && is_admin()){
echo GENERATE_EXPORT($export);
exit;
}}
The function above basically says: if the “to_export” parameter exists, the user has admin rights, then execute the function GENERATE_EXPORT. So let’s create the GENERATE_EXPORT function where the magic happens:
function GENERATE_export($export){
ob_clean();
global $wpdb;
$file_name = 'csvexport'.date('Ymd_His').'.csv';
}
ob_clean() – This function discards the contents of the output buffer and global $wpdb – declares $wpdb as a global variable (WordPress defines a class called wpdb, which contains a set of functions used to interact with a database). $file_name is the name of the CSV file that is generated.
The next thing to do is the create the variable that holds the comma separated values. Let’s start with the header of the CSV – we create the variable $fields and we list the columns of the CSV, then $fields .= is the way to add more things to the variable (PHP stuff). “\n” says that a CSV row is finished.
$fields="Author,Post title"; $fields .= "\n";
As we have the CSV header values, we should generate the rows with the content from wp_query. Let’s insert the wp_query we build before:
$posts = $wpdb->get_results("Select display_name,post_title from wp_users left join wp_posts on wp_users.ID = wp_posts.post_author");
We should cycle through the results of the wp_query and assign values to the $fields value.
foreach($posts as $post) {
$fields .= $post->display_name.',';
$fields .= $post->post_title.',';
$fields .= "\n";
}
The code above assigns display_name and post_title to the $fields variable. Notice the .’,’ addition to the end, that adds a comma after each value.
Generating the CSV file headers
header('Content-Encoding: UTF-8');
header("Content-type: text/x-csv; charset=UTF-8");
header("Content-Transfer-Encoding: binary");
header("Content-Disposition: attachment; filename=".$file_name);
header("Pragma: no-cache");
header("Expires: 0");
$bom=chr(239).chr(187).chr(191);
I won’t go into much details here, as this is the way to generate the headers for a file in PHP.
However, $bom=chr(239).chr(187).chr(191); tells excel document is utf8 encoded – just in case you have UTF8 content.
The last line is to actually create the file:
return $bom.$fields;
Just to make sure you got everything right, here is the code from GENERATE_EXPORT function:
function GENERATE_export($export){
ob_clean();
global $wpdb;
$csv_file_name = $file_name = 'csvexport'.date('Ymd_His').'.csv'; # CSV FILE NAME WILL BE table_name_yyyymmdd_hhmmss.csv
$fields="Author,Post title";
$fields .= "\n";
$posts = $wpdb->get_results("Select display_name,post_title from wp_users left join wp_posts on wp_users.ID = wp_posts.post_author");
foreach($posts as $post) {
$fields .= $post->display_name.',';
$fields .= $post->post_title.',';
$fields .= "\n";
}
header('Content-Encoding: UTF-8');
header("Content-type: text/x-csv; charset=UTF-8");
header("Content-Transfer-Encoding: binary");
header("Content-Disposition: attachment; filename=".$csv_file_name);
header("Pragma: no-cache");
header("Expires: 0");
$bom=chr(239).chr(187).chr(191);
return $bom.$fields;
}
Let’s test. Go to admin, and look in the left menu for Export to CSV, then click on Export to CSV. Tada!