Convert database data to UTF-8

[eluser]Swedie[/eluser]
Well sadly none of above worked. So I wrote a PHP function to loop through all of the tables and convert the data when needed using PHP's mb_convert and mb_encode functions.

I think it worked

Here's the code in case anyone wants it. You can true or false to convert data or just the tables (and columns). I haven't added collate, as it self-adjust to the correct one (atleast for me).

Code:

function fixEncoding($convertData = TRUE, $convertTableAndColumns = TRUE) {
    $table_encoding = "utf8";
    $start_on = "";
    $end_after = "";
    $sql_tables = mysql_query("SHOW TABLES");
    while($table = mysql_fetch_assoc($sql_tables)) {
        if($start_on AND substr($table['Tables_in_smc_www2'],0,strlen($start_on)) < $start_on) continue;
        if($end_after AND substr($table['Tables_in_smc_www2'],0,strlen($end_after)) > $end_after) continue;
        if($convertTableAndColumns) mysql_query("ALTER TABLE ".$table['Tables_in_smc_www2']." CONVERT TO CHARACTER SET ".$table_encoding.";");
        if($convertData) {
            ob_start();
            print "Table: ".$table['Tables_in_smc_www2']." - ";
            $sql_total = mysql_query("SELECT * FROM ".$table['Tables_in_smc_www2']."");
            $total_records = mysql_num_rows($sql_total);
            print " Total records ".$total_records." - ";
            $per_run = 1000;
            $runs = @ceil($total_records/$per_run);
            if($runs <= 0) $runs = 1;
            print "Runs: ".$runs." - Completed: ";
            $done = 0;
            for($i=0; $i < $runs; $i++) {
                if($i > 0) $done += $per_run;
                if($per_run > $total_records) $per_run = $total_records;
                $sql_run = mysql_query("SELECT * FROM ".$table['Tables_in_smc_www2']." LIMIT ".$done.", ".$per_run."");
                $run_count = mysql_num_rows($sql_run);
                if($per_run > $run_count) $per_run = $run_count;
                print $done."-".($done+$per_run).", ";
                if($run_count > 0) {
                    while($row = mysql_fetch_assoc($sql_run)) {
                        unset($data);
                        unset($first);
                        unset($values);
                        foreach($row as $field => $value) {
                            if(!$first) {
                                $first['field'] = $field;
                                $first['id'] = $value;
                            }
                            if(!is_numeric($value)) {
                                $value = stripslashes(html_entity_decode($value));
                                $value = mb_convert_encoding($value, 'ISO-8859-1', mb_detect_encoding($value, 'UTF-8, ISO-8859-1', true)); //from something to iso, then over to utf-8 db tables
                                $data[$field] = $value;
                            }
                        }
                        if(is_array($data)) {
                            foreach($data as $key => $value) {
                                if($key != $first['field']) {
                                    $values .= "$key = '".addslashes($value)."', ";
                                }
                            }
                            $query = "UPDATE ".$table['Tables_in_smc_www2']." SET ".substr($values, 0, -2)." WHERE ".$first['field']." = ".$first['id']."";
                            mysql_query($query) or die("Table: ".$table['Tables_in_smc_www2'].", Fieldname: ".$first['id'].": ".mysql_error());
                        }
                    }
                }
            }
        }
        print "Done!<br>";
        sleep(1);
        ob_flush();
    }
}