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

Isak Savo · Accepted Answer · 2009-09-06 21:17:21Z

2

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"

answered Sep 6, 2009 at 21:17

Isak Savo

36.2k11 gold badges64 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

+1 for the subtle nudge to worry about formatting on the application layer, and to use a function that does exactly what's needed.

OMG Ponies Over a year ago

...and then you have to employ the logic every time you want to display the value in a consistent format. +1 for Maintenance nightmare

zombat · Accepted Answer · 2009-09-06 20:01:17Z

0

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

answered Sep 6, 2009 at 20:01

zombat

94.5k25 gold badges158 silver badges165 bronze badges

Comments

Adam Bellaire · Accepted Answer · 2009-09-06 20:04:53Z

0

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;

answered Sep 6, 2009 at 20:04

Adam Bellaire

111k19 gold badges153 silver badges165 bronze badges

2 Comments

unsafe_where_true Over a year ago

I actually prefer this solution to having to cope with the maintenance issue, although I can see the value of that one. However, my mysql knowledge is really low, so can this be done for all tables just once, like a stored proc or so? Or do I have to even submit this code in my SQL statement inside the PHP file? Thanks

Adam Bellaire Over a year ago

From the looks of things, you can't do this in a stored function because the return type is of two different precisions (a function should have only one specific precision which it returns). There is nothing stopping you from putting this into a sProc, but you will have to call that sProc every time you want to massage the data in this way.

Collectives™ on Stack Overflow

Automatic decimal number formatting in SQL or PHP?

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related