MySQL VALUES（）函数不能与Codeigniter db-> query（）一起使用

I'm trying to make this work:

我正在努力做到这一点：

$articles_id = 105; // comes from argument
$type = 'units_out'; // comes from argument
$quantity = 4; // comes from argument
$date = date('Y-m-d');

$sql = "INSERT INTO stock (articles_id, date, units_in, units_out)
                    VALUES (?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE $type = IF(VALUES($type) - $quantity >= 0, VALUES($type) - $quantity, 0)";

$res = $this->db->query($sql, array($articles_id, $date, 0, $quantity));

The record already exists, so it does an update. The initial value of units_out in BD is 2, and its an UNSIGNED MEDIUMINT field. So, since it's unsigned type, 2 - 4 = -2, which for an UNSIGNED MEDIUMINT means start counting down from its max value.

该记录已存在，因此它会进行更新。 BD中units_out的初始值为2，其为UNSIGNED MEDIUMINT字段。因此，因为它是无符号类型，2 - 4 = -2，对于UNSIGNED MEDIUMINT意味着从其最大值开始倒计时。

After the query, units_out=16777215. I want to avoid this behaviour by using the VALUES() function of MySQL, but it isn't working if I use it with bd->query()

在查询之后，units_out = 16777215。我想通过使用MySQL的VALUES（）函数来避免这种行为，但是如果我将它与bd-> query（）一起使用它就无法工作

I've tried by directly doing it in PHPMyAdmin and it does work, units_out results in 0. Why this isn't working in Codeigniter?

我已经尝试过在PHPMyAdmin中直接执行它并且它确实有效，units_out结果为0.为什么这在Codeigniter中不起作用？

1 个解决方案

#1

add one more condition in your if, (VALUES($type) > $quantity) , like

在if，（VALUES（$ type）> $ quantity）中添加一个条件，比如

$sql = "INSERT INTO stock (articles_id, date, units_in, units_out)
                    VALUES (?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE $type = IF((VALUES($type) > $quantity) and (VALUES($type) - $quantity >= 0), VALUES($type) - $quantity, 0)";

or simply add the greater than condition other than checking for subtraction > 0

或者只是添加大于条件而不是检查减法> 0

$sql = "INSERT INTO stock (articles_id, date, units_in, units_out)
                    VALUES (?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE $type = IF(VALUES($type) >= $quantity, VALUES($type) - $quantity, 0)";

#1