Arabic and English records with LIKE

saidbakr

Hello,
I have a databse table has both Arabic and English records. When I use the following select statement with LIKE to make search, a strange and wiered result obtained,

SELECT * FROM item WHERE  item_description LIKE '%no%'

As you see I searched for the English phrase 'no', so I supposed to have only English records like, nokia, anoma, piano, etc. The strange thing, that I have a result of an Arabic record has no any latin characters of 'no'.
What's the origin of this problem and how can I solve it?
I'm using PHP to code the application.

MarkR

You really need to understand what encoding you're using in your application and how that related to English and Arabic.

If you're using an 8bit character set which contains English and Arabic characters, but MySQL thinks you're using something else, there will be your problem.

MySQL "LIKE" will respect the current MySQL collation. If you're storing some other encoding in a column with latin1 encoding, then MySQL thinks you're storing something totally different from what you are.

So maybe you're hitting latin1 characters n-tilde and o-umlaut which looks like "no" to "LIKE", but those are actually Arabic characters.

You MUST set MySQL's encoding (and hence collation) correctly in order to have LIKE and ORDER BY behave in the manner you expect.

Mark

saidbakr

Hello,
From your reply, I may understand that, I have to change some of MySQL server settings, but what may I do if this option is impossible, shared host for example,?

However,In inserting data, I use a PHP web application's page encoded with iso-8859-6 charset. Do you think that using windows-1256 charset will be a solution? since, the OS of the MySQL server host PC is Windows XP. or I have to do some modifications to the SQL query string that I mentioned before!

MarkR

saidbakr wrote:
Hello,
From your reply, I may understand that, I have to change some of MySQL server settings, but what may I do if this option is impossible, shared host for example,?

No, not at all. You may need to drop and recreate all your tables though, if they were created with the wrong encoding.

Migrating the data could be tricky - you might need to edit the dump file.

However,In inserting data, I use a PHP web application's page encoded with iso-8859-6 charset.

Have you notified MySQL that you're using this charset? Are your tables in the charset?

Personally I'd go for utf8 as it's likely to work better and has a wider range of characters.

But you MUST have your tables in the right encoding AND the connection encoding set correctly ("set names utf8" normally does it).

Do you think that using windows-1256 charset will be a solution?

Only if you're sure that MySQL and the browser fully support that charset. utf8 sounds better.

Remember that "Like" can only be expected to work if the database is really in the encoding that it thinks it is.

Otherwise, your database is really full of junk which just happens to come out right.

Mark

saidbakr

Ok,
You said " No, not at all. You may need to drop and recreate all your tables though, if they were created with the wrong encoding. "
How can I tell MySQL about the character code that I want when I create the table?