Re: Subject: Ordering a varchar field in mysql

At 09:44 04/12/2007, you wrote:
>Message-ID: <37f3cbf50712032142m5ed6ecf7n61e05a4677248233@mail.gmail.com>
>Date: Tue, 4 Dec 2007 11:12:40 +0530
>From: "Vaibhav Informatics"
>
>*Questions on PHP*


On PHP, OK sure....

>In one of our tables, one of the field is acc_no, we had given the data type
>as varchar, since it could take any alpha-numeric values. In viewing this,

Oh, wait on *MySQL* (or some other database) ? You said on PHP above...
You really should specify which database in that case.


>we used 'order by acc_no.' The sequence of records shown was
>1,10,100,1000,A1, A10, A100, etc. whereas we want the sequence to be
>1,2,3,4,etc. for all the numeric values followed by alpha-numeric values A1,
>A2, A3, etc.
>
>
>
>Can someone please give us the code for this type of ordering?


In MySQL :
http://blog.feedmarker.com/2006/02/01/how-to-do-natural-alph a-numeric-sort-in-mysql/

SELECT acc_no FROM your_table ORDER BY acc_no + 0 ASC


In PHP : http://uk3.php.net/natcasesort

natcasesort($your_resultset_array);


The MySQL version possibly has a collation issue in some character
sets, so you'd have to check the results in whatever collation you've
declared on that column. You might have to use iconv() in PHP to make
sure natcasesort works in the expected manner with non ISO-8859-1 or
UTF-8 character sets.


Cheers - Neil

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php