Categories
Dev Stuff WordPress

Quick Tip: Export WordPress SQL output via WP-CLI

If for some reason you can’t run wp db query, but need to export SQL output to a CSV or other file, then have a look at this small WP-CLI command I whipped up that should allow this:

/**
 * Runs a SQL query against the site database.
 *
 * ## OPTIONS
 *
 * 
 * : SQL Query to run.
 *
 * [--format=]
 * : Render output in a particular format.
 * ---
 * default: table
 * options:
 * - table
 * - csv
 * - json
 * - count
 * - yaml
 * ---
 *
 * [--dry-run=]
 * : Performa a dry run
 *
 * @subcommand sql
 */
public function sql( $args, $assoc_args ) {
     global $wpdb;
 
     $sql     = $args[0];
     $format  = WP_CLI\Utils\get_flag_value( $assoc_args, 'format', 'table' );
     $dry_run = WP_CLI\Utils\get_flag_value( $assoc_args, 'dry-run', 'true' );
 
     // Just some precautions.
     if ( preg_match( '/[update|delete|drop|insert|create|alter|rename|truncate|replace]/i', $sql ) ) {
         WP_CLI::error( 'Please do not modify the database with this command.' );
     }
 
     if ( 'false' !== $dry_run ) {
         WP_CLI::log( WP_CLI::colorize( '%gDRY-RUN%n: EXPLAIN of the query is below: (https://mariadb.com/kb/en/explain/)' ) );
         $sql = 'EXPLAIN ' . $sql;
     }
 
     // Fetch results from database.
     $results = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB
 
     // Output data.
     WP_CLI\Utils\format_items( $format, $results, array_keys( $results[0] ) );
}

I’d add an example here, but I don’t have any right now that I can share 😐 I’ll try to find one later (don’t hold your breath on me remembering to do that)

Leave a Reply