CODEWITHSUNDEEP
phpmysqlsqlnumber-formatting
zekia
zekia

Reputation: 4857

How to insert float variable in database properly

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".

Upvotes: 0

Views: 16797

Answers (2)

PatoWhiz
PatoWhiz

Reputation: 1

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

Upvotes: 0

Mikpa
Mikpa

Reputation: 1922

Convert form_order_amount to float first, using this:

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

Upvotes: 3

Related Questions