Why I can not insert 0.00 decimal value with PDO into my database?

Question

I think the question is clear enough. Here are some details...
Sample data:

array(2) {
  [0]=>
  array(5) {
    ["index"]=>
    string(1) "1"
    ["source"]=>
    string(0) ""
    ["target"]=>
    string(0) ""
    ["price"]=>
    string(6) "153.00"
    ["order"]=>
    string(5) "19442"
  }
  [1]=>
  array(5) {
    ["index"]=>
    string(1) "2"
    ["source"]=>
    string(5) "Test1"
    ["target"]=>
    string(5) "Test2"
    ["price"]=>
    string(4) "0.00"
    ["order"]=>
    string(0) ""
  }
}

Then I am just looping trough data and inserting it into database like this:

$sql = "INSERT INTO `os_bill_position`
        (`position_index`, `source_name`, `target_name`, `price_netto`, `FID_bill`, `FID_order`)
        VALUES
        (:index, :source, :target, :price, :billID, :orderID)";
$stmt = $link->prepare($sql);

foreach ($allPositions as $position) {
        $stmt->bindValue(':index', $position['index']);
        $stmt->bindValue(':source', $position['source']);
        $stmt->bindValue(':target', $position['target']);
        $stmt->bindValue(':price', $position['price'], PDO::PARAM_STR);
        $stmt->bindValue(':billID', $billID);
        $stmt->bindValue(':orderID', $position['order']);

        $stmt->execute();
    }

$billID is a constant

Database:

CREATE TABLE `os_bill_position` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `position_index` tinyint(3) unsigned NOT NULL,
  `source_name` varchar(255) NOT NULL,
  `target_name` varchar(255) NOT NULL,
  `price_netto` decimal(10,2) DEFAULT NULL,
  `FID_bill` int(10) unsigned NOT NULL,
  `FID_order` int(10) unsigned DEFAULT NULL,
  `position_tax` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FID_bp_bill` (`FID_bill`),
  KEY `FID_bp_order` (`FID_order`),
  CONSTRAINT `FID_bp_bill` FOREIGN KEY (`FID_bill`) REFERENCES `os_bill` (`ID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT `FID_bp_order` FOREIGN KEY (`FID_order`) REFERENCES `os_order` (`ID`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What I tried so far:

1.According to PDO::PARAM for type decimal

There isn't any PDO::PARAM for decimals / floats, you'll have to use PDO::PARAM_STR

$stmt->bindValue(':price', $position['price'], PDO::PARAM_STR);

2. $stmt->bindValue(':price', $position['price']);

3. $stmt->bindValue(':price', (float) $position['price']);

I always successfully insert anything but cant insert exactly 0.00... It can be anywhere, loop doesnt break it still loops if its on very beginning or somewhere in the middle or at the end. asd

EDIT I was asked to add error message:

In my loop I collect errors and then output them:

$stmt->execute();
$errors[] = $stmt->errorInfo();

array(1) {
  [0]=>
  array(3) {
    [0]=>
    string(5) "00000"
    [1]=>
    NULL
    [2]=>
    NULL
  }
}

Nothing is clear actually. *Nobody* knows why *you* cannot insert something. We don't know even what happens if you try to insert 0.00 — Your Common Sense, May 13 '16 at 08:33
Please check edit. I added errors (nothing). How can I edit my question to make it more clear? I provided all details I could imagine that would help to answer. — , May 13 '16 at 08:35
It's possibly submitting "0.00" as string because the dot is throwing it off. I would suggest that you try what @DevDonkey suggested. This would bind the variable as a float in particular and not as any other type. — Tim, May 13 '16 at 08:37
You can tell **what is the actual outcome** when you try to insert 0.00. A null value? An error? An explosion that ruined your house? — Your Common Sense, May 13 '16 at 08:38
If it doesn't insert a line at all, then you should get an error message as described in this answer: http://stackoverflow.com/questions/32648371/pdo-statement-returns-false/32648423#32648423 — Your Common Sense, May 13 '16 at 08:43
@Tim please, do not suggest an action that will introduce an error in the business logic. Thank you. — Your Common Sense, May 13 '16 at 08:45
your problem lies in that you try to submit zero's in an `integer`. Integers trim the first zero('s) away. You are better off by changing that field to `varchar` — Dorvalla, May 13 '16 at 08:45
@YourCommonSense thanks. The answer pointed me to the right direction. Problem lies not in decimal, but in foreign key which im trying to set to an empty string, which is invalid NULL **and** foreign key. Shame on me. — , May 13 '16 at 09:02
Good to know. Please note that you shoul not cast prices to float. Decimal type have to be handled strictly as a string, or it will lose precision. — Your Common Sense, May 13 '16 at 09:04

score 0 · Accepted Answer · answered May 13 '16 at 09:13

The answer in comments pointed me to the right direction. Problem lies not in decimal 0.00, but in foreign key which im trying to set to an empty string, which is invalid NULL and foreign key.

A quick fix would be to check if string is empty and set it to NULL:

if (empty($position['order'])) {
    $position['order'] = NULL;
}

Why I can not insert 0.00 decimal value with PDO into my database?

1 Answers1