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 […]

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'");

Tagged with:

Manoela Ilic

Manoela is the main tinkerer at Codrops. With a background in coding and passion for all things design, she creates web experiments and keeps frontend professionals informed about the latest trends.

Stay up to date with the latest web design and development news and relevant updates from Codrops.

Feedback 3

Comments are closed.
  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 😉