mySQL add/subtract quantities?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Two mySQL Questions:

1.) how to numerically increase or decrease the value of a mySQL column

2.) what is the best column structure (e.g. "varchar" or "char" etc.)
for speediest increasing/decreasing

Here is the idea:

I sell fruit. I start with 100 apples, 100 bananas, and 100 grapes.
Visitor purchases 8 apples so now I have 92 of them left. What is the
exact mysql query statement  that I would use (the following is wrong,
but gives idea what I'm trying to do):

mysql_query("update fruit_table set quantity_left = 92 where fruit =

Also, what would be the best table structure for the quantity part. Is
this the best?
CREATE TABLE fruit_table (
fruit tinytext NOT NULL,
quantity_left int(3) unsigned zerofill NOT NULL default '00'

Thank you!

Re: mySQL add/subtract quantities?


On Thu, 26 Aug 2004 23:16:23 GMT, Westcoast Sheri

Quoted text here. Click to load it

I think int or decimal should be the right column for a numerical
Quoted text here. Click to load it

Yes, or

update fruit_table set quantity_left = quantity_left - 8 where fruit =

This has two advantages:

1. no need for locking, because if two processes decrease your apple
amount, you might have a problem with

- a reads quantity
- b reads quantity
- a updates quantity
- b updats quantity

2. No need to read it.

Quoted text here. Click to load it

Probably fruit should be varchar instead. I'm also not a big fan of
zerofill, as IMO this should be done in the client app.

You might also want to store your single transactions. That way you
can calculate average apple purchase and maximum apple purchase (eg.
for limiting amounts per person, if you have apples on offer)

You could also calculate the amount on stock from the transactions,
but that wil get slow fast.

HTH, Jochen
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces. /

Re: mySQL add/subtract quantities?

Westcoast Sheri wrote:

Quoted text here. Click to load it

Use a numeric datatype such as INT.

Quoted text here. Click to load it

One of the problems with PHP is the fact that each database operation is ATOMIC.
- Stands alone.  Because of this, I would build a database function that
derives the current value of quantity_left and decrements and saves the new
value.  Otherwise, if you have more than one "cashier" then the possiblity of
"missing" a transaction increases significantly.

Quoted text here. Click to load it

Michael Austin.
Consultant - Available.
Donations welcomed.

Re: mySQL add/subtract quantities?

Quoted text here. Click to load it

What does that mean? Am I correct in now assuming that you mean that this is bad:

$link = mysql_connect('localhost','user','pass');
$increment = "update fruit_table set fruit = fruit - ".$number_sold." where
fruit =

..... and that it would be better to do this:

$link = mysql_connect('localhost','user','pass');
// do code to select (obtain) quantity from mysql database
// then use PHP to decrement the quantity
// then use a mysql statement to insert the new quantity into database

Re: mySQL add/subtract quantities?

Westcoast Sheri wrote:

Quoted text here. Click to load it

It means that you need to read up on what constitutes a transaction to insure
data integrity.

Quoted text here. Click to load it

no.  It means that in your code you have 3 seperate and distinct transactions
that could result in erroneous data.

Using your code:

        query - get current quantity of apples (=100)

    at the same time
        query - get current quantity of apples (still = 100)

        calculate 100 - 8
        calculate 100 - 6
        updates quantity (=92)
        updates quantity (=94)
    user1 exit
    user2 exit

Because EACH is in a seperate TRANSACTION you now have the possibility of being
off by 8.

What if you happen to make a bank transaction and this occurred while you were
making it?   -- Well in this case you would win, but what one was adding money
and another subracting it... you would come up short.

If you do not understand what constitutes a "transaction" in a database and how
they are implemented in a "scripting" language like PHP, you cannot write code
that will work 100% of the time.

Because with PHP, each statement is considered a seperate transaction - unless
you use mysql_query("BEGIN|COMMIT|ROLLBACK"), the locking mechanisms that
prevent this senerio are NOT engaged.  Or by using a DATABASE/MySQL user defined
  FUNCTION that:
    gets the current value
    increment or decrements it
    updates the current with the new
withing a SINGLE transaction, then you may end up with the wrong quanitities or
dollar values...

 From the PHP docs:
"Regarding transactions, you must use a recent MySQL version which supports
InnoDB tables. you should read the mysql manual (the part about Innodb tables,
section 7.5) and configure your server to use them.
Some reading about how it works: $1446?mode=topic
(Click where it says Part2, I can't put the direct URL here because it is too

Then in PHP you use commands like:


You must make sure that you convert your existing tables to innodb or create new
ones: CREATE TABLE (...) type=innodb;"

This will ensure that the 3 statements are a part of the same transaction, not
seperate transactions.

Michael Austin.
System Analyst and DBA
Donations welcomed.

Site Timeline