Sort a Numbering System with Dashes in It

[kinggabbo]

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.

[Barand]

<?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
)
*/

?>

 

 

[kinggabbo]

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.

 

 

[Barand]

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))

[kinggabbo]

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`'))"

[Barand]

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)

[kinggabbo]

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.