Special characters Search Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Special characters Search Problem

Post 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
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Special characters Search Problem

Post 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.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Special characters Search Problem

Post 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%'";
 
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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
Attachments
specialChar_search.jpg
specialChar_search.jpg (151.84 KiB) Viewed 1463 times
Last edited by HarPaddy on Sat Mar 21, 2009 5:16 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Special characters Search Problem

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Special characters Search Problem

Post 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%'";
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Special characters Search Problem

Post 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.
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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?
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Special characters Search Problem

Post 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')";
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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'
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Special characters Search Problem

Post 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?
HarPaddy
Forum Commoner
Posts: 40
Joined: Tue Feb 03, 2009 12:54 am

Re: Special characters Search Problem

Post 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 "
Post Reply