Best MYSQL Field Type for Storing, Sorting, and Displaying Numbers

[siteturbo]

Hello,

 

I am creating a field for numbers.  For example I want to store the following:

 

94

81.25

689

5

 

What is the best way to store these numbers if I want to order them later by their numerical value.  For example, I want to store and then order them from max to min (DESC), like this:

 

689

94

81.25

5

 

If I use a VARCHAR field type it sorts DESC like this (stores correctly, but sorts non-numeric).

 

94

81.25

689

5

 

If I use a DECIMAL (10,5)  field type for example, it orders/sorts them correctly however it stores and displays the numbers like this (don't want those extra zeros!):

 

689.00000

94.00000

81.25000

5.00000

 

However, I want to sort, store, and display like this (no extra zeros and correct numerical order):

 

689

94

81.25

5

 

Thanks in advance.

 

[QuickOldCar]

If you do not want the trailing zero's, maybe you should clean them with this function

 

<?php
function clean_dec($number){
    $pos = strpos($number, '.');
    if($pos === false) { //integer
        return $number;
    }else{ //decimal
        return rtrim(rtrim($number, '0'), '.');
    }
}

$dec_array = array("80","80.0","80.25","80.00","0.80","80.000","80.0000");

foreach($dec_array as $numb){
echo clean_dec($numb)."<br />";
}
?>

[siteturbo]

I thought about doing something similar, however, don't all those zeros padded on the right take up storage space?

 

 

If you do not want the trailing zero's, maybe you should clean them with this function

 

<?php
function clean_dec($number){
    $pos = strpos($number, '.');
    if($pos === false) { //integer
        return $number;
    }else{ //decimal
        return rtrim(rtrim($number, '0'), '.');
    }
}

$dec_array = array("80","80.0","80.25","80.00","0.80","80.000","80.0000");

foreach($dec_array as $numb){
echo clean_dec($numb)."<br />";
}
?>

[QuickOldCar]

I don't feel a few extra zero's would fill up your storage space.

[fenway]

Store as many decimal places as you actually need -- then they aren't "extra".