up vote 0 down vote favorite
share [g+] share [fb]

After reading the many tips here and elsewhere, I think I have finally made my GET query, PHP and MYSQL all talk utf8. However, I cannot get a simple query to work where the string I'm matching contains non-ascii characters. My (simplified) code is:

$link = mysql_connect("h", "u", "p") or die(mysql_error());
mysql_select_db("db", $link) or die(mysql_error($link));
mysql_set_charset("utf8", $link); //connection charset
$name = mysql_real_escape_string($_GET['first'], $link);
$query = sprintf("SELECT name, date FROM guestbook WHERE name='%s'", $name);
$result = mysql_query($query, $link) or die(mysql_error()); 
if (mysql_num_rows($result) == 0) {
...
}

If 'first' is ascii, then the query works, if it contains accented characters it does not. When I print out the db (in an table generated by php) it looks just fine, as does viewing in it phpadmin.

What am I doing wrong? Do I need to tell the '=' operator to use utf8? Why?

Thanks

abo

link|improve this question
25% accept rate
Just because it looks fine, doesn't mean it's fine. What's the collation of the column name? – Shef Aug 21 '11 at 19:56
utf8_general_ci – abonae Aug 21 '11 at 20:04
feedback

2 Answers

up vote 0 down vote

Have you tried encoding the name as UTF-8 before the query?

$query = sprintf("SELECT name, date FROM guestbook WHERE name='%s'", utf8_encode($name));

If that doesn't do the trick, try utf8_decode. It sounds weird but I had weird cases where it did the trick.

link|improve this answer
No, that didn't help (either version) – abonae Aug 21 '11 at 20:10
feedback
up vote 0 down vote

What's the character set of the table itself ?

Also, try running the following query just after connection:

SET NAMES UTF8;
link|improve this answer
It's not necessary because he uses mysql_set_charset. – Andrej L Aug 21 '11 at 20:01
So far as I can tell, everything is utf8. (What's the best way to verify this? Is there a query I can run?). Also the mysql_set_charset should do the SET NAMES UTF8 step. – abonae Aug 21 '11 at 20:02
Check the output of show create table guestbook – arnaud576875 Aug 21 '11 at 20:04
CREATE TABLE guestbook` ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL DEFAULT '<noname>', nname varchar(20) NOT NULL DEFAULT '<noname>', comments text NOT NULL, date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=91 DEFAULT CHARSET=utf8 COMMENT='simple test table'` – abonae Aug 21 '11 at 20:06
Are you sure you are sending data as utf8 to the PHP script ? (e.g. did you added a Content-Type: text/html; charset=utf-8 header or the equivalent meta tag ?) – arnaud576875 Aug 21 '11 at 20:08
show 6 more comments
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.