total items & total price displayed on each page

[[o_O]]

On each I want to have the total items in cart and total price shown on each .asp page:

http://www.bramao.orcon.net.nz/artS.gif


I'm struggling to figure out the simplest and quickest way to do it though. I have a 'cart' table which stores the items and a 'products' table.

http://www.bramao.orcon.net.nz/cart.gif

http://www.bramao.orcon.net.nz/productks.gif


maybe i add a price column to the cart table as well? so to make it easier to calculate the total? otherwise i can only think of one way to do this inlvolving more code than i'd like to.


SQL = "SELECT p.price, c.orderQuantity
FROM products p, cart c
WHERE c.orderItem = p.product_id
AND c.orderNo = '" & Session("orderNo") & "'"

orderQuantity = cartSummaryRS.Fields("orderQuantity")
orderPrice = cartSummaryRS.Fields("price")
itemsTotal = itemsTotal + orderQuantity
orderAmount = orderPrice * orderQuantity
orderTotal = orderTotal + orderAmount



i don't think this would work.


:confused: :confused:

[[o_O]]

<!-- #INCLUDE File="conx.asp" -->
<%
Set cartSumRS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT p.price, c.order_quantity FROM product p, cart c WHERE c.order_item = p.product_id AND c.order_number = '" & Session("orderNo") & "'"

connection.Open connString
cartSumRS.Open SQL,connection
orderTotal = 0
itemsTotal = 0
Do While Not cartSumRS.EOF
orderQuantity = cartSumRS.Fields("order_quantity")
orderPrice = cartSumRS.Fields("price")
itemsTotal = itemsTotal + orderQuantity
orderAmount = orderPrice * orderQuantity
orderTotal = orderTotal + orderAmount

cartSumRS.MoveNext
Loop
cartSumRS.Close
connection.Close

Set cartSumRS = Nothing
Set connection = Nothing

If itemsTotal = 0 Then
itemsTotal = "None"
End If

%>
...
<p>Items: <%=itemsTotal%><br />
Total: <%=FormatCurrency(orderTotal)%></p>



So that does work, if you know a quicker way using less code? or if there are errors or bad bits please point out.


:)

[Roy Sinclair]

There's a Sum() function in SQL you should be able to use in order to get those totals straight from the database and eliminating the need to loop in your code.

[[o_O]]

would you mind showing how I'd use it with my example?


SELECT SUM(order_quantiy)

I would have to do 2 select queries though wouldn't I?

sorry, brain is in slow mode this morning.. :cool:

[Roy Sinclair]

SQL = "select sum(c.order_quantity) as itemsTotal,sum(c.order_quantity * p.price) as orderTotal FROM product p, cart c WHERE c.order_item = p.product_id AND c.order_number = '" & Session("orderNo") & "'"

[[o_O]]

I see. However now if the cart is empty i don't get 'none' displayed and for the total i get:

Type mismatch: 'FormatCurrency'


Becuase using the sql sum what is returned is different. how do i ask if the cart is empty now?


If itemsTotal = 0 Then
itemsTotal = "None"
End If


Doesn't work anymore..

neither does


If itemsTotal < 1 Then
itemsTotal = "None"
End If


or

If itemsTotal = "" Then
itemsTotal = "None"
End If

[whammy]

Try this (a neat little trick I learned from someone here awhile back, don't remember who, but thanks!):


SQL = "SELECT ISNULL(SUM(c.order_quantity),0) AS itemsTotal, ISNULL(SUM(c.order_quantity * p.price),0) AS orderTotal FROM product p, cart c WHERE c.order_item = p.product_id AND c.order_number = '" & Session("orderNo") & "'"


What this does is:

If the sum is NULL, it returns a 0 for you, so the FormatCurrency() function should work just fine for you using this method.

Just goes to show you even hanging around reading posts that are not necessarily relevant to anything you're doing at the moment can teach you a lot - if I remember right I picked that up just looking through a post about someone's problem a few months ago.

[[o_O]]

ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near '0) AS itemsTotal, ISNULL(SUM(c.order_quantity * p.price),0) AS orderTotal FROM p' at line 1

/about.asp, line 216



line 216: SQL = "SELECT ISNULL(SUM(c.....


:eek: looked fine to me?

[whammy]

Before you execute your SQL Statement, put this in:

Response.Write(SQL) : Response.End

This is the best way to initially debug a SQL Statement. Let's see what this produces.

[[o_O]]

wow it cuts off the page right where the cart should start displaying now :eek:



<%
Set cartSumRS = Server.CreateObject("ADODB.Recordset")

Response.Write(SQL) : Response.End

SQL = "SELECT ISNULL(SUM(c.order_quantity),0) AS itemsTotal, ISNULL(SUM(c.order_quantity * p.price),0) AS orderTotal FROM product p, cart c WHERE c.order_item = p.product_id AND c.order_number = '" & Session("orderNo") & "'"

connection.Open connString
cartSumRS.Open SQL,connection

[whammy]

No... let's see the HTML result... what the code is producing and sending to your browser!

[[o_O]]

thats what i mean it cuts off where the cart should start to show up

ouputted html code:


<tr>
<td class="padd2"><span class="blueBoldTxt"><a href="#" class="blueLink">Weekly Deals</a></span></td>
<td class="padd2" align="right"><img src="graphics/arrowSide.gif" height="11" width="6" alt="" /></td>
</tr>
<tr>
<td colspan="3" background="graphics/bottom.gif"><img src="graphics/bottomLeft.gif" height="3" alt="" /></td>
<td><img src="graphics/bottomRight.gif" height="3" alt="" /></td>
</tr>
</table>
<!--End features-->

<br />

<!--Cart -->

nothing.. blank....



under that '<!--Cart -->' is where the asp code i've been posting starts and also where the html code for it starts as you'd expect, not to mention the rest of the page under after it.

no errors come up. source or browser, it just cuts of where the cart should start to kick in.

[whammy]

So where's the Response.Write output for the query, as I demonstrated above, or are you using a different variable for your query than "SQL" ?!?

Make sure you put that IMMEDIATELY after your SQL query!

[[o_O]]

So where's the Response.Write output for the query

good question. The variable is 'SQL' and put before the query it cuts off and displays nothing, no error, no cart. Put after the query and it brings up again:


ADODB.Recordset.1 error '80004005'

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax near '0) AS itemsTotal, ISNULL(SUM(c.order_quantity * p.price),0) AS orderTotal FROM p' at line 1


/etown/about.asp, line 217



it's cool though My original bit for doing this is o.k just uses a bit more code thats all

[whammy]

Oh, I know what the problem is... instead of multiplying those two fields in the second SUM statement, you need to deal with those two fields separately, and THEN multiply them.

Get it?

:D

[[o_O]]

thought i had it there for a minute when i saw

SUM(ISNULL(column) on google, thought i just had it the wrong way round but it didn't fix it.

Perhaps this doesn't work with mySQL? I found variations of it to do with mS SQL server and access

[whammy]

That may be the case, but I still think it is some syntax problem with you trying to get the sum of two separate fields (as ONE variable) by multiplying them, I don't think that will work.

You probably need to figure out how to get each sum separately, and THEN multiply them. Just an educated guess, I haven't tested it, and I could be wrong, but I can't test the code for myself...

Usually (although not always) I am on the right track if not the solution with my guesses. ;)

[[o_O]]

Alright, i'll play around with it.


thanks :)

[Roy Sinclair]

Also contrary to the instructions given above, you should put the response.write of the SQL statement before the call to the SQL. That way you'll get to see the statement as sent to the SQL engine, by putting it after the call to the SQL engine it doesn't get printed when the call fails because the error causes the page to stop being processed (that cutoff you noticed).