Collation Issue?

[friedemann_bach]

Dear phpfreaks,

 

I am working on a database that contains Arabic translations of Greek words. Having to operate with diacritics and vowels, I set all MySQL collations to utf8_unicode_ci. This works very good for Greek, as users can enter any diacritics combination (sometimes up to three on a single letter). It does not work at all for Arabic vowels, though.

 

A request that includes no vowels, like

 

SELECT * FROM vocabulary WHERE arabic_lexeme LIKE 'سبب'

 

should return at least the same results as as a request with vowels, like

 

SELECT * FROM vocabulary WHERE arabic_lexeme LIKE 'سَبَبٌ'

 

but it doesn't. I have two entries that match 'سَبَبٌ', and ... LIKE 'سبب' does not match them.

 

I imagined that it would work like French accents or German umlauts. Maybe I am wrong here?

 

To exclude any errors that are based on my php coding, I have tested the same queries in phpMyAdmin.

I also tried with different collations, cp1256_general_ci and utf8_general_ci.

I am still not able to search for Arabic words with "insensitive vowels".

 

I would appreciate any help or comments on this problem.

Thank you very much for your attention!

 

[fenway]

You'll need to convert those strings into accent-free values when you search -- mysql only provides case-insenstivity.

[friedemann_bach]

I've found out by chance that both

 

SELECT * FROM vocabulary WHERE arabic_lexeme = 'سبب'

 

and

 

SELECT * FROM vocabulary WHERE arabic_lexeme = 'سَبَبٌ'

 

return as well the results with vowels as without vowels.

 

Thought that it would work similar to Greek accents (with LIKE) ... but obviously it does not.

 

Thanks for your help!