icodemonkey

Home

Drupal collation matching

After receiving errors like: * user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like' query: SELECT t.tid, t.name FROM term_data t WHERE t.vid = 14 AND LOWER(t.name) LIKE LOWER('%dfgdfg%') LIMIT 0, 10 in /---path---/html/includes/database.mysql.inc on line 172. * The installed version of at least one of your modules or themes is no longer supported. Upgrading or disabling is strongly recommended! Please see the project homepage for more details. See the available updates page for more information.\ A search location this page, and this script which handles tables and columns: http://www.urbannatives.net/localtreechild/drupal/drupal_code/script_con...

<?php
# Do not change anything below this ( :-) <- Rewwrite Editors note.)

$sql = 'SHOW TABLES';
if ( !( $result = db_query( $sql ) ) ) {
       echo '<span style="color: red;">Get SHOW TABLE - SQL Error: <br>' . "</span>\n";
    }

   
while ( $tables = db_fetch_array($result) ) {
    echo $tables[0];
# Loop through all tables in this database
   $table = $tables[key($tables)];
  
   if ( !( $result2 = db_query("ALTER TABLE %s COLLATE utf8_general_ci", $table) ) ) {
        echo '<span style="color: red;">UTF SET - SQL Error: <br>' . "</span>\n";
     
          break;
           }
  
   print "$table changed to UTF-8 successfully.<br>\n";

   # Now loop through all the fields within this table
   if ( !($result2 = db_query("SHOW COLUMNS FROM %s",$table) ) ) {
          echo '<span style="color: red;">Get Table Columns Query - SQL Error: <br>' . "</span>\n";
     
          break;
           }

   while ( $column = db_fetch_array( $result2 ) )
   {
      $field_name = $column['Field'];
      $field_type = $column['Type'];
     
      # Change text based fields
      $skipped_field_types = array('char', 'text', 'enum', 'set');
     
      foreach ( $skipped_field_types as $type )
      {        
         if ( strpos($field_type, $type) !== false )
         {
            $sql4 = "ALTER TABLE $table CHANGE `$field_name` `$field_name` $field_type CHARACTER SET utf8 COLLATE utf8_bin";
            $result4 = db_query($sql4);

            echo "---- $field_name changed to UTF-8 successfully.<br>\n";
         }
      }
   }
   echo "<hr>\n";
}
?>