Solving PHP MySQL UTF-8 issues

Heres a list of actions you should do in order to get PHP + MySQL working with UTF-8: 1. Database: CREATE DATABASE db_name CHARACTER SET utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT COLLATE utf8_general_ci ; or if the database was already created: ALTER DATABASE db_name CHARACTER SET utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci […]

Heres a list of actions you should do in order to get PHP + MySQL working with UTF-8:

1. Database:

CREATE DATABASE db_name
 CHARACTER SET utf8
 DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci
 DEFAULT COLLATE utf8_general_ci
 ;

or if the database was already created:

ALTER DATABASE db_name
 CHARACTER SET utf8
 DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci
 DEFAULT COLLATE utf8_general_ci
 ;

CREATE TABLE table_name(
 ...
 )
 DEFAULT CHARACTER SET utf8   
 COLLATE utf8_general_ci;

Or if the tables are already created:

ALTER TABLE tbl_name
 DEFAULT CHARACTER SET utf8
 COLLATE utf8_general_ci
 ;

2. Enable this line in php.ini:

extension=php_mbstring.dll

and configure the following in the same file:

mbstring.language = Neutral
mbstring.internal_encoding = UTF-8
mbstring.encoding_translation = On
mbstring.http_input = auto
mbstring.http_output = UTF-8
mbstring.detect_order = auto
mbstring.substitute_character = none
default_charset = UTF-8

3. Use the following php functions instead:

mail()                -> mb_send_mail()
strlen()              -> mb_strlen()   
strpos()              -> mb_strpos()
strrpos()             -> mb_strrpos()
substr()              -> mb_substr()
strtolower()          -> mb_strtolower()
strtoupper()          -> mb_strtoupper()
substr_count()        -> mb_substr_count()
ereg()                -> mb_ereg()
eregi()               -> mb_eregi()
ereg_replace()        -> mb_ereg_replace()
eregi_replace()       -> mb_eregi_replace()   
split()               -> mb_split()
htmlentities($var)    -> htmlentities($var, ENT_QUOTES, 'UTF-8')

4. Use headers and meta tags like:

header('Content-type: text/html; charset=UTF-8') ;

<meta http-equiv="Content-type" value="text/html; charset=UTF-8" />

5. Before any insert / update in the database you should perform the following:

mysql_query("SET NAMES 'utf8'");
Previous:
Next:

Tagged with:

Mary Lou (Manoela Ilic) is a freelance web designer and developer with a passion for interaction design. She studied Cognitive Science and Computational Logic and has a weakness for the smell of freshly ground peppercorns.

View all contributions by

Website: http://tympanus.net/

Related Articles

Feedback 3

Comments are closed.
  1. 1

    Great list, but step 5 isn’t quite right.

    You should never use SET NAMES in a query with the mysql extension. If you do, mysql_real_escape_string() won’t be notified of the change, and i’ll keep escaping your data for latin1, which can open security holes.

    Use mysql_set_charset(‘utf8′); instead. Same thing, much safer ;)

Comments are closed.