SQL syntax queries error

[jnewtcomp]

Having issues with Purchase order view PHP Query errors. Issues listed below.

Purchase_Order_view.php
UPDATE Inventory i set i.Stock_QTY = i.Stock_QTY + where i.InventoryID=1245
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where i.InventoryID=1245' at line 1

Purchase_Order_view.php
UPDATE Inventory i set i.Stock_QTY = i.Stock_QTY + where i.InventoryID=1003
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where i.InventoryID=1003' at line 1

When I set a purchase order as received I have to manually update in stock qty. if i enter the purchase order then close at the same time, everything works. when i 0 out a saved purchase order entry it changes the qty. Any Ideas?

Thank

function Purchase_Order_before_insert(&$data, $memberInfo, &$args){

return TRUE;
}

function Purchase_Order_after_insert($data, $memberInfo, &$args){
sqlvalue("UPDATE Inventory i set i.Stock_QTY = i.Stock_QTY + {$data['Qty_Received']} where i.InventoryID={$data['Part_Number']}");

return TRUE;

}

function Purchase_Order_before_update(&$data, $memberInfo, &$args){

$new_value=sqlvalue("SELECT ({$data['Qty_Received']} - Qty_Received) as qty from Purchase_Order where Purchase_OrderID = {$data['selectedID']} ");
if ($new_value <> 0) {
sqlvalue("UPDATE Inventory i set i.Stock_QTY = i.Stock_QTY + {$new_value} where i.InventoryID={$data['Part_Number']}"); }

return TRUE;
}


function Purchase_Order_after_update($data, $memberInfo, &$args){

return TRUE;
}

function Purchase_Order_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

[saymaad]

in the query you shared there is no value after the plus sign, that is main issue:

Code: Select all

UPDATE Inventory i set i.Stock_QTY = i.Stock_QTY       +       where i.InventoryID=1245

You can avoid this by checking if 'Qty_Received' exists and is a valid number:

Code: Select all

$qtyReceived = isset($data['Qty_Received']) && is_numeric($data['Qty_Received']) ? $data['Qty_Received'] : 0;

sql("UPDATE Inventory i SET i.Stock_QTY = i.Stock_QTY + {$qtyReceived} WHERE i.InventoryID = i.InventoryID={$data['Part_Number']}",$eo);

Try this for other parts of the hook as well, if need be, you can also save the values to a file and debug.