MySQL/PHP character encoding errors (e.g. æ,ø,å) – import, connection, table encoding and html meta header

Today I’ve got four hints that usually solves the character encoding errors I encounter with MySQL and PHP (usually triggered by the special Danish vowels æ,ø,å):

  • PhpMyAdmin import: use the official import function rather than executing crude sql inserts – because then you can specify the character encoding instead of using the default one which is NOT UTF-8.
  • Set encoding for PHP-MySQL connection: Here is an example:
    $conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
    mysql_set_charset('utf8',$conn);

    Link to php.net reference.

    If you use the (much better) PDO db access layer, then you can just add an argument to the connection call:
    $conn = new PDO('mysql:host=' . $hostname . ';dbname=' . $dbname, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

  • Make sure that the MySQL database and tables are UTF-8 encoded: Use the following SQL-commands:
    ALTER DATABASE db_name
    CHARACTER SET utf8
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci
    DEFAULT COLLATE utf8_general_ci;

    ALTER TABLE tbl_name
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci;

  • (x)HTML meta header character encoding: add this in the head section of the HTML code:
    <meta http-equiv="Content-type" value="text/html; charset=UTF-8" />

If you have got a question or more tips then please post it below, then I will update my post 🙂

4 thoughts on “MySQL/PHP character encoding errors (e.g. æ,ø,å) – import, connection, table encoding and html meta header

  1. Pingback: URL

  2. Hi,
    Its great tutorial.
    i have implemented it on my apps & it works. : )
    well I have 2 questions:
    1: What if i convert, my whole Data Base to UTF-8 ? Is it become slower or what?
    2: what the difference between:

    or

    note: 2nd meta wont work for me.

    regards
    Saleha A.Latif

  3. Hi Saleha and thanks for you reply.

    UTF-8 uses more bits per character than ASCII, but the difference in performance is most likely neglectable and many programming languages uses UTF-8 anyway. I always encode all my database content as UTF-8 – in my opinion, the biggest error risk is to get encoding errors which can ruin the user experience.

    I do not understand your question 2 – maybe you need to upload it as an image if it contains special characters?

    note: what HTML version are you using? If you use HTML5 then you must write

    < meta charset="utf-8" >

Leave a Reply

Your email address will not be published. Required fields are marked *