PHP - Parsing an uft-8 file and inserting it to database gives messes up the special chars like é

Question

On a webserver, I have a php script that parses a .sql file (which is stored directly on the server), and executes the queries on a mysql database. I have a lot of french characters that doesn't insert well: é becomes é.

When I open the sql file with notepad++, I see that the encoding is "uft-8 without BOM".

My script looks like this:

$handle = fopen("test.sql", "r") or die("couldn't get handle");
if ($handle) 
{
    while (!feof($handle)) 
    {
        $buffer = fgets($handle, 4096);
        if (strlen ( $buffer ) < 3 ) // if we have a blank line
        {       
            mysql_query($query);
            $query = $buffer;
            sleep(0.5);
        } 
        else 
        {
            $query .= $buffer;
        }
    }
    mysql_query($query); // last insert
    fclose($handle);
}

When I open the database through phpmyadmin, I see that the special chars are already broken right after the execution of the script.

Answer 1

You may need to run 'SET NAMES UTF8' before you do the insert, because mysql is so hilariously flaky about character encoding. Yes, even if your entire database has already been set to use the UTF-8 character encoding and general-utf8-ci collation.

http://forums.mysql.com/read.php?103,46870,46870#msg-46870

Answer 2

Instead, you should use the mysql_set_charset function and not a SET NAMES query, as described at http://www.php.net/manual/en/function.mysql-set-charset.php

Even though your database is in UTF-8, and PHP deals in UTF-8, the connection set up by default is probably a Latin-1 connection, so MySQL will try to convert the data even though it shouldn't