CODEWITHSUNDEEP
phpmysql
Avinash Aher
Avinash Aher

Reputation: 77

mysql not execute the special chars in where clause

SELECT c.ci_num, 
      CASE 
           WHEN c.ci_homonym=1 
           THEN concat(ci_name,', ',name_fr) 
           ELSE ci_name 
      END as ci_name 
FROM city as c join fr_department as fr on c.ci_sr=fr.code  
WHERE ci_name LIKE 'bé%'  
LIMIT 5

I am selecting the city_name and city_num,but when there is special character(bé) string it not execute the query but when i execute this query to phpmyadmin this query show result.Why not execute by mysql_query($query1),why ? and how to execute this, thanks.

Upvotes: 2

Views: 999

Answers (2)

VolkerK
VolkerK

Reputation: 96149

How is this é character encoded? (e.g. utf-8)
Set the charset used by the mysql connection accordingly via mysql_set_charset(). This will also affect the collation used to compare characters. Each charset has a default collation i.e. a default way of comparing/ordering characters. For most charset there is more than one collation available, e.g. for ignoring upper/lower case.

To get a grasp of charsets (and collations) related to mySQL I suggest reading

--- edit: example ---

Let's take for example this script (file)

<?php
$startsWith = 'bé';

$mysql = mysql_connect('localhost', 'localonly', 'localonly') or die(mysql_error());
mysql_select_db('test', $mysql) or die(mysql_error());

echo 'version: ', PHP_VERSION, "\n";
echo 'os: ', PHP_OS, "\n";
echo 'mysql_client_encoding: ', mysql_client_encoding($mysql), "\n";
for($i=0; $i<strlen($startsWith); $i++) {
    printf('%02X ', ord($startsWith[$i]));
}

My editor saves files utf-8 encoded by default and apparently (didn't know that before) my version of php ships with an mysql connector that uses utf-8 as the default connection charset.
Therefore the output is

version: 5.4.1
os: WINNT
mysql_client_encoding: utf8
62 C3 A9

You see how the to characters bé are encoded as the three bytes 0x62 0xC3 0xA9 ; the last to bytes are the utf-8 represenation of é. So we have client-encoding=utf-8 and the input string is utf-8 as well -> ok
Now, if I change the script file's encoding from utf-8 to iso-8859-1 the result is

version: 5.4.1
os: WINNT
mysql_client_encoding: utf8
62 E9

Now the é is encoded by only one byte; it's the iso-8859-1 representation of that character. If this byte sequence was sent to the mysql server (which is expecting to get a utf-8 sequence from the client) the server wouldn't know what to do with it - because it's an invalid utf-8 sequence. Even worse if by chance the iso-8859 sequence forms an also valid utf-8 sequence the server would do something completly wrong, because it would interpret the sequence as something different than supposed.

Upvotes: 3

Peter Ilfrich
Peter Ilfrich

Reputation: 3816

If the "bé" is hard-coded, it might have something to do with the file encoding. Especially with special characters the file encoding of the database should always be the same as your PHP script.

Upvotes: 0

Related Questions