Page 1 of 4

Special characters Search Problem

Posted: Fri Mar 20, 2009 5:57 am
by HarPaddy
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

Re: Special characters Search Problem

Posted: Fri Mar 20, 2009 6:41 am
by Benjamin
Have a look at this:

http://dev.mysql.com/doc/refman/5.0/en/ ... tion_lower

Code: Select all

 
SELECT * FROM foo WHERE LOWER(bar) = 'searchString'
 
You also need to use strtolower() to return the search string in lower case as well.

Re: Special characters Search Problem

Posted: Fri Mar 20, 2009 6:56 am
by Apollo
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?

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

Posted: Sat Mar 21, 2009 2:57 am
by HarPaddy
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

Re: Special characters Search Problem

Posted: Sat Mar 21, 2009 4:42 am
by HarPaddy
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

Re: Special characters Search Problem

Posted: Sat Mar 21, 2009 4:54 am
by VladSun
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
COLLATE [=] collation_name
You may use ALTER TABLE to do it.

Re: Special characters Search Problem

Posted: Sat Mar 21, 2009 5:32 am
by HarPaddy
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

Re: Special characters Search Problem

Posted: Sat Mar 21, 2009 2:24 pm
by Apollo
HarPaddy wrote:Actually i am having one record with this word, but the special character is in upper case.(À)
Ah ok, in that case, can you try these:

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

Posted: Mon Mar 23, 2009 12:46 am
by HarPaddy
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

Re: Special characters Search Problem

Posted: Mon Mar 23, 2009 8:18 am
by Apollo
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:

Code: Select all

if ($s != iconv('utf-8', 'utf-8', $s)) $s = iconv('windows-1252', 'utf-8', $s);
(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.

Re: Special characters Search Problem

Posted: Tue Mar 24, 2009 1:04 am
by HarPaddy
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?

Re: Special characters Search Problem

Posted: Tue Mar 24, 2009 3:23 am
by Apollo
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:

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

Posted: Tue Mar 24, 2009 5:31 am
by HarPaddy
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'

Re: Special characters Search Problem

Posted: Tue Mar 24, 2009 5:15 pm
by Apollo
HarPaddy wrote:Same is coming. No results for 'àngels'
And what happens when you use $s = 'Àngels' ? (with capital À)

Does it find a result then, even with the LOWER(..) functions in the query?

Re: Special characters Search Problem

Posted: Wed Mar 25, 2009 12:01 am
by HarPaddy
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 "