mysql update/replace syntax

[Westcoast Sheri]

To keep track of how many fruits my visitors buy, I use a mySQL database
(2 columns: "fruit" and "quantity").... so can we make these following
mySQL queries work somehow?

(visitor buys 5 apples):
replace into fruit_database set fruit = 'apple' , quantity = quantity +
5;

(visitor buys 7 apples):
replace into fruit_database set fruit = 'apple' , quantity = quantity +
7;

(visitor buys 1 grape):
replace into fruit_database set fruit = 'grape' , quantity = quantity +
1

Thanks!

[CJ Llewellyn]

On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:

CJ Llewellyn wrote:

"Westcoast Sheri" <sh*********@no spamun8nospam.c om> wrote in message
news:41******** *******@nospamu n8nospam.com...

> CJ Llewellyn wrote:
>
> > "Westcoast Sheri" <sh*********@no spamun8nospam.c om> wrote in message
> > news:41******** *******@nospamu n8nospam.com...
> > > To keep track of how many fruits my visitors buy, I use a mySQL

database

> > > (2 columns: "fruit" and "quantity").... so can we make these following
> > > mySQL queries work somehow?
> > >
> > > (visitor buys 5 apples):
> > > replace into fruit_database set fruit = 'apple' , quantity = quantity

+

> > > 5;
> > >
> > > (visitor buys 7 apples):
> > > replace into fruit_database set fruit = 'apple' , quantity = quantity

+

> > > 7;
> > >
> > > (visitor buys 1 grape):
> > > replace into fruit_database set fruit = 'grape' , quantity = quantity

+

> > > 1
> >
> > You are approching this from the wrong angle. You should be quering the
> > customer sales records and producing summary information from that.
> >
> > INSERT INTO sales (customer, product , qty) VALUES
> > ('$customer','$ fruit','$qty')
> >
> > SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
> > numsales
> >
> > http://dev.mysql.com/doc/mysql/en/GR...Functions.html
>
> No, I am wanting to *record* what visitor does. If they buy 5 apples, I

want

> mySQL to find the row with "apple" in it, and increment it by 5. If there

is

> *no* row with "apple", then create a row and enter a "5" there.

Yes you are. The above will tell you exactly how many apples or bananas have
been sold without resorting to increamenting a seperate table/record.

Further more, with a little creativity, you can find out things like, on
what day which fruit sells best. How much of each fruit do you sell each
month? Is there a peek period for selling oranges?

no no no. If visitor "a" buys 5 apples, then a "5" should be in the mySQL
table. But then, an hour later, if visitor "b" buys 3 more apples, then an "8"
should be in the mySQL table. What you are suggesting is that first there will
be a "5" in the table....then when visitor "b" buys 3 apples, there will then
be a "3" in the table. I really thought I worded my question very well.
Apparantly not. Sorry.

You question was understood. It's just your approach to the problem is
wrong.

If you have a table that holds

Apple 5
Orange 6
Banana 567

This will not tell you a lot. Only in total how much of each fruit you've
sold. Unless you reset the values every week, you'll not be able to spot
trends in people's buying thus purchasing more stock than is needed.

You have a table holding

Fred Smith 1/1/04 Apple 2
Fred Smith 1/1/04 Banana 3
John Jones 2/1/04 Apple 5

You can tell how many apples and bananas have been sold, by summing them
up.

[Westcoast Sheri]

CJ Llewellyn wrote:

On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:

CJ Llewellyn wrote:

