MySQL number type

mysql data type number

[dsignergonwron]

Hi everyone,

I want to be able to display a number like this 12.20 but it is being saved in my database like this 12.2 without the 0. I am using DOUBLE but what is the best data type to use? Or do I need some PHP somewhere on my page?

Thanks in advance!

[Romeo]

Hi Dsignergonwron,

welcome to WebMasterWorld.

You could just reformat the value inside a short PHP sequence using printf/sprintf like this example:

<?php
$money = 12.2;
$formatted = sprintf("%01.2f", $money);
// echo $formatted will output "12.20"
?>

Regards,
R.

[dsignergonwron]

Brilliant thanks :)

[arran]

You could also change the column type to

float(X,2)

where

X

is the maximum number of digits you wish to allow (including the 2 decimal digits).

arran.

[dsignergonwron]

Sorry how do you do this inside a repeat region? My code is:

do {
$number = $row_RECOMMENDATION_NUMBER['last_auditor_number'];
$formatted = sprintf("%01.2f", $number);
echo $row_RECOMMENDATION['auditor_number'];
} while ($row_RECOMMENDATION = mysql_fetch_assoc($RECOMMENDATION));

[dsignergonwron]

Arran thanks for that quick method! But this always displays the zeros so my numbers always look like this 12.00 - not so good :(

[arran]

$number = $row_RECOMMENDATION_NUMBER['last_auditor_number'];

Should read:

$number = $row_RECOMMENDATION['last_auditor_number'];

Also, shouldn't you be echo'ing

$formatted

not

$row_RECOMMENDATION['auditor_number'];

?

[dsignergonwron]

Oops! Pasted very rubish code just then - rushing like mad :) Anyway it works now but it displays all the zeros. I guess i have to do some kind of if statement. Cheers guy!

[arran]

The problem is you want two different outputs based on the value of the decimal. For 12 you want 12 but for 12.2 you want 12.20. As you pointed out, you will have to use some logic to cater for the exception (i.e. whole numbers).

arran.