perky416 Posted March 31, 2011 Share Posted March 31, 2011 Hi Guys, When im pulling data from my database and displaying it on my page, im using ORDER BY in my query to display the data in alphabetical order. When it comes to displaying data with numbers in it, it isnt ordering how i want it. Example: This is how it is currently ordering the data: test1 test10 test11 test12 test13 test14 test15 test16 test17 test18 test19 test2 test20 and so on.... How would i go about getting it to display like: test1 test2 test3 test4 test5 test6 test7 test8 test9 test10 test11 test12 and so on.... Thanks Quote Link to comment https://forums.phpfreaks.com/topic/232313-number-issue-with-order-by/ Share on other sites More sharing options...
kickstart Posted March 31, 2011 Share Posted March 31, 2011 Hi That is because it is ordering it in alphabetical order. To do what you want you would need to extract the numeric part of the string and sort by that, then the while string within it. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/232313-number-issue-with-order-by/#findComment-1195110 Share on other sites More sharing options...
Maq Posted March 31, 2011 Share Posted March 31, 2011 If your data constitently has the same name prefixing the integers, then you can use something like: ORDER BY SUBSTRING(column_name,5); Otherwise, it will be a bit more complicated. Quote Link to comment https://forums.phpfreaks.com/topic/232313-number-issue-with-order-by/#findComment-1195169 Share on other sites More sharing options...
kickstart Posted March 31, 2011 Share Posted March 31, 2011 Hi If you do the above then I think you would still need to cast it to be a number. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/232313-number-issue-with-order-by/#findComment-1195189 Share on other sites More sharing options...
perky416 Posted April 1, 2011 Author Share Posted April 1, 2011 Hi guys, Im completely at a loss with this. Im working on a domain script, so people could add domains such as 10.com, test1.com, ismell.com, so it will never be a fixed format. Im tearing my hair out lol. I cant seem to find anything on google about this to work with. Quote Link to comment https://forums.phpfreaks.com/topic/232313-number-issue-with-order-by/#findComment-1195723 Share on other sites More sharing options...
fenway Posted April 3, 2011 Share Posted April 3, 2011 Then you'll need to order this in script with a regex to extract the pieces. Quote Link to comment https://forums.phpfreaks.com/topic/232313-number-issue-with-order-by/#findComment-1196255 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.