Looking for software experts?
Need an expert advice on software development? Need consulting work done in time and at high standards? Tremend has the right solution for you.

We can provide expertise in:
  •    » high traffic and complex content website infrastructures using Java, PHP or .NET. More here ...
  •    » mobile applications for iPhone, Android or J2ME. More here ...

For an enquiry, send an email to contact [at] tremend [dot] ro.

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

Related posts

.

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.