Post

Another Random Media & Technology Post

Export WordPress Data

Export WordPress Data
Several of my clients use the Events Made Easy WordPress plugin to book classes. It works great and even has the option to export *.csv files from the registration data. However, there isn’t a way to set the order of the fields or draw certain, specific information, which my clients need to be able to do, that is, export WordPress data in a specifically ordered format into Excel
So, I discovered that it’s actually pretty easy to pull a *.csv file (which is easily opened in Excel) from a MySQL database table using ‘fputcsv’ & ‘fopen’ and then arrange the fields in any order that is desired.
First, I need to set the variable parameters for a query and create a page with a link to a new WordPress Theme template file I’ll call ‘Download.php‘ file which runs the SQL query code and creates the CSV file. You can then create a new page using the ‘Download.php‘ template.  The front end form can be on any page (as shown just below). It just needs a link which passes the variables to the ‘Download.php‘ file . I created a front end that displays a button. The variables $downloadID and $dlDate can be taken from an input dialog box. What is shown below is simply two input text boxes which are used to collect the desired parameters for the SQL query.

<form action="http://www.yourdomain.com/download-page/" method="post" name="FullDownload">
<label>Event ID Number:
<input type="text" name="downloadInfo" value=""></input></label>
<label>Event Date:
<input type="date" name="downloadDate" value=""></input></label>
<input type=submit Value="Full Download">
</form>

Then, in the ‘Download.php‘ file, I just need an SQL query to pull information from the Events Made Easy tables in the WordPress Database:

 <?php

$downloadID = $_POST["downloadInfo"]; //frontend form Event ID Number
$dlDate = $_POST["downloadDate"];     //frontend form Event Date
$classDate = date("m-d-Y", strtotime($dlDate));
$fileName = $downloadID.'.csv';       //Names the *.csv file using the Event ID

$downloadSQL = "SELECT DISTINCT a.firstname AS 'First Name',a.lastname AS 'Last Name',a.MI,a.suffix AS Suffix,CONCAT_WS(' ', a.address1, a.address2) AS Address ,a.city AS City,a.state AS City,a.zip AS Zip,a.phone AS Phone,a.email AS EMAIL,b.event_id AS 'Class ID'
FROM wp_eme_bookings AS b
JOIN wp_eme_people AS a ON a.person_id = b.person_id
WHERE b.event_id = $downloadID ";

?>

So this query uses the Event ID in Events Made Easy to pull the registrations from a specific event and arranges the information in the order and only the specific data that is needed. The fields in the  *.csv file are in the same order as the SQL Query is ordered. In this case it is:
First Name Last Name Address City State Zip Class ID
By simply rearranging the order of the query, the order of the .CSV file is changed. For example, to have ‘Last Name’ first, all that needs to be done is to change a.firstname AS 'First Name',a.lastname AS 'Last Name' to a.lastname AS 'Last Name',a.firstname AS 'First Name'. Note that I use CONCAT_WS(' ', a.address1, a.address2) to combine the two address fields into one field for the purpose of the download.
Above the SQL Query I need to have the following header directives and then call the WordPress database. This code needs to be just before the SQL Query:

<?php


header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename={$fileName}");
header("Expires: 0");
header("Pragma: public");

$fh = @fopen( 'php://output', 'w' );

global $wpdb; //call the WordPress Database

?>

Now I run the Query and output the result. I put this code after the SQL Query:

<?php

$SQLresults= $wpdb->get_results($downloadSQL,ARRAY_A);//Run the SQL Query

$headerDisplayed = false;
foreach ( $SQLresults as $results )
{
$results = array('Date' => $classDate) + $results;
// Add a header row if it hasn't been added yet
if ( !$headerDisplayed ) {
// Use the keys from $results as the titles
fputcsv($fh, array_keys($results ));
$headerDisplayed = true;
}
// Put the data into the stream
fputcsv($fh, $results );
}
// Close the file
fclose($fh);
// Make sure nothing else is sent, our file is done
exit;

?>

The complete ‘Download.php‘ file can be placed in the WordPress current template directory. Then, a new page needs to be created (‘/download-page’ in the example above) specifying the ‘Download.php’ as the template.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.