mysqli_affected_rows, mysql subtract 2 columns error
Question by Max
I have a column I want to update with results of the difference between 2 mysql columns and count how many rows were affected. In my case it can only be 1. This is the mysql query I am using which is not consistent at all
$connection->query("UPDATE items SET Quantity_Available = Quantity - Quantity_Committed WHERE Item_ID = '$itemid'");
if($count=$connection->affected_rows!=1){echo $count;die('makassi');}
If I replace the Quantity_Committed with a numeric value, I get what I want i.e the code continues. However if I leave it as it is, I get the proper $count figure(1) but it also fails by echoing ‘makassi’ which it shouldn’t.
Is this an improper way of subtracting 2 mysql columns or is this a bug in the php mysqli api??
This is really baffling to me!! Help please
Answer by Starx
This is a bad practice what you are trying to do. If a column in the database is derived from another column already in the column. Then such is create redundancy is the database. All a database should be normalized as much as possible. Please read here about data normalization.
Whatever you are trying to do can be achieved in a much better way. Like
Filtering the records
SELECT * FROM items WHERE Quantity - Quantity_Column > 5
Or, retrieving the quantity available.
SELECT (Quantify - Quantity_Column) as `Quality_Available` from items
