December 29, 2011

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

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!