Мытарства новичка (PHP, UTF8, mysql)

Попалась мне недавно задачка на решение которой я угрохал около дня.

Суть задачи:

Есть сайт (php+mysql), с формой для поиска, содержащий не латинские символы. Для упрощения понимания, скажем, что сайт на русском языке.

Страницы сайта и таблица, в которой хранятся данные, в UTF8.

Необходимо сделать поиск не учитывая регистр. На сайте же, поиск происходил с учетом регистра.

Итак, начинаем разбираться.

В PHP коде никаких подозрительных операций с поисковой переменной не производится.

Кодировка страницы UTF8 и передача поискового параметра не изменяется на пути между браузером и сервером – проверено Chrome Developer Tools.

SELECT * FROM `table` WHERE `field` like '% $search %'

Ничего криминального не нашлось, смотрим дальше.

Попробуем добавить принудительное значение COLLATION utf8_unicode_ci

SELECT * FROM `table` WHERE `field` COLLATE utf8_unicode_ci like '% $search %' utf8_unicode_ci

Получаем ошибку

#1253 - COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1'

Хмм…. Странно…

Смотрим кодировку клиента базы данных в PHP. И, вот оно, вместо UTF8 видим latin1.

Уже хорошо, устанавливаем принудительно в UTF8. Совсем перестает работать поиск.

Смотрим, а что же хранится в базе? А в базе у нас данные с двойным перекодированием. Т.е. исходные данные в UTF8 были сохранены, как latin1, а затем используются, как UTF8. Не хорошо…

Остается – исправить данные в самой базе и принудительно установить в PHP верную кодировку.

Вот пример набора команд в mysql, которая позволит извлечь информацию из базы в правильном виде

CONVERT(CAST(CONVERT(`bad_field` USING latin1) AS BINARY) USING utf8)

Полезные ссылки по теме

stackoverflow.com
htmlpurifier.org
www.bluebox.net