PDA

View Full Version : special characters lost from dumpfile through mysql_query



jonas-e
11-07-2007, 04:04 PM
I have a script on my local machine which makes a dumpfile of the local database, then uploads it to the web-server by ftp. After that, a script on the web-server opens (fopen) and reads (fread) the file, splits it into individual queries and passes them through mysql_query() - thus synchronizing the web-server with the local. During that process, special characters - e.g. the Scandinavian æ, ø and å - get lost and become weird signs.

The remote dump-file is located here: http://skred-svalbard.no/dump.sql. If you open that file in a web-browser, the special characters will indeed look weird, example:

INSERT IGNORE INTO `content` VALUES (121,5,'2007-02-01','Safety training in Ny Ã…lesund', (...)
(supposed to be "Ny Ålesund"). However, if you save the file to your drive and open it with a text-editor, you will see that the characters are still intact - it's just the browser which doesn't render them.

Apparently, the characters are not passed correctly from php to the mysql server.

Any suggestions?

jonas-e
11-07-2007, 04:07 PM
Here is the code which reads and passes the dump file to the mysql server:

$file = "dump.sql";
$fh = fopen($file, 'r');
$text = fread($fh, filesize($file));
fclose($fh);

// insert seperator before each command
$sep=" |SEP| ";
$text=ereg_replace("DROP"," $sep DROP",$text);
$text=ereg_replace("INSERT"," $sep INSERT",$text);
$text=ereg_replace("CREATE"," $sep CREATE",$text);
$text=ereg_replace("--"," $sep --",$text);
$text = eregi_replace(
'/\*([^\\[]+)\*/',
'', $text); // remove what is btw /* and */

// create array from seperator and run
// queries one-by-one
$sqls=explode(' |SEP| ',$text);
echo "<div class='section'><h2 style='text-align:center;'>SQL commands executed</h2>";
foreach ($sqls as $sql) {
echo "<div style='border:solid 1px #888888;margin:0 auto 8px auto;width:80%;'>
<p style='text-align:left;'>".$sql."</p>";
if ($sql!=" ") {
if (!mysql_query($sql)) {
echo '<p style=\'color:red;\'>ERROR: '.mysql_error().'</p>';
} else {echo "<p style='color:red;'>SUCCES!</p>";
}
}
echo "</div>";
}
echo "</div>";

jonas-e
11-08-2007, 10:43 AM
Please, someone out there must have an idea?

I have read the documentation of fread() and fopen() without finding out.

I tried to add a "b" to the fopen argument:
$fh = fopen($file, 'rb');
- it doesn't help.

I suppose it has something to do with how php reads the data - any alternative functions perhaps?

jonas-e
11-09-2007, 11:00 AM
- through this thread:
http://www.sitepoint.com/forums/showthread.php?t=513231

Simply adding this:
$text="SET NAMES 'utf8';".$text;
- just before the:
$sqls=explode(" $sep ",$text);
- does the trick!