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!

[Steve]

Re: mysql update/replace syntax

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

are you after basic sql syntax?

update fruit_database set quantity = quantity + 5 where fruit = 'apple'

are you after how to access mysql from php?

http://www.php.net

Steve

[CJ Llewellyn]

Re: mysql update/replace syntax

"Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
news:41315FCC.A FFCF91D@nospamu n8nospam.com...[color=blue]
> 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[/color]

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/GROUP-BY-Functions.html

[Westcoast Sheri]

Re: mysql update/replace syntax

CJ Llewellyn wrote:
[color=blue]
> "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
> news:41315FCC.A FFCF91D@nospamu n8nospam.com...[color=green]
> > 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[/color]
>
> 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[/color]

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.

[Westcoast Sheri]

Re: mysql update/replace syntax

Steve wrote:
[color=blue]
> Westcoast Sheri wrote:[color=green]
> > 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!
> >[/color]
>
> are you after basic sql syntax?
>
> update fruit_database set quantity = quantity + 5 where fruit = 'apple'[/color]

That only works if there is a row with "apple" there already..

Again, 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.

[Andy Hassall]

Re: mysql update/replace syntax

On Sun, 29 Aug 2004 12:14:44 GMT, Westcoast Sheri
<sheri_deb88@no spamun8nospam.c om> wrote:
[color=blue][color=green]
>> 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[/color]
>
>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.[/color]

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.

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

[CJ Llewellyn]

Re: mysql update/replace syntax

"Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
news:4131C87F.5 BF798C7@nospamu n8nospam.com...[color=blue]
> CJ Llewellyn wrote:
>[color=green]
> > "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
> > news:41315FCC.A FFCF91D@nospamu n8nospam.com...[color=darkred]
> > > To keep track of how many fruits my visitors buy, I use a mySQL[/color][/color][/color]
database[color=blue][color=green][color=darkred]
> > > (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[/color][/color][/color]
+[color=blue][color=green][color=darkred]
> > > 5;
> > >
> > > (visitor buys 7 apples):
> > > replace into fruit_database set fruit = 'apple' , quantity = quantity[/color][/color][/color]
+[color=blue][color=green][color=darkred]
> > > 7;
> > >
> > > (visitor buys 1 grape):
> > > replace into fruit_database set fruit = 'grape' , quantity = quantity[/color][/color][/color]
+[color=blue][color=green][color=darkred]
> > > 1[/color]
> >
> > 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[/color]
>
> No, I am wanting to *record* what visitor does. If they buy 5 apples, I[/color]
want[color=blue]
> mySQL to find the row with "apple" in it, and increment it by 5. If there[/color]
is[color=blue]
> *no* row with "apple", then create a row and enter a "5" there.[/color]

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?

[Westcoast Sheri]

Re: mysql update/replace syntax

CJ Llewellyn wrote:
[color=blue]
> "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
> news:4131C87F.5 BF798C7@nospamu n8nospam.com...[color=green]
> > CJ Llewellyn wrote:
> >[color=darkred]
> > > "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
> > > news:41315FCC.A FFCF91D@nospamu n8nospam.com...
> > > > To keep track of how many fruits my visitors buy, I use a mySQL[/color][/color]
> database[color=green][color=darkred]
> > > > (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[/color][/color]
> +[color=green][color=darkred]
> > > > 5;
> > > >
> > > > (visitor buys 7 apples):
> > > > replace into fruit_database set fruit = 'apple' , quantity = quantity[/color][/color]
> +[color=green][color=darkred]
> > > > 7;
> > > >
> > > > (visitor buys 1 grape):
> > > > replace into fruit_database set fruit = 'grape' , quantity = quantity[/color][/color]
> +[color=green][color=darkred]
> > > > 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[/color]
> >
> > No, I am wanting to *record* what visitor does. If they buy 5 apples, I[/color]
> want[color=green]
> > mySQL to find the row with "apple" in it, and increment it by 5. If there[/color]
> is[color=green]
> > *no* row with "apple", then create a row and enter a "5" there.[/color]
>
> 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?[/color]

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.

[Westcoast Sheri]

Re: mysql update/replace syntax

Andy Hassall wrote:
[color=blue]
> On Sun, 29 Aug 2004 12:14:44 GMT, Westcoast Sheri
> <sheri_deb88@no spamun8nospam.c om> wrote:
>[color=green][color=darkred]
> >> 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[/color]
> >
> >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.[/color]
>
> 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.[/color]

when you say, "a SUM to derive the totals..." you mean to do the steps 1 thru 4
that you outlined above, right? 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?

[Andy Hassall]

Re: mysql update/replace syntax

On Sun, 29 Aug 2004 14:21:50 GMT, Westcoast Sheri
<sheri_deb88@no spamun8nospam.c om> wrote:
[color=blue]
>Andy Hassall wrote:
>[color=green]
>> On Sun, 29 Aug 2004 12:14:44 GMT, Westcoast Sheri
>> <sheri_deb88@no spamun8nospam.c om> wrote:
>>[color=darkred]
>>>> 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.[/color]
>>
>> 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.[/color]
>
>when you say, "a SUM to derive the totals..." you mean to do the steps 1 thru 4
>that you outlined above, right?[/color]

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

Yes, that's right.

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

[CJ Llewellyn]

Re: mysql update/replace syntax

On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:
[color=blue]
> CJ Llewellyn wrote:
>[color=green]
>> "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
>> news:4131C87F.5 BF798C7@nospamu n8nospam.com...[color=darkred]
>> > CJ Llewellyn wrote:
>> >
>> > > "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
>> > > news:41315FCC.A FFCF91D@nospamu n8nospam.com...
>> > > > To keep track of how many fruits my visitors buy, I use a mySQL[/color]
>> database[color=darkred]
>> > > > (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[/color]
>> +[color=darkred]
>> > > > 5;
>> > > >
>> > > > (visitor buys 7 apples):
>> > > > replace into fruit_database set fruit = 'apple' , quantity = quantity[/color]
>> +[color=darkred]
>> > > > 7;
>> > > >
>> > > > (visitor buys 1 grape):
>> > > > replace into fruit_database set fruit = 'grape' , quantity = quantity[/color]
>> +[color=darkred]
>> > > > 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[/color]
>> want[color=darkred]
>> > mySQL to find the row with "apple" in it, and increment it by 5. If there[/color]
>> is[color=darkred]
>> > *no* row with "apple", then create a row and enter a "5" there.[/color]
>>
>> 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?[/color]
>
> 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.[/color]

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]

Re: mysql update/replace syntax

CJ Llewellyn wrote:
[color=blue]
> On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:
>[color=green]
> > CJ Llewellyn wrote:
> >[color=darkred]
> >> "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
> >> news:4131C87F.5 BF798C7@nospamu n8nospam.com...
> >> > CJ Llewellyn wrote:
> >> >
> >> > > "Westcoast Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
> >> > > news:41315FCC.A FFCF91D@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?[/color]
> >
> > 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.[/color]
>
> 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.
>[/color]

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]

Re: mysql update/replace syntax

Andy Hassall wrote:
[color=blue]
> On Sun, 29 Aug 2004 14:21:50 GMT, Westcoast Sheri
> <sheri_deb88@no spamun8nospam.c om> wrote:
>[color=green]
> >Andy Hassall wrote:
> >[color=darkred]
> >> On Sun, 29 Aug 2004 12:14:44 GMT, Westcoast Sheri
> >> <sheri_deb88@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.[/color]
> >
> >when you say, "a SUM to derive the totals..." you mean to do the steps 1 thru 4
> >that you outlined above, right?[/color]
>
> 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.
>[color=green]
> >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?[/color]
>
> Yes, that's right.[/color]

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]

Re: mysql update/replace syntax

On Sun, 29 Aug 2004 22:30:34 GMT, Westcoast Sheri
<sheri_deb88@no spamun8nospam.c om> wrote:
[color=blue]
>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 :-)[/color]

CJ already posted it several messages back.

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

[Michael Austin]

Re: mysql update/replace syntax

Westcoast Sheri wrote:[color=blue]
> CJ Llewellyn wrote:
>
>[color=green]
>>On Sun, 29 Aug 2004 14:18:05 +0000, Westcoast Sheri wrote:
>>
>>[color=darkred]
>>>CJ Llewellyn wrote:
>>>
>>>
>>>>"Westcoas t Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
>>>>news:4131C8 7F.5BF798C7@nos pamun8nospam.co m...
>>>>
>>>>>CJ Llewellyn wrote:
>>>>>
>>>>>
>>>>>>"Westcoas t Sheri" <sheri_deb88@no spamun8nospam.c om> wrote in message
>>>>>>news:4131 5FCC.AFFCF91D@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.[/color]
>>
>>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.
>>[/color]
>
>
> 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?
>
>[/color]

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
:)