MySql, PHP and UTF8

September 26th, 2006 by Iulian Cutui in PHP

Nowadays UTF8 is far from being just “trendy”, it’s the de facto standard for information representation. There are a lot of discussions on the “why locales are bad” theme, and I’m not going to argue with that.

Getting right to the point, how can you use UTF8 in your PHP/MySql web app? First things first: use PHP 5 and MySql 4.1 as minimum requirements. The reason is the same for both of these apps: native/decent UTF8 support.

For MySql, define your tables with UTF8 character set and collation:

CREATE TABLE table_name (

………………..

) CHARACTER SET utf8 COLLATE utf8_general_ci;

For PHP, set the UTF8 encoding before extracting your information from MySql

if (!$link = mysql_connect(‘mysql_host’, ‘mysql_user’, ‘mysql_password’)) {

echo ‘Could not connect to mysql’;
exit;

}

if (!mysql_select_db(‘mysql_dbname’, $link)) {

echo ‘Could not select database’;
exit;

}
$sql = “SET NAMES ‘utf8′”;
mysql_query($sql, $link);

//select your UTF8 data
$sql = “SELECT foo FROM table_name”;
$result = mysql_query($sql, $link);if (!$result) {

echo “DB Error, could not query the database\n”;
echo ‘MySQL Error: ‘ . mysql_error();
exit;

}?>

If you need to process your UTF8 strings use the multi-byte versions of the string functions (they have the same name but a “mb_” prepended, e.g. mb_substr) as they work at the character level rather than the byte level.

For omitting the string encoding parameter that can be passed to the multi-byte string functions is useful to set the default encoding to UTF8 by issuing:

mb_internal_encoding(“UTF-8”);

DZoneGoogle ReaderYahoo MessengerRedditEmailDelicious

You might also like

How to set the default charset to utf-8 for create table when using hibernate with java persistence annotations Yesterday I encountered a problem when trying to persist a String value into a MySQL column of type 'text'...
End to end UTF-8 encoding usage with MySql and Spring Setting up a solution to store, manage and display UTF-8 data using MySql was quite a challenge.There...
Spring+JPA with MySQL/Oracle configurations During development for a solution based on Spring+JPA+MySQL/Oracle we came across two settings to...
Serializable java objects in MySQL How to save a complex java object in a MySQL table If you want to save complex java objects to MySQL...
.

11 Responses

  1. Rafael Says:

    Great Post.

    Thanks.

  2. Sam Bazzi Says:

    Very useful solution.

    Thanks.

  3. beggi Says:

    very useful advice. I It saved me time

  4. beggi Says:

    very good advice. it saved me time

  5. Gunnar C Says:

    dude! you’re a saviour! I’ve been having major JSON/PHP/UTF8 encoding problems for days and that solved it all! :D

  6. zor5 Says:

    Instead of

    $sql = “SET NAMES ‘utf8′”;

    also is possible

    $sql = “SET CHARACTER SET utf8”;

  7. peterg22 Says:

    You’re a genius! I fixed my problem in 10 seconds :-)) Next time you’re in Woking UK I’ll buy you a beer!

  8. UTF-8 Mysql MSsql and UCS-2 « Sharing knowledge Says:

    […] http://blog.tremend.ro/2006/09/26/mysql-php-and-utf8/ […]

  9. Nenorocitul asta de MySQL Says:

    […] daca era sa mearga. Intr-un final, dupa lungi cautari pe Goagal, am ajuns si eu unde trebuia. Site-ul asta mi-a oferit solutia corecta si foarte simpla la problema. Se pare ca tot ce trebuia sa fac era sa […]

  10. Hrvoje Says:

    Great post, thank you!
    I’ve had struggles with croatian characters being shown on the BlackBerry… I spent hours, and hours… and then it came to my mind that maybe the problem is on the server/php side… and you DID help me!
    Thank you, and you have a beer from me next time you’re in Croatia!

  11. pip4so Says:

    Thank you for your solution!

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.