Order Arabic data by alphabet letters

Question

How can I order data grabbed from MySQL by Arabic alphabet ?

Note: I use utf8_general_ci as collation in MySQL, and the encoding is fine, I just want to know how to sort data by alphabet.

Answer 1

In order to sort Arabic text in mysql database using php, you have to make sure that:

1. MySQL charset: UTF-8 Unicode (utf8)
2. MySQL connection collation: utf8_general_ci
3. Your database and table collations set to: utf8_general_ci or utf8_unicode_ci

So when your create your table set charset to utf8 :

CREATE TABLE my_table (
id int(11) NOT NULL auto_increment,
name varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY  (id)
) DEFAULT CHARSET=utf8;

If you have already a table encoded in latin1, convert it like this :

ALTER TABLE `my_table` CONVERT TO CHARACTER SET utf8

See : Character set in MySQL.

Then, you need to add a line in your PHP when you setup the database connection :

mysql_query ('SET NAMES \'UTF8\'');  

Or :

mysql_set_charset ('UTF8'); // PHP 5.2.3 or greater.

Or if you're using PDO, you can pass it as a 4th parameter like this :

$db = new PDO ('mysql:host=localhost;dbname=tests', 'root', '', 
               array (PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\''));

Also don't forget to set the header of your pages to utf8 :

header ('Content-type:text/html; charset=utf-8');

Example sorting query :

$sql = "SELECT * FROM my_table ORDER BY name" ;