Why won't MySQL get the correct data?

Question

I'm using CodeIgniter's Active Record and my code is:

  $current_balance = $this->get_campaign_balance($click_report['campaign_id']);

  $campaign_ledger = array(
    'campaign_id'   =>  $click_report['campaign_id'],
    'description'   =>  "Click Deduction from script",
    'amount'        =>  -1 * $click_report['advertiser_spend'],
    'balance'       =>  "" . $current_balance - $click_report['advertiser_spend'],
    'meta_data'     =>  $click_report['day'],
    'timestamp'     =>  time()
  );

  $this->db->insert('campaign_ledger', $campaign_ledger);

When I var_dump the $campaign_ledger I get:

array(6) {
  ["campaign_id"]=>
  string(3) "277"
  ["description"]=>
  string(27) "Click Deduction from script"
  ["amount"]=>
  float(-0.05)
  ["balance"]=>
  float(89.95)
  ["meta_data"]=>
  string(10) "2012-04-19"
  ["timestamp"]=>
  int(1334881599)
}

But when the data goes into my database, the balance is 91.36800000000001 for some reason. So what's the disconnect all about?

EDIT

After changing to DECIMAL(10,4), I still get 91.3680 as an output.

I had it as FLOAT, I changed to `DECIMAL(10,4)` and it's still off — Shamoon, Apr 20 '12 at 00:47

score 2 · Accepted Answer · answered Apr 20 '12 at 00:29

2

Be careful to store currency as type "DECIMAL" in MySQL. If you use Float, you'll get inconsistent results. I know you didn't say here which data type you used, but something tells me it is probably not DECIMAL(10,2).

answered Apr 20 '12 at 00:29

Jonathan Barlow

1,075
6
9

I had it as`FLOAT` but changed to `DECIMAL(10,4)` and it's still off – Shamoon Apr 20 '12 at 00:48
When you say it's still "off" - what are you storing and what do you see in the database? You should echo what you had before, then look in the database with a raw query or phpmyadmin. Then, look at what you get out of the database. That will narrow down where the improper precision is coming in. If you're storing money, just store cents (10,2). Another trick some people use is to store money with no decimals. Just multiply by 100 and store as a big int. Then divide by 100 when you want to translate back into dollars and cents. – Jonathan Barlow Apr 20 '12 at 00:56
Edited the original question. Please see above. I'm not storing money with standard 2 digit precision. I need way more granularity. – Shamoon Apr 20 '12 at 01:01
Shamoon, I really think that if you've got 89.95 as a float in PHP, and you maybe do a number_format(89.95,4) and then put it into MySQL (and even if you don't do the number_format) I really think you're not going to get 91.3680. Maybe you've got the old value in there still? – Jonathan Barlow Apr 20 '12 at 01:22
It appears to be a speed issue, which makes no sense. If I insert just one value, it's fine, but when I throw it in a loop, no dice. Any ideas? – Shamoon Apr 20 '12 at 13:40
Shamoon - I would need to see more code - sometimes a mysterious problem like this needs another set of eyes and another programmer may see a simple thing that is causing the problem. – Jonathan Barlow Apr 26 '12 at 14:52

Why won't MySQL get the correct data?

1 Answers1