As mentioned in the very first article of this series one of the major problems with a custom database tables is the fact that they are not handled by existing import & export handlers. This article aims to address that problem – but it should be noted that currently there is no completely satisfactory solution.

Let’s consider two scenarios:

1. The custom table references a native WordPress table
2. The custom table is completely independent of the native tables

The ‘worst case’ scenario is the first. Take the example of a custom table keeping logs of user activity. It references user ID, object ID, and object type – all of which refer to data stored in native WordPress tables. Now imagine someone wanting import all the data from their WordPress site into a second one. It’s entirely possible that when importing a post, for example, WordPress has to allocate it a new ID to it, since there may already exist a post with that ID in the second site.

In this situation it would be necessary to keep track of such changes and update the IDs referenced in our table. This is not itself that difficult. Unfortunately, the WordPress Importer plug-in which handles importing data from other WordPress site’s lacks the necessary hooks to make this possible. As suggested in this comment, one potential work-around is to store the data in post meta too. Unfortunately this results in duplicate data, and flies in the face of database normalization – generally not a good idea. Finally, it is only really workable in a minority of use cases.

The second case avoids this complexity but still requires custom import and export handlers. It's this case we'll be demonstrating in the next two articles. However, for consistency with the rest of the series we'll be sticking with the activity logs table even though it is an example of case (1).

Deciding the Format

First we need to decide the format that our export file is to take. The best format depends on the nature (or the ‘structure’) of the data and how it is to be used. In my opinion XML is generally better since it handles one-to-many relationships. However, sometimes if the data is tabular, then CSV can be preferable – in particular for it ease of integration with spreadsheet applications. In this example we’ll be using XML.

The Mark-Up

The next step is to create an admin page to allow users to export the data in the log table. We’ll create a class that will add a page underneath the ‘Tools’ menu item. This page will contain little more than a button, prompting the user to download the export file. The class will also add a handler to listen for the form submission and trigger the file download.

First let's take a look at the structure of the class, before filling in the details of its methods.

class WPTuts_Log_Export_Admin_Page{

    /**
     * The page hook suffix
     */
    static $hook_suffix='';

    static function load(){
        add_action('admin_menu', array(__CLASS__,'add_submenu'));
        add_action('admin_init', array(__CLASS__,'maybe_download'));
    }

    static function add_submenu(){}

    static function maybe_download(){}

    static function display(){}
}
WPTuts_Log_Export_Admin_Page::load();

The WPTuts_Log_Export_Admin_Page::load() initialises the class and hooks callbacks to the appropriate actions:

• add_submenu – The method responsible for adding the page under the Tools menu.
• maybe_download – This method will listen to check if a download request has been submitted. This will also check permissions and nonces.

The export listener needs to be called early on and before any headers are sent, as we’ll be setting these ourselves. We could hook it onto init, but since we will only allow the export file to be downloaded in the admin, admin_init is more appropriate here.

Adding a page to the menu is very simple. To add a page under Tools we just need to call add_management_page().

static function add_submenu(){
    self::$hook_suffix = add_management_page( __('Export Logs','wptuts-log'), __('Export Logs','wptuts-log'), 'manage_options', 'wptuts-export', array(__CLASS__,'display') );
}

The $hook_suffix here is the suffix used for various screen-specific hooks, discussed here. We don’t use it here – but if you do, its good idea to store its value in a variable, rather than hard-coding it.

In the above we’ve set the method display() to be the callback for our page, we define this next:

static function display(){

    echo '<div class="wrap">';
        screen_icon();
        echo '<h2>' . __( 'Export Activity Logs', 'wptuts-log' ) . '</h2>';
        ?>

        <form id="wptuts-export-log-form" method="post" action="">
            <p>
                <label><?php _e( 'Click to export the activity logs','wptuts-log' ); ?></label>
                <input type="hidden" name="action" value="export-logs" />
            </p>
            <?php wp_nonce_field('wptuts-export-logs','_wplnonce') ;?>
            <?php submit_button( __('Download Activity Logs','wptuts-log'), 'button' ); ?>
        </form>

    <?php
}

Finally, we want to listen for when the above form is submitted and trigger the export file download.

static function maybe_download(){
     /* Listen for form submission */
    if( empty($_POST['action']) || 'export-logs' !== $_POST['action'] )
        return;

    /* Check permissions and nonces */
    if( !current_user_can('manage_options') )
        wp_die('');

    check_admin_referer( 'wptuts-export-logs','_wplnonce');

    /* Trigger download */
    wptuts_export_logs();
}

