MySQL VALUES() function not working with Codeigniter db->query()

Question

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.

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()

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?

LogicBlower · Accepted Answer · 2017-08-24 13:34:01Z

1

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

$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

$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)";

answered Aug 24, 2017 at 13:34

LogicBlower

1,3501 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

luis.ap.uyen Over a year ago

It works, thank you! How come my query worked if I directly used it in PhpMyAdmin, but not in Codeigniter?

Collectives™ on Stack Overflow

MySQL VALUES() function not working with Codeigniter db->query()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related