trim_all_tables_columns_fields

<?php
/**
 * This file will iterate through all your tables and TRIM each field.
 * (That means NO trailing or leading spaces)
 * Ideally it would be used if there were thousands of rows and columns
 * trailing or leading spaces that need to get rid of.
 * Oh, and always backup your entire database first.
 */
 
$link = mysqli_connect(...); 
$rows_affected = trim_all_tables($link);
echo $rows_affected.' fields trimmed total.'."\n";
 
 
 
 
function get_tables(mysqli $link)
{
  $tableList = array();
  $res = mysqli_query($link,"SHOW TABLES");
  while($cRow = mysqli_fetch_array($res))
  {
    $tableList[] = $cRow[0];
  }
  return $tableList;
}
 
function trim_all_columns_in_table(mysqli $link, $table){
    //first grab all columns 
    $sql1 = "SELECT * FROM $table LIMIT 1";
    $result = mysqli_query($sql1);
    $row = mysql_fetch_assoc($result);
    if (empty($row)){ return false; }
    
    //now iterate through each column and trim each one.
    $total_affected_rows = 0;
    foreach($row as $column => $unused){
        $result2 = mysqli_query("UPDATE $table SET $column = TRIM($column)");
        $total_affected_rows += mysqli_affected_rows($link);
    }
    return $total_affected_rows;
}
 
function trim_all_tables(mysqli $link){
   $all_tables = get_tables();
   $total_affected_rows = 0;
   foreach($all_tables as $table){
        $total_affected_rows += (int)trim_all_columns_in_table($link, $table);
   }
   return $total_affected_rows;
}