How to search with strings like 'Curaçao' or others words with accents in MySql

[cfmtravel]

I'm having some troubles with a simple SELECT query in PHP + MySQL.

The problems start when I search for string like 'Curaçao' or 'Saint Barthélemy' to mention a few. The problems are the letters 'ç' and 'é'.

Fields, tables, database, connection, charset on the page that display the result are set utf8_unicode_ci.

I tried all the solution that I found but I cannot make it work and few hours of fruitless attempts I have to post a new question.

I also try straight in the phpMyAdmin to use this query SELECT nationFlag FROM tbnations WHERE nation LIKE _utf8 "Curaçao" COLLATE utf8_unicode_ci and the result of the query is empty

Any suggestion?

[edited by: engine at 11:14 am (utc) on Sep 3, 2014]

[omoutop]

OK some points here:
- you are not in stackoverflow ;)
- database, table, colums: all must have same collation. Verify that they do.
- Try running a simple query first in your phpMyAdmn:
SELECT nationFlag FROM tbnations WHERE nation="Curaçao" and see if you get results (assuming that there is a record "Curaçao" in your table
- After that, construct any query you want.


To find what collation your database, table and colums are:

For your whole database:
SHOW VARIABLES LIKE 'character_set_database'

for all database tables:
SHOW TABLE STATUS (will show all your tables and various info)

for single table (checkig its colums):
SHOW FULL COLUMNS FROM table_name