Where condition returns empty resultset for utf-8 chars

Databases
1

Hi All,
I have created mysql table which has a text field, varchar and both are in utf-8 format. But When I do select query with where condition it returns empty result set even it has a value.
I make the field as utf-8 format, table as utf-8 format. What should I do for this problem

2

are you using the mysql client or a GUI for this or are you using a front end like PHP and perhaps introducing the error there?

3

I’m using php it doesn’t show any error message. It just return an empty string.

4

Most likely, one of the values(either value in db, or val in the sql query) is not really utf8, or it may have some additional characters that you can’t visually detect.

Take a close look at the values. This should help make the difference more obvious.


select HEX(name), HEX('$name'), 
       LENGTH(name), LENGTH('$name'), 
       CHAR_LENGTH(name), CHAR_LENGTH('$name'), 
       name, '$name'
from sometable
5

are you using phpmyadmin or php in a script? if the latter, what error checking? Do you have access to the mysql client to check if your query should return rows?

can you show some sample rows in the table, which of those rows should be returned by the query and the query itself?

6

If you are using php to connect to MySQL, right after you establish a connection to the MySQL server but before you select a database if using the mysql_* extension:

mysql_set_charset('utf8',$connection_identifier);

if using the mysqli_* extension:

mysqli_set_charset ($connection_identifier,'utf8')

That will tell PHP what character set you wish to use for the connection to the MySQL server.