Re: mysql update/replace syntax

From: CJ Llewellyn (invalid_at_example.con)
Date: 08/29/04

  • Next message: Andy Hassall: "Re: Timestamp on local Win2k server returns 1970. Help!" 
    Date: Sun, 29 Aug 2004 13:37:54 +0100

    "Westcoast Sheri" <sheri_deb88@nospamun8nospam.com> wrote in message
    news:4131C87F.5BF798C7@nospamun8nospam.com...
    > CJ Llewellyn wrote:
    >
    > > "Westcoast Sheri" <sheri_deb88@nospamun8nospam.com> wrote in message
    > > news:41315FCC.AFFCF91D@nospamun8nospam.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/GROUP-BY-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?

  • Next message: Andy Hassall: "Re: Timestamp on local Win2k server returns 1970. Help!"

    Relevant Pages

    • Re: mysql update/replace syntax
      ... >> customer sales records and producing summary information from that. ... >> SELECT fruit, sumAS numsales FROM sales GROUP BY fruit ORDER BY ...
      (comp.lang.php)
    • Re: mysql update/replace syntax
      ... >> mySQL queries work somehow? ... > SELECT fruit, sumAS numsales FROM sales GROUP BY fruit ORDER BY ... If they buy 5 apples, ...
      (comp.lang.php)
    • Re: mysql update/replace syntax
      ... > mySQL queries work somehow? ... INSERT INTO sales VALUES ... SELECT fruit, sumAS numsales FROM sales GROUP BY fruit ORDER BY ...
      (comp.lang.php)