Trim All MySQL Tables

trim_all_mysql_tables.php

<?php

// Config (change these if you want)
define( 'DISABLE_TIME_LIMIT', true );
define( 'DISABLE_OUTPUT_BUFFERING', true );
define( 'CUSTOM_ERROR_HANDLING', false );
define( 'DB_HOST', 'localhost' );
define( 'DB_PORT', 3306 );
define( 'DB_USER', 'root' );
define( 'DB_PASS', '' );
define( 'DB_NAME', '' );

// Don't allow PHP to stop after global timeout setting
if ( DISABLE_TIME_LIMIT ) set_time_limit( 0 );

if ( DISABLE_OUTPUT_BUFFERING ) {
    // Remove output buffering
    while ( ob_get_level() ) ob_end_clean();
    // Output buffers directly
    ob_implicit_flush( true );
}

if ( CUSTOM_ERROR_HANDLING ) {
    // Custom error handling
    error_reporting( 0 );
    function handleError( $errno, $errmsg, $filename, $linenum, $vars ) {
       exit(
           '[' . $errno . '] Line #' . $linenum .
           PHP_EOL . $errmsg . PHP_EOL . ' in ' . $filename
       );
    }
    $old_error_handler = set_error_handler( 'handleError' );
}

function showUsage() {
    print '@todo Show usage';
}

function p( $s ) {
    print "\n" . $s;
}

// Only allow this script to be run via the command line
if ( strtoupper(PHP_SAPI) !== 'CLI' ) {
    print 'This script can only be run via the command line.';
    showUsage();
    exit( 1 );
}

// Start a new database connection
$db = new mysqli( DB_HOST, DB_USER, DB_PASS, DB_NAME );

// Make sure we've logged in & selected a database properly
if ( $db->connect_errno ) {
    exit( 'could not connect to database server.' );
}

$res = $db->query( 'SHOW TABLES' );

if ( !$res ) {
    exit( 'could not query database' );
}

$tables = array();
foreach ( $res as $row ) {
    // Get the first index (associative array), the table name
    $tables[] = array(
        'name' => array_shift( $row ),
        'columns' => array(),
        'status' => 'Waiting'
    );
}
$res->close();

foreach ( $tables as $k => $t ) {
    
    $res = $db->query('
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = "' . $t['name'] . '"'
    );
    
    foreach ( $res as $row ) {
        $tables[$k]['columns'][] = array_pop( $row );
    }
    
    @$res->close();
    
}

print_r($tables);

print "\nCleaning......";

foreach ( $tables as $t ) {
    
    foreach ( $t['columns'] as $c ) {
        
        $res = $db->query('
            UPDATE ' . $t['name'] . '
            SET ' . $c . ' = LTRIM(RTRIM(' . $c . '))'
        );
        
        if ( !$res || !empty($db->error) ) {
            print "\nERROR " . $db->error;
        }
        
    }
    
}

@$res->close();
@$db->close();