"Westcoast Sheri" <sh*********@no spamun8nospam.c om> wrote in message
news:41******** *******@nospamu n8nospam.com...
> CJ Llewellyn wrote:
>
> > "Westcoast Sheri" <sh*********@no spamun8nospam.c om> wrote in message
> > news:41******** *******@nospamu n8nospam.com...
> > > To keep track of how many fruits my visitors buy, I use a mySQL
database
> > > (2 columns: "fruit" and "quantity").... so can we make these following
> > > mySQL queries work somehow?
> > >
> > > (visitor buys 5 apples):
> > > replace into fruit_database set fruit = 'apple' , quantity = quantity
+
> > > 5;
> > >
> > > (visitor buys 7 apples):
> > > replace into fruit_database set fruit = 'apple' , quantity = quantity
+
> > > 7;
> > >
> > > (visitor buys 1 grape):
> > > replace into fruit_database set fruit = 'grape' , quantity = quantity
+
> > > 1
> >
> > You are approching this from the wrong angle. You should be quering the
> > customer sales records and producing summary information from that.
> >
> > INSERT INTO sales (customer, product , qty) VALUES
> > ('$customer','$ fruit','$qty')
> >
> > SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
> > numsales
> >
> > http://dev.mysql.com/doc/mysql/en/GR...Functions.html
>
> No, I am wanting to *record* what visitor does. If they buy 5 apples, I
want
> mySQL to find the row with "apple" in it, and increment it by 5. If there
is
> *no* row with "apple", then create a row and enter a "5" there.

Yes you are. The above will tell you exactly how many apples or bananas have
been sold without resorting to increamenting a seperate table/record.

Further more, with a little creativity, you can find out things like, on
what day which fruit sells best. How much of each fruit do you sell each
month? Is there a peek period for selling oranges?

no no no. If visitor "a" buys 5 apples, then a "5" should be in the mySQL
table. But then, an hour later, if visitor "b" buys 3 more apples, then an "8"
should be in the mySQL table. What you are suggesting is that first there will
be a "5" in the table....then when visitor "b" buys 3 apples, there will then
be a "3" in the table. I really thought I worded my question very well.
Apparantly not. Sorry.

You question was understood. It's just your approach to the problem is
wrong.

If you have a table that holds

Apple 5
Orange 6
Banana 567

This will not tell you a lot. Only in total how much of each fruit you've
sold. Unless you reset the values every week, you'll not be able to spot
trends in people's buying thus purchasing more stock than is needed.

You have a table holding

Fred Smith 1/1/04 Apple 2
Fred Smith 1/1/04 Banana 3
John Jones 2/1/04 Apple 5

You can tell how many apples and bananas have been sold, by summing them
up.

Okay...I see you are putting a spin on things...and it is quite interesting. I
think you are saying, "ya you have an idea...but this one is better," and I think I
may agree. It is quite easy for me to create php code to make the "fred smith"
example table... but the question, now, is How do I do a "SUM" select statement
(e.g. something like "select SUM (quantity) from fruit_table where fruit = 'apple'"
or something like that?

[Westcoast Sheri]

Andy Hassall wrote:

On Sun, 29 Aug 2004 14:21:50 GMT, Westcoast Sheri
<sh*********@no spamun8nospam.c om> wrote:

Andy Hassall wrote:

On Sun, 29 Aug 2004 12:14:44 GMT, Westcoast Sheri
<sh*********@no spamun8nospam.c om> wrote:

> You are approching this from the wrong angle. You should be quering the
> customer sales records and producing summary information from that.
>
> INSERT INTO sales (customer, product , qty) VALUES
> ('$customer','$ fruit','$qty')
>
> SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
> numsales
>
> http://dev.mysql.com/doc/mysql/en/GR...Functions.html

No, I am wanting to *record* what visitor does. If they buy 5 apples, I want
mySQL to find the row with "apple" in it, and increment it by 5. If there is
*no* row with "apple", then create a row and enter a "5" there.

If a customer buys 5 apples, then a week later buys 3 apples, you've still
only got one row. Since you haven't given an indication that there's any table
recording each sale, that's what's raising concerns about your design.

If you really do want to do it like this, then:

(1) Lock the SALES table.
(2) Attempt an update, setting quantity = quantity + $n.
(3) Check mysql_affected_ rows(). If zero, do an insert instead.
(4) Unlock the SALES table.

The locking is necessary because other sessions might do the same operation,
causing a classic race condition leading to either a key violation or a lost
update.

Since you're trying to ensure the non-existence of a row, you can't row-level
lock a non-existent row, so you have to table lock, meaning this operation is
always serialised.

With the one-row-per-sale and then a SUM to derive the totals, these issues
don't arise.

when you say, "a SUM to derive the totals..." you mean to do the steps 1 thru 4
that you outlined above, right?

No, I was agreeing with CJ Llewellyn's suggestion above. The steps 1-4 are how
to implement your current design and original question where there is only a
running total of quantity in the table.

If so, I can [figure out how to] do that.
However, it looks like you might have suggested the step 1-4 thingy, and then
followed up by saying it is better to do something different (like each sale has
own line...e.g. apples = 4, apples = 7, apples = 9, and then using a PHP query to
select all rows w/ apples and add em up). right?

Yes, that's right.

Okay, I guess I'll pour over the literature to find how to select a "sum".... unless
of course, you would please type the line of code :-)

