Querying names with accented characters

[NiallFH]

I have a search script which essentially just searches a list of soccer player's names and returns a list of those that match the search criteria.

$get_players = mysql_query("
SELECT
 CONCAT(PlayerLastName, ', ', PlayerFirstName) AS name,
 PlayerID AS id,
 DATE_FORMAT(PlayerDOB, '%Y') AS dob
FROM 
 tplss_players
WHERE
 CONCAT(PlayerFirstName, ' ', PlayerLastName) LIKE '%$result%' OR
 PlayerFirstName LIKE '%$result%' OR
 PlayerLastName LIKE '%$result%'
ORDER BY 
 PlayerLastName, PlayerFirstName ASC
", $connection);

The problem is, if a player has an accented character in their name and the person searching isn't aware of it, it wont display them in the results.

 

I.e. Nadir Çiftçi.  If someone searches for "Nadir Ciftci", nothing will come up.

 

Is there a way to configure your query so that accented characters are treated as normal characters? i.e. Ç is C?

 

The fields in question (PlayerFirstName and PlayerLastName) are set to use Collation of "utf8_bin".

 

Thanks in advance for any help you can offer,

 

Niall

[Irate]

You could save them in your database as name without accents and when a user searches for them, you output the name with accented characters using possibly a switch statement and str_replace.

Though that'd imply that the correctly spelled name wouldn't be found either.

[NiallFH]

You could save them in your database as name without accents and when a user searches for them, you output the name with accented characters using possibly a switch statement and str_replace.

Though that'd imply that the correctly spelled name wouldn't be found either.

 

There'd be too many examples of clashing names for that to be work.  Seán and Sean, for example.

[requinix]

Don't use utf8_bin as the collation. Binary collations mean strings have to match exactly. Try utf8_general_ci.

 

Unicode Character Sets

[NiallFH]

Don't use utf8_bin as the collation. Binary collations mean strings have to match exactly. Try utf8_general_ci.

 

Unicode Character Sets

 

Thank you so much, that has worked to a degree for my searches.  I can now find Çiftçi, for example, simply by searching Ciftci, but some of the accented consonants still don't match up, such as ć or Ł.  In fact, it seems to be mainly consonants with accents that don't work.

 

Also - it still doesn't sort the names in the correct order when displaying multiple results.  It still orders Ç for Çiftçi after Z.  

 

Any ideas?

Edited July 31, 2013 by NiallFH

[requinix]

Try a different collation.

[jazzman1]

Don't change the utf8_general_ci collation in your database to another. It works just fine. Tested few times.  

Few things you need to check.

1. Make sure that your database collation and the tables which contain accented characters in their name are under utf8_general_ci.

2. Check that everything is speaking utf-8. HTML forms, pages or http headers in apache (if you're using it).

3. When you established the connection to the database and set a database table(s), before send the actual searching query string to DB, make sure that PHP is using a utf-8 connection to MySQL.  
You could use a mysql_set_charset('utf8', $link_identifier) (personally i prefer) or a SET NAMES utf8 mysql function.

In this way, you will be sure that all data you really send is utf-8!

 

In your first post, I don't see wher you've been set up a character encoding in php to DB.

Edited July 31, 2013 by jazzman1