1

I have a Mysql database (in utf8_general_ci) and have to query columns containing special characters. The problem is: queries containing special characters do not retrieve any results – but queries containing the regular version of those characters do.

For instance, if I want to query the word “été”:

  • SELECT … WHERE column LIKE % été % will not work.
  • SELECT … WHERE column LIKE % ete % will work!

This problem must be due to my encoding (both queries work when done directly in phpMyAdmin).

I use PDO and followed the instruction here (http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) and, when connecting to the database, do exec(“SET CHARACTER SET utf8”) AND exec(“SET collation_connection = ‘utf8_general_ci’”). What bothers me is that, excepted for this search query, the website seems to work perfectly with utf8 both for the display and the database.

I thought that I could operate a transliterate_to_ascii on the strings containing special chars. I’m however afraid that this option is suboptimal, especially for non european languages. Thank you very much for your help!

Raphael
  • 1,709
  • 2
  • 17
  • 27
  • @Pekka for the query : SELECT * FROM translations WHERE translations.content LIKE :search_terms + $query->bindParam(':search_terms', $search_terms) – Raphael Nov 03 '11 at 11:02
  • 1
    Show the full code you are using to do the query – Pekka Nov 03 '11 at 11:02
  • @Pekka it's way too long (250 lines), because there are much conditions in it – Raphael Nov 03 '11 at 11:05
  • This is a fairly complex issue and can have many reasons. For example, it could be that the search terms are not UTF-8 encoded - I would make that sure first. – Pekka Nov 03 '11 at 11:07
  • @Pekka yes I think you're right, I will re-check that! Danke :) – Raphael Nov 03 '11 at 11:09
  • @Raphael You can uses websites such as pastebin.com to paste long blocks of code – Ben Swinburne Nov 03 '11 at 12:29
  • @BenSwinburne, you can easily paste 250 lines of code on SO. – Johan Nov 03 '11 at 12:51
  • @Johan, indeed you can, but as the OP seemed concerned I thought I'd suggest another option – Ben Swinburne Nov 03 '11 at 13:32
  • @BenSwinburne, problem is that code on other sites can disappear, links get lost and it's a pest to have to navigate away from SO + you cannot edit that code. – Johan Nov 03 '11 at 13:40

1 Answers1

0

Please try running SET NAMES utf8; on mysql right after the connect

Edson Medina
  • 9,862
  • 3
  • 40
  • 51
  • Ok the problem was that, in order to run the query, I applied strtolower on the search terms and converted the content of the mysql columns with LOWER(). This seems to truncate the special characters and explains why the query works only with the regular chars. – Raphael Nov 03 '11 at 13:22
  • 1
    You need to change the collation of the fields – Edson Medina Nov 15 '11 at 21:04