All that remains is to create the function wptuts_export_logs() which creates and returns our .xml file.

Creating the Export File

The first thing we want our function to is to retrieve the logs. If there are any, we’ll need to set the appropriate headers and print them in XML format. Since we want the user to download an XML file, we’ll be setting the the Content-Type to text/xml and Content-Description to File Transfer. We’ll also generate a suitable name for the download file. Finally, we’ll include some comments – these are entirely optional, but can be helpful in instructing the user on what to do with the downloaded file.

Since in the previous part of this series we created an API for our table, our export handler does not need touch the database directly – nor do we need to sanitize the $args array as this is handled by the wptuts_get_logs().

function wptuts_export_logs( $args = array() ) {

    /* Query logs */
    $logs = wptuts_get_logs($args);

    /* If there are no logs - abort */
    if( !$logs )
        return false;

    /* Create a file name */
    $sitename = sanitize_key( get_bloginfo( 'name' ) );
    if ( ! empty($sitename) ) $sitename .= '.';
    $filename = $sitename . 'wptuts-logs.' . date( 'Y-m-d' ) . '.xml';

    /* Print header */
    header( 'Content-Description: File Transfer' );
    header( 'Content-Disposition: attachment; filename=' . $filename );
    header( 'Content-Type: text/xml; charset=' . get_option( 'blog_charset' ), true );

    /* Print comments */
    echo "<!-- This is a export of the wptuts log table -->\n";
    echo "<!-- (Demonstration purposes only) -->\n";
    echo "<!--  (Optional) Included import steps here... -->\n";

    /* Print the logs */
}

You’ll notice that we’ve passed the actual query array as an argument for the wptuts_export_logs() function. We could have hard-coded this, but it makes sense not to. Although the intention here is just to export everything in the table, passing the query as an argument allows us to later add the option of exporting logs in a certain time frame, or for a particular user.

When creating the XML file we need to ensure that no values printed between the tags contain the characters &, < or >. To ensure this, for IDs we sanitize the data with absint, and the object types and activities with sanitize_key (since we expect these to contain only lowercase alpha-numerics and underscores and hyphens).

/* Print logs to file */
echo '<logs>';
foreach ( $logs as $log ) { ?>
    <item>
        <log_id><?php echo absint($log->log_id); ?></log_id>
        <activity_date><?php echo mysql2date( 'Y-m-d H:i:s', $log->activity_date, false ); ?></activity_date>
        <user_id><?php echo absint($log->user_id); ?></user_id>
        <object_id><?php echo absint($log->object_id); ?></object_id>
        <object_type><?php echo sanitize_key($log->object_type); ?></object_type>
        <activity><?php echo sanitize_key($log->activity); ?></activity>
    </item>
<?php }
echo '</logs>';

More generally you can sanitize the values being printed by wrapping them inside CDATA tag using the following function:

/**
 * Wraps the passed string in a XML CDATA tag.
 *
 * @param string $string String to wrap in a XML CDATA tag.
 * @return string
 */
function wptuts_wrap_cdata( $string ) {
    if ( seems_utf8( $string ) == false )
        $string = utf8_encode( $string );

    return '<![CDATA[' . str_replace( ']]>', ']]]]><![CDATA[>', $string ) . ']]>';
}

Finally we exit() to prevent any further processing:

    /* Finished - now exit */   
    exit();

Navigating to our export page, clicking ‘Download Activity Logs’ should prompt a download of an XML file.

Summary

In this tutorial we’ve looked at exporting data from our custom table. Unfortunately, where the data references native WordPress tables, this is at best problematic. The method outlined above is only useful for cases where the data does not do this. The example used (our activity logs) obviously doesn’t fall into this category, but is used simply for consistency with the rest of this series.

When the data does reference native tables it’s obviously necessary to import it along with the native tables and in doing so, keeping track of any changes in IDs that occur during the import. Currently that’s not possible with the existing import and export handlers – and so the only workable option is to create your own. In simpler cases where the custom data only references a single post type, it’s possible to design your import and export handlers to handle that post type as well as your custom data and inform the user not to use the native exporter for that post type.

In the next part of this series we’ll be creating a simple import handler for the exported .xml file.