...

View Full Version : Sort a Numbering System with Dashes in It



KingGabbo
07-22-2008, 11:14 PM
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.

Fou-Lu
07-23-2008, 12:44 AM
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.

KingGabbo
07-23-2008, 09:35 PM
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.

ShaneC
07-23-2008, 09:49 PM
Best way I can think of doing it is exploding the data into separate arrays and sorting them that way.

KingGabbo
07-23-2008, 09:55 PM
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???

Fou-Lu
07-24-2008, 12:27 AM
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.


function cmp($a, $b)
{
$fA = (float)str_replace('-', '.', $a['yourIndexForNum']);
$fB = (float)str_replace('-', '.', $b['yourIndexForNum']);
return ($fA < $fB) ? -1 : (($fA > $fB) ? 1 : 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.

oesxyl
07-24-2008, 12:38 AM
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.


function cmp($a, $b)
{
$fA = (float)str_replace('-', '.', $a['yourIndexForNum']);
$fB = (float)str_replace('-', '.', $b['yourIndexForNum']);
return ($fA < $fB) ? -1 : (($fA > $fB) ? 1 : 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:



$query = "select replace(numfieldname,'-','.') as howyouwant ..."


PS: this in case that sorting this way fit op needs, :)

regards

Fou-Lu
07-24-2008, 01:17 AM
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:


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.

digital-ether
07-24-2008, 09:43 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum