How to insert float variable in database properly

Question

I have the following table

CREATE TABLE IF NOT EXISTS `payment_data` (
  `orderid` int(11) NOT NULL,
  `orderDesc` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `orderAmount` float NOT NULL,
  UNIQUE KEY `orderid` (`orderid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and I'm trying to insert a line in it using PHP.

$sql = 'INSERT INTO payment_data '.
           '(orderid, orderDesc, name, email, orderAmount) '.
           'VALUES ( '.$form_order_id.', "'.$form_order_desc.'", "'.$form_name.'", "'.$form_email.'", '.number_format($form_order_amount, 2, '.', ',').' )';

The problem is that "orderAmount" is inserted without the decimal part. For example if $form_order_amount=30,45 then 30 is what's inserted in database.

I used number_format() because it's supposed to convert "30,45" to "30.45".

You need the decimal precision for float orderAmount float(7,2)` or something desireable — Abhik Chakraborty
– Abhik Chakraborty, Commented Oct 1, 2014 at 8:41
30,45 with a , decimal is not a valid number to a computer; you need a decimal point (.) when inserting to MySQL — Mark Baker
– Mark Baker, Commented Oct 1, 2014 at 8:42
That said, don't use float for database money, use decimal.... or even better, adjust the value to cents so you're working with integers if you ever need to do adjustments for tax calculations, etc where floating point precision may cause issues — Mark Baker
– Mark Baker, Commented Oct 1, 2014 at 8:43
number_format() should be used when displaying a float value, not internally when storing it, and it won't convert 30,45 to 30.45, but will do the reverse — Mark Baker
– Mark Baker, Commented Oct 1, 2014 at 8:45

Mikpa · Accepted Answer · 2014-10-01 08:47:16Z

3

Convert form_order_amount to float first, using this:

$form_order_amount = floatval(str_replace(',', '.', $form_order_amount));

answered Oct 1, 2014 at 8:47

Mikpa

1,93215 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Banana · Accepted Answer · 2018-02-23 10:46:01Z

0

I had a similar problem. This is how I did it. Source php manuals

public static function tofloat($num) {
    $dotPos = strrpos($num, '.');
    $commaPos = strrpos($num, ',');
    $sep = (($dotPos > $commaPos) && $dotPos) ? $dotPos :
            ((($commaPos > $dotPos) && $commaPos) ? $commaPos : false);

    if (!$sep) {
        return floatval(preg_replace("/[^0-9]/", "", $num));
    }

    return floatval(
        preg_replace("/[^0-9]/", "", substr($num, 0, $sep)) . '.' .
        preg_replace("/[^0-9]/", "", substr($num, $sep + 1, 
        strlen($num)))
    );
}

edited Feb 23, 2018 at 10:46

Banana

2,4438 gold badges37 silver badges62 bronze badges

answered Feb 23, 2018 at 9:54

PatoWhiz

1

Collectives™ on Stack Overflow

How to insert float variable in database properly

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related