Welcome to WebmasterWorld Guest from 54.224.63.26

Forum Moderators: open

Message Too Old, No Replies

MySQL number type

mysql data type number

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

New User

10+ Year Member

joined:Oct 10, 2005
posts:5
votes: 0


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)

Preferred Member

10+ Year Member

joined:Sept 28, 2002
posts:505
votes: 0


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)

New User

10+ Year Member

joined:Oct 10, 2005
posts:5
votes: 0


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

Preferred Member

10+ Year Member

joined:Feb 16, 2005
posts:456
votes: 0


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)

New User

10+ Year Member

joined:Oct 10, 2005
posts:5
votes: 0


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)

New User

10+ Year Member

joined:Oct 10, 2005
posts:5
votes: 0


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)

Preferred Member

10+ Year Member

joined:Feb 16, 2005
posts:456
votes: 0


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

New User

10+ Year Member

joined:Oct 10, 2005
posts:5
votes: 0


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)

Preferred Member

10+ Year Member

joined:Feb 16, 2005
posts:456
votes: 0


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.