Viewed   458 times

I am trying to filter Turkish names from MySql database through AJAX POST, the English letter words are listing all okay however if I send Ö (which is letter O with dots) the results come for both O and Ö not only Ö

Also what I noticed is the AJAX post is send Ö as %C3%96, anybody can help?



Please bare my somewhat lengthy response.
Let's start with your second question. %C3%96 means that the bytes 0xC3 and 0x96 are transmitted. Those two bytes encode the character Ö in utf-8.
From this (and that your query yields the described results) I assume that you're using utf-8 all the way through.

The lexicographical order of characters of a given charset is determined by the collation used.
That's more or less an ordered list of characters. E.g. A,B,C,D,.... meaning A<B<C....
But these lists my contain multiple characters in the same "location", e.g.
[A,Ä],B,C,D.... meaning that A==Ä->true

___ excursion, not immediately relevant to your question ____
Let's take a look at the "name" of the character Ö, it's LATIN CAPITAL LETTER O WITH DIAERESIS.
So, the base character is O, it just has some decoration(s).
Some systems/libraries allow you to specify the "granularity"/level/strength of the comparison, see e.g. Collator::setStrength of the php-intl extension.

// utf8 characters
define('SMALL_O_WITH_DIAERESIS', chr(0xC3) . chr(0xB6));
define('CAP_O_WITH_DIAERESIS', chr(0xC3) . chr(0x96));

$coll = collator_create( 'utf-8' );
foreach( array('PRIMARY', 'SECONDARY', 'TERTIARY') as $strength) {
    echo $strength, "rn";
    $coll->setStrength( constant('Collator::'.$strength) );
    echo '  o ~ ö = ', $coll->compare('o', SMALL_O_WITH_DIAERESIS), "rn";
    echo '  Ö ~ ö = ', $coll->compare(CAP_O_WITH_DIAERESIS, SMALL_O_WITH_DIAERESIS), "rn";


  o ~ ö = 0
  Ö ~ ö = 0
  o ~ ö = -1
  Ö ~ ö = 0
  o ~ ö = -1
  Ö ~ ö = 1

On the primary level all the involved characters (o,O,ö,Ö) are just some irrelevant variations of the character O, so all are regarded as equal.
On the secondary level the additional "feature" WITH DIAERESIS is taken into consideration and on the third level also whether it is a small or a capital letter.
But ...MySQL doesn't exactly work that way, sorry again ;-)
___ end of excursion ____

In MySQL there are collation tables that specify the order. When you select a charset you also implictly select the default collation for that charset, unless you explictly specify one. In your case the implictly selected collation is probably utf8_general_ci and it treats ö==o.
This applies to both the table defintion and charset/collation of the connection (the latter being almost irrelevant in your case).
utf8_turkish_ci on the other hand treats ö!=o. That's probably the collation you want.

When you have a table defintion like

  x varchar(32)

the default collation for utf8 is chosen -> general_ci -> o=ö
You can specifiy the default collation for the table when defining it

  x varchar(32)
CHARACTER SET utf8 COLLATE utf8_turkish_ci

Since you already have a table plus data, you can change the collation of the table ...but if you do it on the table level you have to use ALTER TABLE ... CONVERT (in case you use MODIFY, the column keeps its "original" collation).


That should pretty much take care of your problem.

As a side note there is (as mentioned) a collation assigned to your connection as well. Selecting a charset means selecting a collation. I use mainly PDO when (directly) connecting to MySQL and my default connection code looks like this

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(

note the charset=utf8; no collation, so again general_ci is assigned to the connection. And that's why

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(

$smallodiaresis_utf8 = chr(0xC3) . chr(0xB6);
foreach( $pdo->query("SELECT 'o'='$smallodiaresis_utf8'") as $row ) {
    echo $row[0];

prints 1 meaning o==ö. The string literals used in the statement are treated as utf8/utf8_general_ci.

I could either specify the collation for the string literal explicitly in the statement

SELECT 'o' COLLATE utf8_turkish_ci ='ö'

(only setting it for one of the two literals/operands; for why and how this works see Collation of Expressions)
or I can set the connection collation via

$pdo->exec("SET collation_connection='utf8_turkish_ci'");

both result in

foreach( $pdo->query("SELECT 'o'[...]='$smallodiaresis_utf8'") as $row ) {
    echo $row[0];

printing 0.

edit: and to complicate things even a bit further:
The charset utf8 can't represent all possible characters. There's an even broader character set utf8mb4.

Thursday, November 17, 2022

If you have made sure that both the tables, and the output encoding are UTF-8, almost the only thing left is the connection encoding.

The reason for the change in behaviour when updating servers could be a change of the default connection encoding:


However, I can't see any changes in the default encoding between versions, so if those were brand-new installs, I can't see that happening.

Anyway, what happens if you run this from within your PHP query and output the results. Any differences to the command line output?

 SHOW VARIABLES LIKE 'character_set%';
 SHOW VARIABLES LIKE 'collation%'; 
Friday, November 18, 2022

Character set issues are often really tricky to figure out. Basically, you need to make sure that all of the following are true:

  • The DB connection is using UTF-8
  • The DB tables are using UTF-8
  • The individual columns in the DB tables are using UTF-8
  • The data is actually stored properly in the UTF-8 encoding inside the database (often not the case if you've imported from bad sources, or changed table or column collations)
  • The web page is requesting UTF-8
  • Apache is serving UTF-8

Here's a good tutorial on dealing with that list, from start to finish:

It sounds like your problem is specifically that you've got double-encoded (or triple-encoded) characters, probably from changing character sets or importing already-encoded data with the wrong charset. There's a whole section on fixing that in the above tutorial.

Wednesday, September 28, 2022

I think the encoding you are looking for is Windows code page 1252 (Western European). It is not the same as ISO-8859-1 (or 8859-15 for that matter); the characters in the range 0xA0-0xFF match 8859-1, but cp1252 adds an assortment of extra characters in the range 0x80-0x9F where ISO-8859-1 assigns little-used control codes.

The confusion comes about because when you serve a page as text/html;charset=iso-8859-1, for historical reasons, browsers actually use cp1252 (and will hence submit forms in cp1252 too).

iconv('cp1252', 'utf-8', "x80 and x95")
-> "xe2x82xac and xe2x80xa2"
Saturday, October 15, 2022
// Create an empty array for the encoded resultset
$rows = array();

// Loop over the db resultset and put encoded values into $rows
while($row = mysql_fetch_assoc($result)) {
  $rows[] = array_map('utf8_encode', $row);

// Output $rows
echo json_encode($rows);
Monday, September 5, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :