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!
19  8698 
Westcoast Sheri wrote: 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!
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
"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
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.
Steve wrote: Westcoast Sheri wrote: 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!
are you after basic sql syntax?
update fruit_database set quantity = quantity + 5 where fruit = 'apple'
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.
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.
--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool
"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?
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.
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? 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?
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.
--
Andy Hassall / <an**@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Angelos |
last post by:
Hello,
First of all sorry if this is not the correct newsgroup for this question,
but I am using PHP with MYSQL and someone here could have an answer and the
experience.
I have the a table (content) with some content and every time I edit it I
store all the table contents in an other table (content_bak) when I want to
restore the contents
How am i going to do that ? I am trying to do :
|
by: Lajos Kuljo |
last post by:
Eine kleine Dokumentation.
Gruss Joska
MySQL Syntax
SELECT
select_expression,...
|
by: red85 |
last post by:
hello i have mysql 4.1 with win2000 SP3, i know that it is only an
alpha and i don't know if someone else has already posted this
problem:
when i execute this sql
UPDATE tableX SET fieldX=valueX
WHERE id IN (SELECT tX.id FROM tableX AS tX WHERE tX.fieldY=valueY AND
filedZ=valueZ);
|
by: David |
last post by:
Hi,
I have a quick question.
I have a .asp application in which certain parts, clients can now
edit/update/add information to their records in our DB.
I basically use an include statement at the top of each page. The
include .asp page has my connection data within .asp tags. The general
'user' to which the connection belongs only has 'select' facilities
within the db.
|
by: Craig Keightley |
last post by:
Can these lines of sql statements be consolidated into one sql statement
(possibly using reg exps??)
BEGIN CODE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Update applications Set news_article = replace(news_article, '<FONT
face="Times New Roman" color=#000000 size=3>', '');
Update applications Set news_article = replace(news_article, '<P
class=MsoNormal style="MARGIN: 0cm 0cm 0pt">', '');
Update...
| | |
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application. I know VBA pretty well but
have never before needed to do this HTTP/XML/MySQL type functions.
|
by: ajos |
last post by:
hi evrybdy,
the problem is:-
i had this running before in jsp but when i changed the jsp page using struts tags there occoured a problem...
when i enter values in the 2 text boxes and click enter an exception occours which is-
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
|
by: osward |
last post by:
Hello there,
I am using phpnuke 8.0 to build my website, knowing little on php programing.
I am assembling a module for my member which is basically cut and paste existing code section of various module that I found it useful.
Here is the 1st problem I encounter:
I had a function to edit a event row form the database which is fine with me, than I pass on the code to a function that save(update) the data to the database.
|
by: ssnaik84 |
last post by:
Hi Guys,
Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of things (stored procedures, functions).. we have to manually edit.
That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :)
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
