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: <code>EXPLAIN</code> 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] ) );
}Code language: PHP (php)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