Special characters Search Problem
Moderator: General Moderators
Special characters Search Problem
Hi,
I have some problem with special characters in my search module. I am using PHP 5.2.0-8 and MySQL 5.0.32.
If i give normal characters as search words its giving the records which matches the words with case insensitive. But if i give special character like(à é è í ï ó ò ú ü ç) in search word its giving the records with case sensitive.
for example:
i am having the restaurant name like “Els Àngels restaurant”.
- If I give search word as “Àngels”, its giving the results.
- If I give search word as “àngels”, no results.
This is the problem.
And i have created FULL TEXT INDEX for restaurant name field.
I am struggling with this, Please anyone help me.Thanks in Advance..
Thanks,
HarPaddy
I have some problem with special characters in my search module. I am using PHP 5.2.0-8 and MySQL 5.0.32.
If i give normal characters as search words its giving the records which matches the words with case insensitive. But if i give special character like(à é è í ï ó ò ú ü ç) in search word its giving the records with case sensitive.
for example:
i am having the restaurant name like “Els Àngels restaurant”.
- If I give search word as “Àngels”, its giving the results.
- If I give search word as “àngels”, no results.
This is the problem.
And i have created FULL TEXT INDEX for restaurant name field.
I am struggling with this, Please anyone help me.Thanks in Advance..
Thanks,
HarPaddy
Re: Special characters Search Problem
Have a look at this:
http://dev.mysql.com/doc/refman/5.0/en/ ... tion_lower
You also need to use strtolower() to return the search string in lower case as well.
http://dev.mysql.com/doc/refman/5.0/en/ ... tion_lower
Code: Select all
SELECT * FROM foo WHERE LOWER(bar) = 'searchString'
Re: Special characters Search Problem
If lowering the search string as well as the database column does not work (or only when no foreign chars involved), it's most likely a matter of incompatible encodings.
If your situation allows it, it's probably easiest to use utf-8 encoding everywhere. Without properly defined encodings, there's NO telling how characters such as à are actually stored.
What do the following queries result in?
If your situation allows it, it's probably easiest to use utf-8 encoding everywhere. Without properly defined encodings, there's NO telling how characters such as à are actually stored.
What do the following queries result in?
Code: Select all
$s1 = chr(0xE0); // à in iso-8859-1 encoding
$s2 = chr(0xC3).chr(0xA0); // à in utf-8 encoding
$query1 = "SELECT * FROM restaurants WHERE name LIKE '%".$s1."ngels%'";
$query2 = "SELECT * FROM restaurants WHERE name LIKE '%".$s2."ngels%'";
Re: Special characters Search Problem
Hi Appollo,
Thanks for giving response.
I have tried the queries on my server with PHP, but its not giving any records for this search word.
Actually i am having one record with this word, but the special character is in upper case.(À)
So, how could i get the this record if i search with ('à' and also 'a'). I am running the query with PHP mysql_query function.
Thanks,
HarPaddy
Thanks for giving response.
I have tried the queries on my server with PHP, but its not giving any records for this search word.
Actually i am having one record with this word, but the special character is in upper case.(À)
So, how could i get the this record if i search with ('à' and also 'a'). I am running the query with PHP mysql_query function.
Thanks,
HarPaddy
Re: Special characters Search Problem
Hi Appollo,
I have tried the query with converting the column name(restaurant_name) with lower case but no result. Its giving the records when if the search keyword is in Uppercase special character.
Query : SELECT restaurant_id FROM restaurant WHERE LOWER(restaurant_name) LIKE '%àngels%'
I am having two records with the search word.Please have a look in the attachment.
And the special characters are saving as like that in DB and the CHARSET we are using in this is 'UTF-8'.
Thanks,
Paddy
I have tried the query with converting the column name(restaurant_name) with lower case but no result. Its giving the records when if the search keyword is in Uppercase special character.
Query : SELECT restaurant_id FROM restaurant WHERE LOWER(restaurant_name) LIKE '%àngels%'
I am having two records with the search word.Please have a look in the attachment.
And the special characters are saving as like that in DB and the CHARSET we are using in this is 'UTF-8'.
Thanks,
Paddy
- Attachments
-
- specialChar_search.jpg (151.84 KiB) Viewed 1462 times
Last edited by HarPaddy on Sat Mar 21, 2009 5:16 am, edited 1 time in total.
Re: Special characters Search Problem
Set the collation of the DB (or table) to *_ci (i.e. case insensitive, * - use an appropriate collation for your case)
http://dev.mysql.com/doc/refman/5.0/en/ ... abase.html
http://dev.mysql.com/doc/refman/5.0/en/ ... abase.html
You may use ALTER TABLE to do it.COLLATE [=] collation_name
There are 10 types of people in this world, those who understand binary and those who don't
Re: Special characters Search Problem
Thanks for response.
The database and tables are having already the collation as case insensitive only (utf8_general_ci).
I think thats why the results with case insensitive are displaying when i do search with normal characters , for special characters only the problem.
Thanks,
Paddy
The database and tables are having already the collation as case insensitive only (utf8_general_ci).
I think thats why the results with case insensitive are displaying when i do search with normal characters , for special characters only the problem.
Thanks,
Paddy
Re: Special characters Search Problem
Ah ok, in that case, can you try these:HarPaddy wrote:Actually i am having one record with this word, but the special character is in upper case.(À)
Code: Select all
$s1 = chr(0xC0); // À in iso-8859-1 encoding
$s2 = chr(0xC3).chr(0x80); // À in utf-8 encoding
$query1 = "SELECT * FROM restaurants WHERE name LIKE '%".$s1."ngels%'";
$query2 = "SELECT * FROM restaurants WHERE name LIKE '%".$s2."ngels%'";Re: Special characters Search Problem
I have executed the queries ,
for 1 st one
$s1 = chr(0xC0); No records are showing.
$s2 = chr(0xC3).chr(0x80); // À in utf-8 encoding
One record is showing if i execute this query
And our requirement is it has to show all the records if i do search with lower case special character(à).
Without encoding of special charter also we are getting the records for upper case À. So, whats the use of encoding the special character.
And we are taking the search word dynamically which enter in text field, we don't where the special character comes so how we can give encoding of that character.
Thanks,
HarPaddy
for 1 st one
$s1 = chr(0xC0); No records are showing.
$s2 = chr(0xC3).chr(0x80); // À in utf-8 encoding
One record is showing if i execute this query
And our requirement is it has to show all the records if i do search with lower case special character(à).
Without encoding of special charter also we are getting the records for upper case À. So, whats the use of encoding the special character.
And we are taking the search word dynamically which enter in text field, we don't where the special character comes so how we can give encoding of that character.
Thanks,
HarPaddy
Re: Special characters Search Problem
Ok, here's what to do:
1. Make sure the restaurant_name column has utf8_general_ci collation.
2. Make sure the search text has utf-8 encoding. You can probably do this with:(depending on your webserver's language setting, but this most likely works)
The encoding of the input text (the search term) is determined by the encoding you specify in the html that displays the search form. I'd always recommend to make this utf-8. If you're not sure (because you're in some complex CMS or environment you don't fully control or whatever), you can see by inspecting the html source when visiting the form. But the above converse-if-necessary code probably works for you.
1. Make sure the restaurant_name column has utf8_general_ci collation.
2. Make sure the search text has utf-8 encoding. You can probably do this with:
Code: Select all
if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);The encoding of the input text (the search term) is determined by the encoding you specify in the html that displays the search form. I'd always recommend to make this utf-8. If you're not sure (because you're in some complex CMS or environment you don't fully control or whatever), you can see by inspecting the html source when visiting the form. But the above converse-if-necessary code probably works for you.
Re: Special characters Search Problem
Thanks for reply.
The solution is not meet our requirement. I need the result with case insensitive.
I am getting the results with this
if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);
for case sensitive only.. I need it as case insensitive results.
The restaurant_name column has already having 'utf8_general_ci' collation.
for example :
If i give search word as 'Restaurant Aligué' its showing the results with consisting of this word, bur if i give search word like 'Restaurant AliguÉ' it has to show all the records which meets this , but no records. This is my requirement , Can u please give a solution for this?
The solution is not meet our requirement. I need the result with case insensitive.
I am getting the results with this
if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);
for case sensitive only.. I need it as case insensitive results.
The restaurant_name column has already having 'utf8_general_ci' collation.
for example :
If i give search word as 'Restaurant Aligué' its showing the results with consisting of this word, bur if i give search word like 'Restaurant AliguÉ' it has to show all the records which meets this , but no records. This is my requirement , Can u please give a solution for this?
Re: Special characters Search Problem
What MySQL version are you using? The _ci means case insensitive, so any search should already be case insensitive.
What happens when you try this:
What happens when you try this:
Code: Select all
if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);
$s = '%'.$s.'%';
$query = "SELECT * FROM restaurants WHERE LOWER(restaurant_name) LIKE LOWER('$s')";Re: Special characters Search Problem
Thanks for reply
We are using Mysql version 5.0.32-Debian_7etch8-log for our server.
I have executed the
1. if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);
2. $s = '%'.$s.'%';
3. $query = "SELECT * FROM restaurants WHERE LOWER(restaurant_name) LIKE LOWER('$s')";
Same is coming. No results for 'àngels'
We are using Mysql version 5.0.32-Debian_7etch8-log for our server.
I have executed the
1. if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);
2. $s = '%'.$s.'%';
3. $query = "SELECT * FROM restaurants WHERE LOWER(restaurant_name) LIKE LOWER('$s')";
Same is coming. No results for 'àngels'
Re: Special characters Search Problem
And what happens when you use $s = 'Àngels' ? (with capital À)HarPaddy wrote:Same is coming. No results for 'àngels'
Does it find a result then, even with the LOWER(..) functions in the query?
Re: Special characters Search Problem
yes we are getting the results for the query with LOWER()
query: " SELECT DISTINCT(R.restaurant_id) FROM restaurant R WHERE LOWER(R.restaurant_name) LIKE LOWER('%Àngels%') AND R.status = 1 "
query: " SELECT DISTINCT(R.restaurant_id) FROM restaurant R WHERE LOWER(R.restaurant_name) LIKE LOWER('%Àngels%') AND R.status = 1 "