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 🙂

install and setup LAMP on Ubuntu for development

LAMP stack: Linux, Apache, MySQL, PHPMyAdmin

LAMP: Linux Operating System, Apache Webserver, PHP programming language, MySQL database, PHPMyAdmin database web interface

First install Apache2, PHP5, Mysql, Phpmyadmin in that order.

WEBROOT
########################
Then lets setup ~/public_html as webroot rather than /var/www/ (because it requires root privileges to modify there which is a pain!):

1. Copy the existing website configuration (virtual directory):
sudo cp /etc/apache2/sites-available/default /etc/apache2/sites-available/mysite

2. Modify the following of mysite:
gksudo gedit /etc/apache2/sites-available/mysite
/var/www -> /home/USERNAME/public_html

3. enable mysite and disable default
sudo a2ensite
sudo a2dissite default
sudo /etc/init.d/apache2 reload

/home/USERNAME/public_html can now be accessed as webroot in webbrowser as http://localhost

PHP
########################
Setup PHP at this file: /etc/php5/apache2/php.ini
short_open_tag = Off (only use <php ?>? rather than non-standard <? ?> (inverted <)
error_reporting = E_ALL | E_STRICT (see all errors including the E_STRICT ones)
display_errors = On (obvious…)
display_startup_errors = On
html_errors = On

Remember to run this to update the apache configuration:
sudo /etc/init.d/apache2 restart

MYSQL
########################
user = root
Remember to use innoDB as storage engine for mySQL databases as it enables transactions and much more.

PHPMYADMIN
########################
access phpmyadmin at localhost/phpmyadmin (use “root” as user)

External guide with lots of more stuff including security: https://help.ubuntu.com/10.04/serverguide/C/httpd.html