Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Apr 06, 2006 12:08 pm
I have a database of records. Each record has a charge amount attached to it. I want to add the total amount for all of the charges in a database WHERE the customer = the customer selected. I was wondering if there was a MySQL built in ADD or COUNTING function that would add up all of the charge fields for the selection below?
My current selection code:
Code: Select all
mysql_query("SELECT * FROM cases WHERE customer='" . $_POST["customer"] . "'");
Example of my question (incorrect I am sure):
Code: Select all
mysql_query("SELECT * FROM cases ADD(charge) WHERE customer='" . $_POST["customer"] . "'");
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Apr 06, 2006 1:26 pm
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Apr 06, 2006 2:01 pm
I am still at a loss.
Where in that link does it show me what I am looking for?
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Apr 06, 2006 2:03 pm
Read through the functions there, in particular the one referenced in the hash of the link.. Here's a hint: it's one of the last functions listed and it's a another word for "add."
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Apr 06, 2006 3:09 pm
I tried using SUM but it is not working.
Here is my code:
Code: Select all
$total_amount = mysql_query("SELECT SUM(charge) FROM cases WHERE customer='" . $_POST["customer"] . "');
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Apr 06, 2006 3:37 pm
How is it not working? Echo out the query string you are creating. Add
mysql_error() outputs to see if there's something you missed as well.
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Apr 06, 2006 3:57 pm
Here is my error:
Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/capous/public_html/executive.php on line 1584
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Thu Apr 06, 2006 4:08 pm
that doesn't relate to the query not working, but your MySQL connection not working or being malformed or some other such stuff.
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Thu Apr 06, 2006 5:15 pm
Correction on my error:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUM(charge) WHERE customer='webdemo' AND date_completed BETWEEN
RobertGonzalez
Site Administrator
Posts: 14293 Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA
Post
by RobertGonzalez » Thu Apr 06, 2006 6:55 pm
In your MySQL administration utility (phpMyAdmin, Query Browser, MySQL Administrator) run the following query to find out what version of MySQL you are running:
Then search the MySQL manual for that version and the SUM() function.
icesolid
Forum Regular
Posts: 502 Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY
Post
by icesolid » Fri Apr 07, 2006 9:41 am
I figured it out.
Here is my code:
Code: Select all
$result = mysql_query("SELECT SUM(charge) as total_amount FROM cases WHERE customer='" . $_POST["customer"] . "' AND date_completed BETWEEN '" . $_POST["start_date"] . "' AND '" . $_POST["end_date"] . "'");
$row = mysql_fetch_array($result);
echo $row["total_amount"];