Jump to content

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


siteturbo

Recommended Posts

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.

 

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 />";
}
?>

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 />";
}
?>

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.