mySQL add/subtract quantities?

[Westcoast Sheri]

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("up date fruit_table set quantity_left = 92 where fruit =
'apple'");

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!

[Jochen Daum]

Re: mySQL add/subtract quantities?

Hi,

On Thu, 26 Aug 2004 23:16:23 GMT, Westcoast Sheri
<sheri_deb88@no spamun8nospam.c om> wrote:
[color=blue]
>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[/color]

I think int or decimal should be the right column for a numerical
datatype.[color=blue]
>
>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("u pdate fruit_table set quantity_left = 92 where fruit =
>'apple'");[/color]

Yes, or

update fruit_table set quantity_left = quantity_left - 8 where fruit =
'apple'

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.
[color=blue]
>
>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_lef t int(3) unsigned zerofill NOT NULL default '00'
>);[/color]

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.

http://sourceforge.net/projects/phpdbedittk/

[Michael Austin]

Re: mySQL add/subtract quantities?

Westcoast Sheri wrote:
[color=blue]
> 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[/color]

Use a numeric datatype such as INT.
[color=blue]
>
> 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("up date fruit_table set quantity_left = 92 where fruit =
> 'apple'");[/color]

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.
[color=blue]
>
> 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!
>[/color]


--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

[Westcoast Sheri]

Re: mySQL add/subtract quantities?

> One of the problems with PHP is the fact that each database operation is ATOMIC.[color=blue]
> - 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.[/color]

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

$link = mysql_connect(' localhost','use r','pass');
mysql_select_db ('database',$li nk);
$increment = "update fruit_table set fruit = fruit - ".$number_sold. " where fruit =
'".$type_of_fru it."'";
mysql_query($in crement,$link);

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

$link = mysql_connect(' localhost','use r','pass');
mysql_select_db ('database',$li nk);
// 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

[Michael Austin]

Re: mySQL add/subtract quantities?

Westcoast Sheri wrote:
[color=blue][color=green]
>>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.[/color]
>
>
> What does that mean? Am I correct in now assuming that you mean that this is bad:
>[/color]

It means that you need to read up on what constitutes a transaction to insure
data integrity.
[color=blue]
> $link = mysql_connect(' localhost','use r','pass');
> mysql_select_db ('database',$li nk);
> $increment = "update fruit_table set fruit = fruit - ".$number_sold. " where fruit =
> '".$type_of_fru it."'";
> mysql_query($in crement,$link);
>
> .... and that it would be better to do this:
>
> $link = mysql_connect(' localhost','use r','pass');
> mysql_select_db ('database',$li nk);
> // 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
>[/color]

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


Using your code:

user1:
query - get current quantity of apples (=100)

at the same time

user2:
query - get current quantity of apples (still = 100)

user1
calculate 100 - 8
user2
calculate 100 - 6
user1
updates quantity (=92)
user2
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 "transactio n" 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("BE GIN|COMMIT|ROLL BACK"), 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:
http://php.weblogs.com/discuss/msgReader$1446? mode=topic
(Click where it says Part2, I can't put the direct URL here because it is too long)

Then in PHP you use commands like:

mysql_query("BE GIN");
mysql_query("CO MMIT");
mysql_query("RO LLBACK");

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. Http://www.firstdbasource.com/donations.html
:)