Converting number formats between php and mysql

Question

I'm sure this is something really simple, like a misplaced quote, but I'm completely stumped. I'm using an update query to enter shopping cart data into a mysql table, and having some major issues with card balance. The column in the mysql table is called BalanceCurrent and it's a decimal(18,2). In my php code (this is a custom Joomla component, so there are some Joomla objects and methods like JFactory and quote), here's what I'm doing:

$rightNow = date("YmdHis");
$db =& JFactory::getDBO();

$query = "update arrc_Voucher set BalanceCurrent = " . $db->quote(number_format($this->balanceRemaining,2)) . ", UpdateDT = ".$db->quote($rightNow). "  where VoucherNbr = ".$db->quote($this->voucherNbr);
error_log("update query: ".$query);
$db->setQuery($query);  
if (!$db->query()) error_log("error inside updateVoucher: ".$db->stderr());

The issue is this: $db->quote(number_format($this->balanceRemaining,2)). Without the quotes (created with $db->quote around the value) I get a mySQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '950.00, UpdateDT = '20100813150330'  where VoucherNbr = '1142100120514884'' at line 1 SQL=update arrc_Voucher set BalanceCurrent = 4,950.00, UpdateDT = '20100813150330'  where VoucherNbr = '1142100120514884'

With the quotes, there's no error, and this is the SQL statement that results (printed to the error_log):

update query: update arrc_Voucher set BalanceCurrent = '4,950.00', UpdateDT = '20100813150508'  where VoucherNbr = '1142100120514884'

It looks fine, but the problem is, when I actually go into the database, the BalanceCurrent field is now set to 4.00, not 4950.00.

Any ideas? This is really killing me.

Answer 1

I guess the thousands separator comma maybe a problem. What's the datatype of the field?

If you want to strip the thousands separator try: number_format($this->balanceRemaining,2, '.', '')

Answer 2

Just do not use number_format() for inserting data into database. It's for displaying data. For pure rounding use round() function.