[Andy Hassall]

On Sun, 29 Aug 2004 22:30:34 GMT, Westcoast Sheri
<sh*********@no spamun8nospam.c om> wrote:

Okay, I guess I'll pour over the literature to find how to select a "sum".... unless
of course, you would please type the line of code :-)

CJ already posted it several messages back.

--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Michael Austin]

Westcoast Sheri wrote:

CJ Llewellyn wrote:

On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:

CJ Llewellyn wrote:
"Westcoas t Sheri" <sh*********@no spamun8nospam.c om> wrote in message
news:41**** ***********@nos pamun8nospam.co m...

>CJ Llewellyn wrote:
>
>
>>"Westcoas t Sheri" <sh*********@no spamun8nospam.c om> wrote in message
>>news:41** *************@n ospamun8nospam. com...
>>
>>>To keep track of how many fruits my visitors buy, I use a mySQL

database

>>>(2 columns: "fruit" and "quantity").... so can we make these following
>>>mySQL queries work somehow?
>>>
>>>(visit or buys 5 apples):
>>>replac e into fruit_database set fruit = 'apple' , quantity = quantity

+

>>>5;
>>>
>>>(visit or buys 7 apples):
>>>replac e into fruit_database set fruit = 'apple' , quantity = quantity

+

>>>7;
>>>
>>>(visit or buys 1 grape):
>>>replac e into fruit_database set fruit = 'grape' , quantity = quantity

+

>>>1
>>
>>You are approching this from the wrong angle. You should be quering the
>>custome r sales records and producing summary information from that.
>>
>>INSERT INTO sales (customer, product , qty) VALUES
>>('$custom er','$fruit','$ qty')
>>
>>SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
>>numsale s
>>
>>http://dev.mysql.com/doc/mysql/en/GR...Functions.html
>
>No, I am wanting to *record* what visitor does. If they buy 5 apples, I

want

>mySQL to find the row with "apple" in it, and increment it by 5. If there

is

>*no* row with "apple", then create a row and enter a "5" there.

Yes you are. The above will tell you exactly how many apples or bananas have
been sold without resorting to increamenting a seperate table/record.

Further more, with a little creativity, you can find out things like, on
what day which fruit sells best. How much of each fruit do you sell each
month? Is there a peek period for selling oranges?

no no no. If visitor "a" buys 5 apples, then a "5" should be in the mySQL
table. But then, an hour later, if visitor "b" buys 3 more apples, then an "8"
should be in the mySQL table. What you are suggesting is that first there will
be a "5" in the table....then when visitor "b" buys 3 apples, there will then
be a "3" in the table. I really thought I worded my question very well.
Apparantly not. Sorry.

You question was understood. It's just your approach to the problem is
wrong.

If you have a table that holds

Apple 5
Orange 6
Banana 567

This will not tell you a lot. Only in total how much of each fruit you've
sold. Unless you reset the values every week, you'll not be able to spot
trends in people's buying thus purchasing more stock than is needed.

You have a table holding

Fred Smith 1/1/04 Apple 2
Fred Smith 1/1/04 Banana 3
John Jones 2/1/04 Apple 5

