954,568 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article

Sort Problem

Hello everybody,
I am using WAMP:
1. PHP 5..
2. MySQL 5..
3. Apache 2..

the collation of my database and tables and fields are 'utf8_persian_ci', and also my query is in this format in PHP:

mysql_query("SET NAMES utf8");
mysql_query("SET CHARACTER SET utf8");
$query = mysql_query("SELECT * FROM tblName SORT BY fieldName COLLATE utf8_persian_ci");


this all utf8 is for my Arabic and persian words...

Problem:
I faced a problem on sorting english words, I dont know why the result on the browser is like the attachment

Attachments sort.PNG 2.25KB
tanha
Posting Whiz in Training
218 posts since Aug 2007
Reputation Points: 8
Solved Threads: 1
 

just a guess check for extra spaces

amigura
Junior Poster in Training
72 posts since Jan 2008
Reputation Points: 11
Solved Threads: 7
 

There is no more spaces...

just I dont know why doing like this

tanha
Posting Whiz in Training
218 posts since Aug 2007
Reputation Points: 8
Solved Threads: 1
 
amigura
Junior Poster in Training
72 posts since Jan 2008
Reputation Points: 11
Solved Threads: 7
 

I dont have farsi sort problem, but see the attachment, the problem is that the english words are not sorted correctly ...

also I enter through the web, mysql command line, phpMyAdmin the following data:
Asad
asad
Asad
asad

and again by executing the following query I get the same dispaly:
SELECT * FROM tblName order by fieldName;

or

SELECT * FROM tblName order by fieldName COLLATE utf8_persian_ci;

The Result:
Asad
asad
Asad
asad

tanha
Posting Whiz in Training
218 posts since Aug 2007
Reputation Points: 8
Solved Threads: 1
 

try

SELECT * FROM  table_name ORDER BY BINARY column_name ASC
amigura
Junior Poster in Training
72 posts since Jan 2008
Reputation Points: 11
Solved Threads: 7
 

Then if I try to use the binary, then it is ok, but you know that the binary sort according to ascii code of the character, then the following problem:
Asad
asad
Asad
asad
Bob
Tom
the output:

Asad
Asad
Bob
Tom
asad
asad

The sort must be like this:
Asad
Asad
asad
asad
Bob
Tom

tanha
Posting Whiz in Training
218 posts since Aug 2007
Reputation Points: 8
Solved Threads: 1
 

SELECT * FROM table_name ORDER BY column_name, BINARY column_name ASC

amigura
Junior Poster in Training
72 posts since Jan 2008
Reputation Points: 11
Solved Threads: 7
 

again it is the same result, nothing more... but I need the result as:
Asad
Asad
asad
asad
Bob
Tom

tanha
Posting Whiz in Training
218 posts since Aug 2007
Reputation Points: 8
Solved Threads: 1
 

when i try it i get

ASAD
ASAD
ASAD
AsAD
Asad
Asad
asad
asad
BOB
Bob
bob
TOM
tom
tom

amigura
Junior Poster in Training
72 posts since Jan 2008
Reputation Points: 11
Solved Threads: 7
 

Yes. Thanks very much, I forgot putting the comma ....


Thanks

tanha
Posting Whiz in Training
218 posts since Aug 2007
Reputation Points: 8
Solved Threads: 1
 

This question has already been solved

Post: Markdown Syntax: Formatting Help
You