Can someone help me with my code…if i run a single code it can run without any error but if i put as below it shows this error…
Failed to execute SQL. Error: 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 ‘AND item_stock=)’ at line 1 (1064)
// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
// Enter your code here
// To cancel, set return value to FALSE
$stocksqty = ExecuteScalar(“SELECT * FROM stocklist WHERE (code_name=”.$rsnew[“item_code”]." AND item_stock=“.$rsnew[“stock_out”].”)“);
$requestqty = $rsnew[‘stock_out’];
$stocksqty2 = Execute(“SELECT * FROM stocklist WHERE (code_name=”.$rsnew[“item_code2”].” AND item_stock=“.$rsnew[“stock_out2”].”)");
$requestqty2 = $rsnew[‘stock_out2’];
if (intval($requestqty) > intval($stocksqty)) {
$this->CancelMessage = “Sorry, you can not request a quantity(”.$requestqty.“) which is greater than Stock quantity(”.$stocksqty.“)!”;
return FALSE;
}elseif (intval($requestqty2) > intval($stocksqty2)) {
$this->CancelMessage = “Sorry, you can not request a quantity(”.$requestqty2.“) which is greater than Stock quantity(”.$stocksqty2.“)!”;
return FALSE;
i’m not sure but i think you may have a problem of quote (') or double quote (") sometime.
sometimes : $rsnew[“stock_out”]
sometimes : $rsnew[‘stock_out’]
moreover, if code_name is a string field, you forgot the quote in your concate !
i already changed it…nevermind i’ll give it another try. Thanks…will update the results later.
amiens80 wrote:
hi,
i’m not sure but i think you may have a problem of quote (') or double
quote (") sometime.
sometimes : $rsnew[“stock_out”]
sometimes : $rsnew[‘stock_out’]
moreover, if code_name is a string field, you forgot the quote in your
concate !
$stocksqty = ExecuteScalar(“SELECT * FROM stocklist WHERE (code_name='”.$rsnew[“item_code”].“’ AND item_stock=”.$rsnew[“stock_out”].“)”);
$requestqty = $rsnew[‘stock_out’];
$stocksqty2 = Execute(“SELECT * FROM stocklist WHERE (code_name='”.$rsnew[“item_code2”].“’ AND item_stock=”.$rsnew[“stock_out2”].“)”);
$requestqty2 = $rsnew[‘stock_out2’];
$stocksqty = ExecuteScalar(“SELECT * FROM stocklist WHERE
(code_name='”.$rsnew[“item_code”].“’ AND
item_stock=”.$rsnew[“stock_out”].“)”);
$requestqty = $rsnew[‘stock_out’];
$stocksqty2 = Execute(“SELECT * FROM stocklist WHERE
(code_name='”.$rsnew[“item_code2”].“’ AND
item_stock=”.$rsnew[“stock_out2”].“)”);
$requestqty2 = $rsnew[‘stock_out2’];
Still cannot…it give me this error…
Failed to execute SQL. Error: 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 ‘)’ at line 1 (1064)
i’m not sure but i think you may have a problem of quote (') or double
quote (") sometime.
sometimes : $rsnew[“stock_out”]
sometimes : $rsnew[‘stock_out’]
moreover, if code_name is a string field, you forgot the quote in your
concate !
Done changing the quotes but it shows the same results. Not sure what is wrong. If i run only the first code without the 2nd quote it ran smoothly. Hum
This code:
$stocksqty2 = Execute(“SELECT * FROM stocklist WHERE (code_name=”.$rsnew[“item_code2”]." AND item_stock=“.$rsnew[“stock_out2”].”)");
should be:
$stocksqty2 = ExecuteScalar(“SELECT * FROM stocklist WHERE (code_name='”.$rsnew[“item_code2”].“’ AND item_stock=”.$rsnew[“stock_out2”].“)”);
Since you want to get a single value (scalar) from SELECT SQL, then make sure you do not use * to get from the certain field. You should use SELECT FROM …
This code:
$stocksqty2 = Execute(“SELECT * FROM stocklist WHERE (code_name=”.$rsnew[“item_code2”]."
AND item_stock=“.$rsnew[“stock_out2”].”)");
should be:
$stocksqty2 = ExecuteScalar(“SELECT * FROM stocklist WHERE (code_name='”.$rsnew[“item_code2”].“’
AND item_stock=”.$rsnew[“stock_out2”].“)”);
Since you want to get a single value (scalar) from SELECT SQL, then make sure you
do not use * to get from the certain field. You should use SELECT FROM
…
Ok…is there a way that we can terminate the first if statement with ‘End if’ and then start over again for the next Executescalar? By doing the above I know that all fields needed to be filled with value, but what if they only filled the first 2 fields which is (item_code and stock_out) and they left the 2nd field BLANK (item_code2 and stock_out2) ?
It’s good to have general_log = ON during development.
By doing so you can always examine and check syntax error of fired queries into db-machine.
Google “enable mysql general_log”
For a NULL user-input, you can check the condition,
if ($rsnew[“AField”]) { // user-input is not null
// fire query containing $rsnew[“AField”]
}
if ($rsnew[“item_code”] && $rsnew[“stock_out”]) { // user-input for BOTH field is not null
$stocksqty = ExecuteScalar(“SELECT * FROM stocklist WHERE (code_name=”.$rsnew[“item_code”]." AND item_stock=“.$rsnew[“stock_out”].”)");
$requestqty = $rsnew[‘stock_out’];
if (intval($requestqty) > intval($stocksqty)) {
$this->CancelMessage = “Sorry, you can not request a quantity(”.$requestqty.“) which is greater than Stock quantity(”.$stocksqty.“)!”;
return FALSE;
}
if ($rsnew[“item_code”] && $rsnew[“stock_out”]) { // user-input for BOTH field is
not null
You’d better use:
if (!empty($rsnew[“item_code”]) && !empty($rsnew[“stock_out”])) { // user-input for BOTH field is not empty (not empty = not null or not empty/blank string)
The only difference between if ($var) and if (empty($var)) is that empty($var) will also check if $var is exist or not.
As for the case of $rsnew array the $var is already exist.
if (!empty($rsnew[“item_code”]) && !empty($rsnew[“stock_out”])) { // user-input for BOTH field is not empty (not empty = not null or not empty/blank string)
$stocksqty = ExecuteScalar(“SELECT item_stock FROM stocklist WHERE code_name=”.$rsnew[“item_code”]." AND item_stock>=“.$rsnew[“stock_out”].”");
$requestqty = $rsnew[‘stock_out’];
if (intval($requestqty) > intval($stocksqty)) {
$this->CancelMessage = “Insufficient stock”;
return FALSE;
} else {
return TRUE;
}
}
if (!empty($rsnew[“item_code2”]) && !empty($rsnew[“stock_out2”])) { // user-input for BOTH field is not empty (not empty = not null or not empty/blank string)
$stocksqty2 = ExecuteScalar(“SELECT item_stock FROM stocklist WHERE code_name=”.$rsnew[“item_code2”]." AND item_stock>=“.$rsnew[“stock_out2”].”");
$requestqty2 = $rsnew[‘stock_out2’];
Above is my current codes. My problem right now is, if the first codes that involve stocksqty and requestqty, it will ran smoothly without error but if I fill both rows, if one value is true and the other is false both value will still be added (it will return true). I have another 3 set of codes to write (til item_code5 and stock_out5)…any ideas?