You can tell how many apples and bananas have been sold, by summing them
up.

Okay...I see you are putting a spin on things...and it is quite interesting. I
think you are saying, "ya you have an idea...but this one is better," and I think I
may agree. It is quite easy for me to create php code to make the "fred smith"
example table... but the question, now, is How do I do a "SUM" select statement
(e.g. something like "select SUM (quantity) from fruit_table where fruit = 'apple'"
or something like that?

Wc Sheri,

As I stated in the previous thread, you need to have a better understanding of
database design BEFORE you write lots code and create databases that are of
little value outside of storing the data. Storing it is of no use unless you
can derive information from it "easily".

I certainly hope you are not creating a new table for EACH customer as seems to
be inferred by the statement "It is quite easy for me to create php code to make
the "fred smith" example table... ".

Create ONE table

Customer_ID (auto increment), txdate, Last_name, First_name, Item, Qty, Total

then in your PHP code

insert into customer values (nextval, date(), 'Thomas', 'Fred', 'Apple', 5, 2.30);

etc...

Now to find out what happened today:

select item,sum(qty),s um(total) from customer where item = 'Apple'
where txdate = date('somedates tring') group by item, qty, total;

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

[Gordon Burditt]

>> > You are approching this from the wrong angle. You should be quering the

> customer sales records and producing summary information from that.
This is a hospital kitchen. Recording Personally Identifiable
Information about a patient in a database makes that database legally
unusable for the purpose intended for it (what kind of food they
should order and how much they should keep on hand), and that's why
your predecessor is in jail now: violating HIPAA requirements.
> INSERT INTO sales (customer, product , qty) VALUES
> ('$customer','$ fruit','$qty')
>
> SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
> numsales
>
> http://dev.mysql.com/doc/mysql/en/GR...Functions.html
No, I am wanting to *record* what visitor does. If they buy 5 apples, I

want

mySQL to find the row with "apple" in it, and increment it by 5. If there

is

*no* row with "apple", then create a row and enter a "5" there.

It is possible to insert or update a row with one query:

INSERT INTO sales_summary SET product = 'apple', qty = 3
ON DUPLICATE KEY UPDATE qty = qty + 3;

This requires that the product column has a unique key on it.

It has the advantage that it's atomic: you don't have to do explicit
locking but you can't get fouled up by different ordering of requests.
(The problem with this kind of query is that you need a minimum
version of MySQL of about 4.1 (not sure exactly which version), and
I don't know that any other database accepts this syntax.).

You might be able to add a "date" column to get daily totals, but
only if you can convince management that asking the judge for
permission is worth the risk of having him prohibit the existence
of the database entirely.

I use this sort of thing a lot with email white/black lists. You
want to record, say, the sender, the number of emails from this
sender to good addresses (but NOT what the good addresses are), the
number of emails from this sender to bad addresses (but NOT what
the bad addresses are), and the time of the latest email from that
sender. If there's no entry, add one. If there is an entry,
increment one of the counters. There is a high probability of
simultaneous SPAMs from the same sender arriving at the same time.
You DO NOT want to record each email: this allows spammers to
conduct a denial-of-service attack against you by running your
database out of disk space.

Another process can later classify the sender as one to be blocked
or not, in part based on the assumption that a sender who sends
a lot of mail to mostly invalid addresses is a spammer. Other
fields can store manual settings.
Yes you are. The above will tell you exactly how many apples or bananas have
been sold without resorting to increamenting a seperate table/record.
Yes, but you use a lot more storage, and the personally identifiable
information in it means you're not allowed to do any SELECTs at
all, and has a high probability of getting the whole project cancelled.
Further more, with a little creativity, you can find out things like, on
what day which fruit sells best. How much of each fruit do you sell each
month? Is there a peek period for selling oranges?

Keeping this kind of marketing information around can kill your business
if it gets out that you're keeping it, say, because someone managed
to steal it.

Gordon L. Burditt

[CJ Llewellyn]

"Gordon Burditt" <go***********@ burditt.org> wrote in message
news:cg******** @library1.airne ws.net...

> You are approching this from the wrong angle. You should be quering the > customer sales records and producing summary information from that.

This is a hospital kitchen. Recording Personally Identifiable
Information about a patient in a database makes that database legally
unusable for the purpose intended for it (what kind of food they
should order and how much they should keep on hand), and that's why
your predecessor is in jail now: violating HIPAA requirements.

-snip-

Nobody has said that this is a hospital kitchen. HIPAA requirements do not
apply outside of the USA. HIPAA only applies to hospital information.
Nothing in the example mandates that personal information is to be kept.
Keeping this kind of marketing information around can kill your business
if it gets out that you're keeping it, say, because someone managed
to steal it.

Every single in the company in the world keeps this type of information.
They have to, otherwise they have great difficulty in managing their
accounts, stock order processes, producing statutory information i.e. tax
returns and providing good customer service.

[Westcoast Sheri]

Michael Austin wrote:

Westcoast Sheri wrote:

CJ Llewellyn wrote:

On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:
CJ Llewellyn wrote:
>"Westcoas t Sheri" <sh*********@no spamun8nospam.c om> wrote in message
>news:41**** ***********@nos pamun8nospam.co m...
>
>>CJ Llewellyn wrote:
>>
>>
>>>"Westcoas t Sheri" <sh*********@no spamun8nospam.c om> wrote in message
>>>news:41** *************@n ospamun8nospam. com...
>>>
>>>>To keep track of how many fruits my visitors buy, I use a mySQL
>
>database
>
>>>>(2 columns: "fruit" and "quantity").... so can we make these following
>>>>mySQL queries work somehow?
>>>>
>>>>(visit or buys 5 apples):
>>>>replac e into fruit_database set fruit = 'apple' , quantity = quantity
>
>+
>
>>>>5;
>>>>
>>>>(visit or buys 7 apples):
>>>>replac e into fruit_database set fruit = 'apple' , quantity = quantity
>
>+
>
>>>>7;
>>>>
>>>>(visit or buys 1 grape):
>>>>replac e into fruit_database set fruit = 'grape' , quantity = quantity
>
>+
>
>>>>1
>>>
>>>You are approching this from the wrong angle. You should be quering the
>>>custome r sales records and producing summary information from that.
>>>
>>>INSERT INTO sales (customer, product , qty) VALUES
>>>('$custom er','$fruit','$ qty')
>>>
>>>SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
>>>numsale s
>>>
>>>http://dev.mysql.com/doc/mysql/en/GR...Functions.html
>>
>>No, I am wanting to *record* what visitor does. If they buy 5 apples, I
>
>want
>
>>mySQL to find the row with "apple" in it, and increment it by 5. If there
>
>is
>
>>*no* row with "apple", then create a row and enter a "5" there.
>
>Yes you are. The above will tell you exactly how many apples or bananas have
>been sold without resorting to increamenting a seperate table/record.
>
>Further more, with a little creativity, you can find out things like, on
>what day which fruit sells best. How much of each fruit do you sell each
>month? Is there a peek period for selling oranges?

no no no. If visitor "a" buys 5 apples, then a "5" should be in the mySQL
table. But then, an hour later, if visitor "b" buys 3 more apples, then an "8"
should be in the mySQL table. What you are suggesting is that first there will
be a "5" in the table....then when visitor "b" buys 3 apples, there will then
be a "3" in the table. I really thought I worded my question very well.
Apparantly not. Sorry.

You question was understood. It's just your approach to the problem is
wrong.

If you have a table that holds

Apple 5
Orange 6
Banana 567

This will not tell you a lot. Only in total how much of each fruit you've
sold. Unless you reset the values every week, you'll not be able to spot
trends in people's buying thus purchasing more stock than is needed.

You have a table holding

Fred Smith 1/1/04 Apple 2
Fred Smith 1/1/04 Banana 3
John Jones 2/1/04 Apple 5

You can tell how many apples and bananas have been sold, by summing them
up.

Okay...I see you are putting a spin on things...and it is quite interesting. I
think you are saying, "ya you have an idea...but this one is better," and I think I
may agree. It is quite easy for me to create php code to make the "fred smith"
example table... but the question, now, is How do I do a "SUM" select statement
(e.g. something like "select SUM (quantity) from fruit_table where fruit = 'apple'"
or something like that?

Wc Sheri,

As I stated in the previous thread, you need to have a better understanding of
database design BEFORE you write lots code and create databases that are of
little value outside of storing the data. Storing it is of no use unless you
can derive information from it "easily".

I certainly hope you are not creating a new table for EACH customer as seems to
be inferred by the statement "It is quite easy for me to create php code to make
the "fred smith" example table... ".

Create ONE table

Customer_ID (auto increment), txdate, Last_name, First_name, Item, Qty, Total

then in your PHP code

insert into customer values (nextval, date(), 'Thomas', 'Fred', 'Apple', 5, 2.30);

etc...

Now to find out what happened today:

select item,sum(qty),s um(total) from customer where item = 'Apple'
where txdate = date('somedates tring') group by item, qty, total;

Thank you for the code. I was actually creating a separate table for not only each
customer, but each value.
....(...just kidding, I just have one table :-)

[Westcoast Sheri]

Gordon Burditt wrote:

> You are approching this from the wrong angle. You should be quering the
> customer sales records and producing summary information from that.
This is a hospital kitchen. Recording Personally Identifiable
Information about a patient in a database makes that database legally
unusable for the purpose intended for it (what kind of food they
should order and how much they should keep on hand), and that's why
your predecessor is in jail now: violating HIPAA requirements.
INSERT INTO sales (customer, product , qty) VALUES
> ('$customer','$ fruit','$qty')
>
> SELECT fruit, sum(qty) AS numsales FROM sales GROUP BY fruit ORDER BY
> numsales
>
> http://dev.mysql.com/doc/mysql/en/GR...Functions.html

No, I am wanting to *record* what visitor does. If they buy 5 apples, I

want

mySQL to find the row with "apple" in it, and increment it by 5. If there

is

*no* row with "apple", then create a row and enter a "5" there.

It is possible to insert or update a row with one query:

INSERT INTO sales_summary SET product = 'apple', qty = 3
ON DUPLICATE KEY UPDATE qty = qty + 3;

.....actually this is the first code I ever tried (because it's exactly what I
wanted in the first place!), and kept getting errors! But then I realized that
perhaps my server was not using the mySQL version that allowed this ("...blah
blah blah syntax error near "ON DUPLICATE KEY"). That is the perfect line of
code, though! Thanks!!!

[ercherry]

I had the exact same problem as the original poster, and I didn't want all of the excess data that was suggested as imperative for collection by the responders. My problem was increased by the fact that I couldn't use that ON DUPLICATE KEY UPDATE line of code due to lack of support. (SO SAD)

So here is what I did...

$query="SELECT c FROM Table WHERE a= "somevalue" ;
$result =mysql_query($q uery)OR die("error 3 - query failed".mysql_e rror());
$myrow = mysql_fetch_arr ay($result);
$3val = $myrow["c"];
++$3val;

$query = "REPLACE INTO Table (a, b, c) VALUES ($aval, $2val, $3val)";
$result =mysql_query($q uery)OR die("error 3 - query failed".mysql_e rror());

....

I tried to simplify it a bit for reading here...

I'm trying to increment a value before it gets replaced. The above works, but strangely, the $3val gets incremented twice.

It could be happening somewhere else in the code, because I am using Flash remoting. It's a crazy little bug to track down, but I have a feeling that it is the REPLACE command that is doing it to me. I'm at a loss.

I also tried stripping the

++$3val;

and instead putting it directly into the query

$query = "REPLACE INTO Table (a, b, c) VALUES ($aval, $2val, ($3val + 1))";
$result =mysql_query($q uery)OR die("error 3 - query failed".mysql_e rror());