How to echo error message if value in table is 0 or less?

[dbalas]

Hi!

I have a problem with getting error on update...

What i have is - one table where i store my orders, and one table where i keep stock records. I managed to update the stock in my table with this piece of code but when i have for example 5 products in stock and i place order of 10, my stock goes -5 which is no gud :) How can i change that?

$sql="update parts set quantity=quantity-".$values["quantity"]." where part_number='".$values["part_number"]."'";

Thank you!

[pritaeas]

You can add quantity - $values["quantity"] >= 0 to the where clause, but then you don't get an error. What you describe is business logic and should be checked by you before doing the update.

[dbalas]

Hi!

Thank you for your reply. I added it and now on parts page quantity doesnt go below 0 although i had stock of 9 and i placed order of 10 it processed my order as valid. Stock in warehouse went from 9 to 0 and sales order was 10 as i placed it. this is the code i have now

function AfterAdd(&$values,&$keys,$inline) { $sql="update parts set quantity=quantity-".$values["quantity"]." >= 0 where part_number='".$values["part_number"]."'"; customQuery($sql);

Anny ideas?

Thank you!

[diafol]

You can either retrieve the qty of the part in the DB and check the required qty against that to see if it's valid, or an easy / possibly cheap way would be to check amount in the update WHERE clause and check whether it has been updated or not.

$r = mysql_query("UPDATE table SET ... WHERE ... AND qty >= $qty");
if(mysql_affected_rows()){
    echo "OK";
}else{
    echo "Not OK";
}

However, that's just for general update or not - a 0 affected rows could be due to anything else, not just qty < $qty.

[dbalas]

Thank you. It almost does the trick :) But as you said, it echos me Not OK but still places the order.

[diafol]

That shouldn't happen.

Do this:

echo "UPDATE table SET ... WHERE ... AND qty >= $qty";

Just before your query to see what's going on. If the $qty is greater than the qty field, it shouldn't update.

[dbalas]

Hi! Thank you for the effort... I played a bit with it and now when there is not enough products in stock, it doesnt reduce quantity so the stock control is ok but but still it places the invalid order... Also if the order is valid or invalid it always echos me the message "Order Was NOT Placed"... This is what i have now

$sql="update parts set quantity=quantity-".$values["quantity"]." where part_number='".$values["part_number"]."' AND quantity >= ".$values["quantity"]." "; 
if(mysql_affected_rows()){
echo "Order was placed.";
}else{
echo "Order Was NOT Placed";
}
customQuery($sql);

[dbalas]

Also i echoed the query and it this is it when the order is placed successfully (have 7 in stock, placed order for 1) and stock was affected:

Order Was NOT Placed update parts set quantity=quantity-1 where part_number='1-3554-23' AND quantity >= 1

and this is what happens when the order is place unseccessfully (have 6 products in stock, placed order for 60) and the stock was no affected:

Order Was NOT Placed update parts set quantity=quantity-60 where part_number='1-3554-23' AND quantity >= 60

[diafol]

SO it does work?

[dbalas]

Yes, but even if order is placed with stock above stock quantity, it goes through (stock stay the same but order is there on order page) and no matter if the order is placed or not, it echos that order was not placed...