Welcome to WebmasterWorld Guest from 54.159.250.110

Forum Moderators: open

Message Too Old, No Replies

MySQL number type

mysql data type number

   
1:49 pm on Oct 10, 2005 (gmt 0)

5+ Year Member



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!

1:58 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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.

2:01 pm on Oct 10, 2005 (gmt 0)

5+ Year Member



Brilliant thanks :)
2:05 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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.

2:08 pm on Oct 10, 2005 (gmt 0)

5+ Year Member



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));

2:12 pm on Oct 10, 2005 (gmt 0)

5+ Year Member



Arran thanks for that quick method! But this always displays the zeros so my numbers always look like this 12.00 - not so good :(
2:14 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



$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'];
?
2:23 pm on Oct 10, 2005 (gmt 0)

5+ Year Member



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!
2:31 pm on Oct 10, 2005 (gmt 0)

10+ Year Member



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.