Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort a Numbering System with Dashes in It

    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.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This is going to depend on how the data is handled. In any case, if SQL cannot do it for you you will need to write a custom sorting function for it and sort it with usort/uasort/uksort functions. I can't do anything more for you until I know the format you are storing this data in when retrieved from the database.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply. The data is stored in the database as a varchar type but that can be changed if necessary. I retrieve the data from the database using mysql_fetch_array and store it in an associate array. I then use a for loop to print out the data to a table. Thanks for any help you can give me.

  • #4
    Codeasaurus Rex
    Join Date
    Jun 2008
    Location
    Redmond, WA
    Posts
    659
    Thanks
    31
    Thanked 100 Times in 94 Posts
    Best way I can think of doing it is exploding the data into separate arrays and sorting them that way.
    Unless otherwise stated, any code posted is most likely untested and may contain syntax errors.
    My posts, comments, code, and suggestions reflect only my personal views.
    Web Portfolio and Code Snippets: http://shanechism.com

  • #5
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I had thought about that. Putting the 2 separate arrays in two separate columns in the database and then using ORDER BY those 2 columns???

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    I'm thinking you can probably get away with a simple comparison function. If you run an str_replace on you're '-' and replace it with a '.' and cast it to a float, you may be able to cut out a lot of additional work.
    PHP Code:
    function cmp($a$b)
    {
        
    $fA = (float)str_replace('-''.'$a['yourIndexForNum']);
        
    $fB = (float)str_replace('-''.'$b['yourIndexForNum']);
        return  (
    $fA $fB) ? -: (($fA $fB) ? 0);
    }
    usort($arr'cmp'); 
    If that doesn't work, I would do the 2 column idea, but use PHP to do it. Split it off on the - and do comparisons between the first and second numbers to determine their positions. Try the first one, if it doesn't work than I'll put together a different one.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Fou-Lu View Post
    I'm thinking you can probably get away with a simple comparison function. If you run an str_replace on you're '-' and replace it with a '.' and cast it to a float, you may be able to cut out a lot of additional work.
    PHP Code:
    function cmp($a$b)
    {
        
    $fA = (float)str_replace('-''.'$a['yourIndexForNum']);
        
    $fB = (float)str_replace('-''.'$b['yourIndexForNum']);
        return  (
    $fA $fB) ? -: (($fA $fB) ? 0);
    }
    usort($arr'cmp'); 
    If that doesn't work, I would do the 2 column idea, but use PHP to do it. Split it off on the - and do comparisons between the first and second numbers to determine their positions. Try the first one, if it doesn't work than I'll put together a different one.
    it's a good idea to use float,
    in that case can avoid custom function to compare and no need to modify the data from mysql by using in query the mysql function replace:

    PHP Code:
    $query "select replace(numfieldname,'-','.') as howyouwant ..." 
    PS: this in case that sorting this way fit op needs,

    regards

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Yeah, I was thinking about getting sql to do all the work, but I am not certain if it will treat it as a string or a float value.

    The other (PHP Based) comparison was the splitting:
    PHP Code:
    function compare($a$b)
    {
        list(
    $iA1$iA2) = split("-"$a['keytonumber']);
        list(
    $iB1$iB2) = split("-"$b['keytonumber']);
        
    $result 0;
        if ((int)
    $iA1 == (int)$iB1)
        {
            
    $result = (int)$iA2 - (int)$iB2;
        }
        else
        {
            
    $result = (int)$iA1 - (int)$iB1;
        }
        return 
    $result;

    One problem I can see with the above is that if you compare a number like 1-1 and 1-01, they equate to the same number. This is why it makes sense to use a float technique, either through PHP or through SQL.

    If the SQL works totally use that method; sorting can be done on the resulting field as well making everything 100x easier.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    New Coder
    Join Date
    May 2006
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah, I was thinking about getting sql to do all the work, but I am not certain if it will treat it as a string or a float value.
    Why not have mysql cast it to a float then?

    I think you should save it as a float in the first place, so that you can use the indexes on that column. Saving as text makes sorting a lot slower.

    Or save it as int. Just give it enough padding of 0s on the left to cater for the largest values after the -.
    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.
    Where is this failing? in MySQL? Then maybe you didn't allocate enough digits after the floating point when creating the float column.
    Fiji Web Design - where i do Joomla Web Design
    Bucabay.com - My blog


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •