Description:
I can't get MySQL to sort Turkish characters properly. The default character set is set to Latin5 in my.ini and I create a table as follows:
CREATE TABLE t (c VARCHAR(20) CHARACTER SET latin5 COLLATE latin5_turkish_ci);
and insert some characters I get from an HTML form into the table using PHP. The form is very simple:
form.html
=======================================================
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-9" />
<meta http-equiv='content-language' content='TR'>
</head>
<body>
<p>
<form action="process.php" method="post">
<input type="text" name="thebox">
<input type="submit" value="Submit">
</form>
</body>
</html>
=======================================================
And inside the process.php, I insert the entered characters into the table:
process.php
=======================================================
<?
$dbcnx = mysql_connect('localhost', <username>, <password>)
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('mydb') or die('Could not select database');
$variablename=$_POST['thebox'];
$sql = "insert into t values ('$variablename')";
// write to table
if (mysql_query($sql, $dbcnx)) {
echo "Success writing to table";
} else {
echo "Failed to write to table";
exit();
}
// read from table
$query = 'SELECT * FROM t ORDER BY c';
$result = mysql_query($query) or die('select failed ' . mysql_error());
// Display the results
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)){
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_result($result);
// Closing connection
mysql_close($dbcnx);
?>
=======================================================
So when I submit the following three characters one by one using the form (form.html):
a
ğ
b
I get the final sorted result as:
ğ
a
b
which should be:
a
b
ğ
The table looks like this after the three inserts:
mysql> select * from t;
+--------+
| c |
+--------+
| a |
| ğ |
| b |
+--------+
3 rows in set (0.00 sec)
mysql>
I've tried all the combinations of latin5 charset+collation on table/column/database scope to no avail. (I've also tried everything with utf8 charset/collation as well, and got the exact same results).
I has definitely has something to do with the character 'ğ' showing as ğ in the table when I display it's content in a command window. Not sure why 'a' and 'b' doesn't show up like numbers or vice versa??
Thanks!
How to repeat:
- Install PHP 5.0.3RC2, MySQL 4.1.7 and Apache HTTP server 2.0.52 into a Windows XP machine.
- Place the form code into a file called form.html and php code that accesses the MySQL db that's indicated above into a file called process.php. Set the default charset of MySQL to latin5 in my.ini and restart the server process.
- Create the table t in MySQL command line as follows:
CREATE TABLE t (c VARCHAR(20) CHARACTER SET latin5 COLLATE latin5_turkish_ci);
- point your browser to form.html and repeat the following step for letters 'a', 'ğ' and 'b':
-- Enter the character and hit Submit button, then return back to form.html
Everytime you click 'Submit', process.php inserts the data into the table and retrieves everything back and displays the results in order.
