Automatic decimal number formatting in SQL or PHP?

Question

In my DB, most numeric values are of type DECIMAL(10,6). They are currency columns.

Now, when I retrieve the numbers from mysql, I get something like this

34.123456 //I want these
0.987654
500.000000 //I do not want these
1.000000

Is it possible to have the numbers automatically show up as integers when they are and to maintain decimals to my desired precision after the point, so that I get e.g.

34.123456
0.987654
500
1

Answer 1

In PHP, you could do a simple integer comparison before output:

echo ((int) $var == $var) ? (int) $var : $var;

As a quick/dirty example, this code:

$var = '500.01';
echo ((int) $var == $var) ? (int) $var : $var;
echo "\n";
$var = '500.00';
echo ((int) $var == $var) ? (int) $var : $var;

Produces:

500.01
500

Answer 2

You can CAST the value to whatever type you like to enforce this precision at the SQL side, for example:

SELECT 
  CASE myNumber MOD 1 
    WHEN 0 THEN CAST(myNumber AS decimal(10,0)) 
    ELSE myNumber 
  END 
FROM myTable;

Answer 3

If all you want is to modify the displayed digits, then you can use printf with the %g formatter and specify maximum number of precision digits:

printf ("%.10g", 123.456); // outputs "123.456"
printf ("%.10g", 123.456000000); // outputs "123.456"
printf ("%.10g", 123.000000000); // outputs "123"
printf ("%.10g", 1.234567891); // outputs "1.234567891"