MySQL Collate Latin1_General_CI_AI Problem

Question

I have the following select statement where I'm trying to search for a players first and last name that may contain accents in it.

$sql = "
    SELECT * FROM player
    WHERE player.player_first LIKE '%$first%'
    AND player.player_last LIKE '%$last%'
    ";

I adding COLLATE Latin1_General_CI_AI after before both of the LIKE clauses, but that didn't work and returned errors.

Any help would be appreciated.

Unknown collation: 'Latin1_General_CI_AI' – Jako Mar 19 '11 at 09:20 — Jako, Mar 19 '11 at 09:20

score 1 · Answer 1 · answered Mar 19 '11 at 09:27

1

According to MySQL doc the collation Latin1_General_CI_AI does not exists.

Try latin1_general_ci instead.

answered Mar 19 '11 at 09:27

Maxime Pacary

I thought AI did exist though because the AI meant it was accent insensitive, that's what I'm looking for. – Jako Mar 19 '11 at 16:08
After some searches, the only AI + CI collation in MySQL seems to be `utf8_general_ci`. Check http://dev.mysql.com/doc/refman/5.0/en/charset-collation-implementations.html and http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql. But I guess that your DB charset must be UTF8 then. – Maxime Pacary Mar 19 '11 at 20:11

1 Answers1