how to sort numerical data in sql table using php

Question

I have data which has 5000 rows with 40 columns. I want to sort the data according to the first column in the row, which contains the id value of the particular row. The values in the id column are like 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,.....100,101,102.....1000,1001..... when I use a SQL statement with the order clause ORDER by id ASC.

Data is sorting in this manner..1,10,100,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,101.102,103,........

But I want the data to be in ascending order like: 1,2,3,4,5,6,7,8,9,10,11,12,13.....

This is the statement: $result = mysql_query("SELECT * FROM masterdb ORDER BY id ASC");

The query code you shown will give you exact output:- 1,2,3,4,5,6,7,8,9,10,11,12,13.......... — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Mar 1, 2017 at 5:43
Your query is correct as soon as your ids exact column is id or you id must not be numeric type — BetaDev
– BetaDev, Commented Mar 1, 2017 at 5:43
Thanks @kazz ,my coumn id i gave is varchar,when i changed it to int,its working. — sasi
– sasi, Commented Mar 1, 2017 at 5:47

Tim Biegeleisen · Accepted Answer · 2017-03-01 05:46:34Z

2

You may want to consider making your id column a numeric type if you expect to have the need to often sort numerically. That being said, one workaround would be to cast the id column to a numeric type and then sort using this:

SELECT *
FROM masterdb
ORDER BY CAST(id AS UNSIGNED)

answered Mar 1, 2017 at 5:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sasi Over a year ago

I want to keep my id coumn as varchar , because sometimes my id contains alpabets also like TNDIDTNC120134,TNDIDTNC120135,TNDIDTNC120136....along with the numeric id's like 1,2,3,4,5,6...In this situation ,how can i sort all the alphanumerics in the starting and the numerics at the end.

Tim Biegeleisen Over a year ago

@sasi Does the id column always have the format of letters followed by numbers? Furthermore, is the width of the letter portion always the same? Update your question showing the full extent of what this column might look like.

sasi Over a year ago

Sometimes id is characters and numericals and sometimes only numericals. The width of the id is always of this length ITNMWROCT160010 ,but sometimes its lesser than this with just numerical values like 1,2,3,4,5,6,7,8,9,10,......100,101,102,103....1000

Tim Biegeleisen Over a year ago

If the width of the id is always some length, but sometimes less, then it isn't always some length. Update your question and clearly show us the range of values which the id column can take.

sasi Over a year ago

The range of the value is 15 and if id is an alphanumeric like 'ITNMWROCT160010' it is fixed length for all alphanumeric entries,if it is only numeric like 1,2,3,4,5,6,7,8....

|

Collectives™ on Stack Overflow

how to sort numerical data in sql table using php

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related