How to extract a UTF-8 string (In Arabic) from a MySQL DB and echo to screen using PHP

Question

Hi,

I have a MySQL db, i've set collation = utf8_unicode_ci.

I'm trying to fetch the value through PHP but i'm getting "???" instead of the actual string.

I have read about this subject and tried using mb_convert_encoding but it didn't work, what am I missing?

Can someone please post a code snippet that actually pulls a value from a DB and echos the string to the screen?

Thanks,

Answer 1

I have a MySQL db, i've set collation = utf8_unicode_ci.

I'm trying to fetch the value through PHP but i'm getting "???" instead of the actual string.

Character sets are how characters are encoded.

Collations are how characters are sorted.

These are different things. Chances are that your tables or columns have the right collation, but the wrong character set. The Internationalization section of the MySQL manual has a great deal of information on how to set things up correctly.

Can someone please post a code snippet that actually pulls a value from a DB and echos the string to the screen?

Let's demonstrate how to use utf8 as a character set, and the utf8 "general case insensitive" collation. I'm using PDO in this example, but the same general idea should work with mysqli as well. I wouldn't advise using the old mysql extension.

// Let's tell MySQL we're going to be working with utf8 data.
// http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html
$db->query("SET NAMES 'utf8'");
// Create a table with our proper charset and collation.
// If we needed to, we could specify the charset and collation with
// each column. 
// http://dev.mysql.com/doc/refman/5.1/en/charset-column.html
// We could also set the defaults at the database level.
// http://dev.mysql.com/doc/refman/5.1/en/charset-database.html
$db->query('
    CREATE TABLE foo(
        bar TEXT
    ) 
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE utf8_general_ci
    ENGINE=InnoDB
');

// I don't know Arabic, so I'll type this in English.  It should
// work fine in Arabic, as long as the string is encoded as utf8.    
$sth = $db->prepare("INSERT INTO foo(bar) VALUES(?)");
$sth->execute(array("Hello, world!"));

$sth = $db->query("SELECT bar FROM foo LIMIT 1");
$row = $sth->fetch(PDO::FETCH_NUM);
echo $row[0]; // Will echo "Hello, world!", or whatever you inserted.

---

@tomp's comment below is correct. Make sure to emit a proper character set with your content type header. For example:

header('Content-type: text/html; charset=utf-8'); // Note the dash!