Handling Money with PHP

[rcouser]

Hello there,

I'm having a problem displaying money correctly with php. I have a field called "balance" with the type float(10,2), in this field I have a number store as "34.55" which I can go in and look at within phpmyadmin but when I echo this value on the front-end of the site it is displayed as "34.549999237061"

Can someone please help?

Regards.

[BlueSkyIS]

do you have a question?

[rcouser]

Yep, why is the 34.55 coming out of the database as 34.549999237061 and how do I make it display as 34.55

[BlueSkyIS]

the value in the database is probably really 34.549999237061, regardless of what you see in phpmyadmin.

 

you can use money_format() or number_format() to display the value with only 2 decimal places.

[PFMaBiSmAd]

You should be using a DECIMAL data type to store your values AND you are probably doing something in your php code that treats the number as a floating point value which results in a floating point conversion error. What's your actual code that is retrieving and displaying the value?

[rcouser]

Thanks for the reply, I am working with a prebuilt system that uses float for currency. To retrieve the value is use the follow:

<code>

$conn = dbConnect('query');

$sql = 'SELECT id, balance

        FROM user

        WHERE id = ?';

$stmt = $conn->stmt_init();

if ($stmt->prepare($sql)) {

    $stmt->bind_param('i', $_GET['id']);

    $stmt->bind_result($id, $balance);

    $OK = $stmt->execute();

    $stmt->fetch();

    $stmt->free_result();

}

 

echo $balance;

</code>

[jamesxg1]

Is this what you are looking for?

 

Change echo $balance; to this.

 

setlocale(LC_MONETARY, 'en_GB');
echo money_format('%i', $balance);

 

James.

 

[rcouser]

Thanks james but that's not what i'm looking for. I added it to the database as 34.55 and I need it to come out as 34.55 so that if u update the value but adding 0.30, it can go back into the database as 34.85. The problem with using money_format() or number_format() is that it adds punctuation such as commas and full stops.

Where does the 34.549999237061 come from?

[jamesxg1]

What are the properties of the `balance` ? Is is a varchar, int, text, ect?

 

James.

[rcouser]

The property of 'balance' is float(10,2)

[jamesxg1]

Ok in the query change balance to this, it may or may not work.

 

FORMAT(`balance`, 2)

 

James.

 

[jamesxg1]

May be very useful.

 

http://stackoverflow.com/questions/2251290/storing-money-amounts-in-mysql

 

James.

 

[rcouser]

Thanks you very much for all the help James. Much Appreciated.

[jamesxg1]

Thanks you very much for all the help James. Much Appreciated.

 

No problem, my pleasure.

 

James.