make subtotal variable

lelandmyrick

Thanks to some help from this forum I got my earlier problem fixed, but now I have another. I'm new to php and mysql and I'm learning by doing, so bear with me:

I have a mysql database with only one table which contains orders and associated prices in a field 'price'. I want to extract the prices by selecting from the table by the session id field and then summing them.

question one: does the sum(field) function do this?

question two: if it doesn't, what's the cleanest way to do it? I've tried several things without success.

Thanks,

Leland Myrick

mohamed42

First I'm going to assume that your table's name is tbl_orders. Then I'm going to assume that there's more than one "price" field for each session_id. The following will do what you want:

$query = "select price from tbl_orders where session_id = $session";

$result = mysql_query($query);

$result = mysql_fetch_array($result);

$num_results = mysql_num_rows($result);

$sub = 0;

(insert a loop here that I'm not sure how to implement)

That code will generate an associative array of all of the "price" fields that match the session_id help in $session. You'll then need to write a loop that will run through the array, using $num_results to determine how many times to run through the loop, and for each pass through the loop, add on to the value of "Sub".

brandonschnell

or just:

select sum(price) as sum from tbl_orders where session_id = $session

lelandmyrick

Okay, this is the kind of thing I was looking for. However, I tried defining it as a function:

function get_subtotal($sessionid)
{
mysql_select_db("dbsnugtuck");
$query = "select sum(price) as sum from temp where sessionid = '$sessionid'";
$result = mysql_query($query);
echo $result;
}

but all I get as the result is "Resource id #5"

Do I need to process $result further to get the sum to echo?

Thanks for the help,
Leland

Anon

Umm, have you ever actually done this? mysql_fetch_array() does not work at all like you describe. Instead, in fetches a single row's results into an associative array.

In order to derive the sum of the 'price' column, you either need to issue a sum() query, as Brandon describes, or else say 'select price...' as here but then loop through the entire result set and add them up. (This latter may seem like more work, but if you need the individual values as well, e.g. you're printing all the line items rather than just a total, it's not.)

Anon

You need to look through the mysql functions a little more closely; and probably consult a good mysql tutorial (here on phpbuilder among the articles, devshed, webmonkey, etc...). All mysql_query() does is send the query to the database, and return success (if the query was valid, i.e. no syntax or permission errors) or false if it was not. To actually retrieve the data from the row(s) of the result, you need to call one of the mysql_fetch... functions. A query that contains aggregates such as sum(), count, etc, but has no GROUP BY clause will only ever return one row, but you still have to call the fetch function to get the data.

mohamed42

Hence the need for a loop to plow through the results.

Anon

You're still missing the point: you said

That code will generate an associative array of all of the "price"
fields that match the session_id help in $session. You'll then need to
write a loop that will run through the array

but that's not at all a good description of what's going on. The array will hold only the data from only one row at any given time, and what the loop will contain is calls to mysql_fetch_array() or mysql_fetch_row() to retrieve the data from the query, one row at a time.

rovel

so, you have to do:

SELECT SUM(price) as sum
FROM temp
WHERE sessionid = '$sessionid'
GROUP BY sessionid

if i'm correct.

Hope this helps

Ron Velzeboer

lelandmyrick

skywalk wrote:

so, you have to do:

SELECT SUM(price) as sum
FROM temp
WHERE sessionid = '$sessionid'
GROUP BY sessionid

Thanks. My question now is, how do I fetch the sum?

Anon

I guess this is it.

function get_subtotal($sessionid)
{
mysql_select_db("dbsnugtuck");
$query = "SELECT SUM(price) as sum ";
$query .= "FROM temp ";
$query .= "WHERE sessionid = '$sessionid' "; $query .= "GROUP BY sessionid";

$result = mysql_query($query);

if( mysql_num_rows($result) > 0 )
{
list( $sum ) = mysql_fetch_row( $result );
echo $sum;
}
}

Regards,

Ron Velzeboer

http://templatepower.codocad.com

lelandmyrick

Bingo! Thanks heaps. Now I understand what I was doing wrong. And I was able to adapt this function to create the tax and shipping functions.

Thanks again for your help.

Leland Myrick