kinggabbo Posted July 22, 2008 Share Posted July 22, 2008 My company has documents with a numbering system as follows: 1-1, 1-2, 2-3, 5-3, 10-2, 2-101, etc... Numbers and dashes. I am storing info about these documents, such as these numbers in a mysql database and then outputting the info into an HTML table on the web page. I was wanting to be able to sort the table ascending and descending based on this numbering system. Does anyone have any ideas how to do this? I tried converting the dashes to decimals but that fails whenever you have more than two numbers after the dash. Any help would be appreciated. Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/ Share on other sites More sharing options...
Barand Posted July 22, 2008 Share Posted July 22, 2008 <?php $arr = array ( '1-1', '1-2', '2-3', '5-3', '10-2', '2-101' ); sort ($arr); echo '<pre>', print_r($arr, true), '</pre>'; /* gives Array ( [0] => 1-1 [1] => 1-2 [2] => 10-2 [3] => 2-101 [4] => 2-3 [5] => 5-3 ) */ // however, with natsort() natsort($arr); echo '<pre>', print_r($arr, true), '</pre>'; /* gives Array ( [0] => 1-1 [1] => 1-2 [4] => 2-3 [3] => 2-101 [5] => 5-3 [2] => 10-2 ) */ ?> Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/#findComment-596880 Share on other sites More sharing options...
kinggabbo Posted July 23, 2008 Author Share Posted July 23, 2008 Barand, Thanks for the reply. natsort() seems to sort like I want it. However I am not sure how to use it to make the whole table sort by that field(the field that has been sorted using natsort()) I am relatively new to php. Any help would be appreciated. Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/#findComment-597887 Share on other sites More sharing options...
Barand Posted July 23, 2008 Share Posted July 23, 2008 MySQL doesn't have a natsort so you could a ) read all data into an array and natsort the array, or, if tables too large b ) split code into two int parts and ORDER BY code1, code2 UPDATE table SET code1 = LEFT(code, LOCATE('-', code)-1), code2 = RIGHT(code, LENGTH(code) - LOCATE('-', code)) Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/#findComment-597937 Share on other sites More sharing options...
kinggabbo Posted July 23, 2008 Author Share Posted July 23, 2008 OK. I tried the code but I'm not sure I coded this right or understood your code sample. My original field with the numbers and dashes is called Exhibit Number. I added the two new fields called code1 and code2, according to how I understood your code. When I ran the query, all it did was insert 'Exhibit Number' into the code2 field. Here is the query: "update exhibits set code1 = LEFT('`Exhibit Number`', LOCATE('-', '`Exhibit Number`')-1), code2 = RIGHT('`Exhibit Number`', LENGTH('`Exhibit Number`') - LOCATE('-', '`Exhibit Number`'))" Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/#findComment-597984 Share on other sites More sharing options...
Barand Posted July 23, 2008 Share Posted July 23, 2008 or c ) alternative sort without adding extra columns SELECT * FROM table ORDER BY CAST(LEFT(`Exhibit Number`, LOCATE('-', `Exhibit Number`)-1) AS SIGNED INTEGER), CAST(RIGHT(`Exhibit Number`, LENGTH(`Exhibit Number`) - LOCATE('-', `Exhibit Number`)) AS SIGNED INTEGER) Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/#findComment-598001 Share on other sites More sharing options...
kinggabbo Posted July 23, 2008 Author Share Posted July 23, 2008 You know what! I think option c worked. I am going to do some more testing but it looks like it is doing what I want it to. Thanks alot for the help everyone. Now to try to figure out exactly how it works and I'll be set. Link to comment https://forums.phpfreaks.com/topic/116077-sort-a-numbering-system-with-dashes-in-it/#findComment-598019 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.