php & mysql - adding results of a column

robsgaming

I have another question. Based upon a query that selects a certain range of dates, the column 'OrderTotal' displays the total price of the order for a day.

Example: I run query selecting orders made from october 1 to october 15... say i get three results from teh query

10.5.02 - $4.55
10.9.02 - $45.00
10.14.02 - $39.00

how can i get mysql to add up everything in the OrderTotal column based upon the query i run which is:

$query = "SELECT * FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'";

($year and $month are predefinied using a form)

does this make sense?

any help is appreciated. Thanks.

rob

IceD

$query = "SELECT SUM(OrderPrice) as OrderTotal FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'";

no1uknowdfw

I think this is what you are requesting....

Basically you want to sum up the column of data coming back.

above your sql query make an array to write in your column data...

$all_orders = array();

in your while loop...

while ($row = sybase_fetch_array($query)) {
$A = $row['Product'];

$all_orders[] = $row['orders'];

or in your while loop after you've called your rows

$all_order[] = $orders;

Add up the array and echo outside your while loop....

echo array_sum($orders);

No1UKnow

no1uknowdfw

disregard the while loop with sybase command...
That does not relate to you... Ooops....

The sum of the arrays works good in any database....

The count feature in MySql works great also as the guy above me posted....

robsgaming

heres the code:

	$db = mysql_connect("xxxt", "xxx", "xxx");
	mysql_select_db("orders",$db);
	$totresults=mysql_query("SELECT * FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'");
	$totresults=mysql_num_rows($totresults);
	$query = "SELECT * FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'";
	$result = MYSQL_QUERY($query);
            echo "<table border=1><tr><td><B>Date</B></td><td><B>Order Total</B></td></tr>";
	$all_orders = array(); 
	while ($row = mysql_fetch_array ($result)): 
	extract($row);
	$all_order[] = $orders; 
	echo "<tr><td>$OrderDate</td><td>$OrderTotal</td></tr>\n";
	endwhile;
            echo "</table><P>$totresults orders found for month $month, $year<P>";
            echo array_sum($orders);

i get this error:

Fatal error: Call to undefined function: array_sum() in /blahblahblah/blahblahblah/blahblah on line 20

no1uknowdfw

Excuse me I got carried away with your code... <grin>
Check this out gives you some examples of what you could do...
The Sum fuction that the other guy posted is also a great way...
But if you want a little more control, especially if you are using a different database other than mysql this is a good technique...

$db = mysql_connect("xxxt", "xxx", "xxx");
mysql_select_db("orders",$db);

// declare the array before the query...
$all_orders_a = array();
$all_orders_b = array();
$all_orders_c = array();

$totresults=mysql_query("SELECT columna, columnb, columnc FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'");
$totresults=mysql_num_rows($totresults);
$query = "SELECT columna, columnb, columnc FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'";
$result = MYSQL_QUERY($query);

while ($row = mysql_fetch_array ($result) {
$columna = $row['columna'];
$columnb = $row['columnb'];
$columnc = $row['columnc'];

	$all_orders_a[] = $columna; 
	$all_orders_b[] = $columnb; 
	$all_orders_c[] = $columnc; 

$tr .= "<tr><td>$OrderDate</td><td>$OrderTotal</td></tr>";

}

$displaytable = "<table border=1><tr><td><B>Date</B></td><td><B>Order Total</B></td></tr>
	$tr
	<tr colspan=2 nowrap><td>$totresults orders found for month $month, $year</td></tr>
	<tr colspan=2 nowrap><td>echo array_sum($all_orders_a) - array_sum($all_orders_b) - array_sum($all_orders_c)</td></tr>
	";

// or

echo array_sum($all_orders_a);
echo array_sum($all_orders_b);
echo array_sum($all_orders_c);

robsgaming

Thanks for the help

im getting a parse error on line 20 which is the line denoted with the text arrows:

$result = MYSQL_QUERY($query);

====> while ($row = mysql_fetch_array ($result) { <====
$columna = $row['columna'];
$columnb = $row['columnb'];

no1uknowdfw

====> while ($row = mysql_fetch_array ($result) { <====
$columna = $row['columna'];
$columnb = $row['columnb'];

looks like your missing a ")"

while($row = mysql_fetch_array($result)) {
$columna = $row['columna'];
$columnb = $row['columnb'];
}

robsgaming

Ok I finally figured it out right before you posted.

Now, lol, im getting resource errors:

Warning: Supplied argument is not a valid MySQL result resource in /blahblahblah/ on line 15

Warning: Supplied argument is not a valid MySQL result resource in /blahblahlksjlkjblah on line 20

I'm guessin its coming from this statement:

SELECT columna, columnb, columnc FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'

have any ideas?

no1uknowdfw

columna, columnb, columnc

you'll need to name these to what ever your column table names are in the mysql db...

These were just examples...

If you are using MySQL, IceD's post may be easier for you...
He beat me to the posting...
That's the only reason I gave you this idea...

Like I said this way gives you more control in the long run if you are going to scale the script... But for something simple the sum command in mysql works great....

robsgaming

your idea was good

$query = "SELECT SUM(OrderPrice) as OrderTotal FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'";

is good as well

my FINAL question,

how do i get the results of that query to a variable that i can echo out anywhere i want?

IceD

$query = "SELECT SUM(OrderPrice) as OrderTotal FROM Orders WHERE OrderDate BETWEEN '$year:$month:01' AND '$year:$month:31'";
I suggested this in the start of this topic... You should not fetch all data from DB and sum it up in the PHP code. It's VERY BAD! Imagine, that you have 10000000 records. In case of SUM(OrderPrice) you will get only one record back, otherwise...

no1uknowdfw

IceD is 100% correct...

In a large corp enviroment (using stored procedures) though where it takes time get the db admins to do there job. <grin>

You have this as an option.

For reports and small returns it's not a big deal to throw this in there...

But if I had that many records I would be using Oracle not MySql...
Anything over 4 gigs of data slows down using MySql where Oracle is faster over 4 gigs. <grin>