CODEWITHSUNDEEP
phpmysql
user3733831
user3733831

Reputation: 2926

mysql decimal and double data issue

I am trying to store currency data in mysql. Here I used decimal and double datatype for it.

This is my table structure: 

CREATE TABLE IF NOT EXISTS transactions (
  tb_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tb_our_rate DECIMAL(15, 2) NOT NULL,
  tb_bank_rate DECIMAL(15, 2) NOT NULL,
  tb_lkr_funds DOUBLE(15, 2) NOT NULL,
  tb_aud_funds DOUBLE(15, 2) NOT NULL,  
  PRIMARY KEY (tb_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I tried inserting some values to this columns. This is my inserted values.

133.11
120.20
-12312,123,123.02
-12312,231,423.32

This is how it insert to database: 

mysql> select * from transactions\G
*************************** 1. row ***************************
   tb_our_rate: 13311.00
  tb_bank_rate: 12020.00
  tb_lkr_funds: -1231212312302.00
  tb_aud_funds: -1231223142332.00
    date_added: 2015-11-26 07:36:01
*************************** 2. row ***************************

UPDATE: This is how my insert query work:

$our_rate   = filter_input(INPUT_POST, 'our_rate', FILTER_SANITIZE_NUMBER_FLOAT);
$bank_rate  = filter_input(INPUT_POST, 'bank_rate', FILTER_SANITIZE_NUMBER_FLOAT);
$lkr_funds  = filter_input(INPUT_POST, 'lkr_funds', FILTER_SANITIZE_NUMBER_FLOAT);
$aud_funds  = filter_input(INPUT_POST, 'aud_funds', FILTER_SANITIZE_NUMBER_FLOAT);

$query = "INSERT INTO transaction_banks( 
                              tb_our_rate
                            , tb_bank_rate
                            , tb_lkr_funds
                            , tb_aud_funds
                            , date_added
                            ) VALUES (?, ?, ?, ?)";
$insert_stmt = $mysqli->prepare($query);

if ($insert_stmt){
    $insert_stmt->bind_param('dddd', 
                                $our_rate
                              , $bank_rate
                              , $lkr_funds
                              , $aud_funds
                            );
  $insert_stmt->execute(); 
}

Can anybody tell me what is the problem of this?

Upvotes: 0

Views: 68

Answers (2)

Drew
Drew

Reputation: 24949

This looks good to me. Don't do inserts with commas in the numbers. I am afraid to even try.

create table thing99
(   id int auto_increment primary key,
    tb_our_rate DECIMAL(15, 2) NOT NULL,
    tb_bank_rate DECIMAL(15, 2) NOT NULL,
    tb_lkr_funds DOUBLE(15, 2) NOT NULL,
    tb_aud_funds DOUBLE(15, 2) NOT NULL
);


insert thing99(tb_our_rate,tb_bank_rate,tb_lkr_funds,tb_aud_funds) values
(133.11,120.20,-12312123123.02,-12312231423.32);

select * from thing99;
+----+-------------+--------------+-----------------+-----------------+
| id | tb_our_rate | tb_bank_rate | tb_lkr_funds    | tb_aud_funds    |
+----+-------------+--------------+-----------------+-----------------+
|  1 |      133.11 |       120.20 | -12312123123.02 | -12312231423.32 |
+----+-------------+--------------+-----------------+-----------------+

Edit:

Just found it. You have to use option 2 below.

$our_rate   = filter_input(INPUT_POST, 'our_rate', FILTER_SANITIZE_NUMBER_FLOAT);

$our_rate2=filter_input(INPUT_POST,'our_rate',
FILTER_SANITIZE_NUMBER_FLOAT,FILTER_FLAG_ALLOW_FRACTION);



echo $our_rate."<br>";
echo $our_rate2."<br>";

tested

From the Manual page on Filter Flags and FILTER_FLAG_ALLOW_FRACTION:

enter image description here

Upvotes: 0

Logan Wayne
Logan Wayne

Reputation: 5991

Remove the FILTER_SANITIZE_NUMBER_FLOAT. This removes the . in your input.

Check this documentation.

It sanitizes the input, and only allows + and -.

You can use FILTER_FLAG_ALLOW_FRACTION:

$our_rate   = filter_input(INPUT_POST, 'our_rate', FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$bank_rate  = filter_input(INPUT_POST, 'bank_rate', FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$lkr_funds  = filter_input(INPUT_POST, 'lkr_funds', FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$aud_funds  = filter_input(INPUT_POST, 'aud_funds', FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);

Check this previous question about this problem.

Upvotes: 1

Related Questions