Shopping Cart Delivery Charge

Hi I am building a shopping cart which calculates shipping charges, some products also have an additional surcharge. Which is where i am stuck, The additional surcharge is stored within the product table. What i thought was to loop through the products in the cart,  query the products database to get the surcharge or surcharges, loop through the query results for the individual surcharges. If this is the correct way, how do i then add the surcharges up and account for when the loop finishes. Jason

i think the best way would be to do this is directly through sql in one query. you can build a list with the ids of all the products the customer has selected, then in 1 query... select surcharge from products where productid in(<cfqueryparam value="theListOfProductIds#" list="yes" cfsqltype="cf_sql_varchar">) and surcharge <> "" i am not sure but there must be a function in sql that adds up all the values in a column - maybe someone else can suggest it - but this sql statement then saves all the time that a loop would take and would just return the results where those products you have entered have a surcharge, then it is just a matter of adding it up. but as i said there must be a sql function you can use within this same function to do that ----- Excess quoted text cut - see Original Post for more -----

if there isnt a function in sql that can do that then there is a function on cflib to get a columntotal http://cflib.org/udf.cfm?ID=887 so you can run that one query then pass the resulting column into the columnTotal udf and thats it, you will have the total surcharge... ----- Excess quoted text cut - see Original Post for more -----

sorry i must be half asleep, you can use the sum function. so the full function would be select sum(surcharge) from products where productid in(<cfqueryparam value="theListOfProductIds#" list="yes" cfsqltype="cf_sql_varchar">) and surcharge <> "" ----- Excess quoted text cut - see Original Post for more -----

Do you already have a query with your shopping cart? If so, just use a query-of-query (with SUM()). You don't mention if the surcharge is by product or by each item, but you may need to also be sure to factor in the product quantity. --- Mary Jo

The surcharge is for each product, you can only buy one main product at at time (multiple purchases are treated as trade), you can also add multiple accessories (which have no delivery charge) Shopping cart items are stored in the session. I am exploring the sum route at the moment. Thank you all for your response. > Do you already have a query with your shopping cart? If so, just use a > query-of-query (with SUM()). You don't mention if the surcharge is by > product or by each item, but you may need to also be sure to factor in > the product quantity. > > --- Mary Jo >

You can carry a variable through your cfloop/cfoutput query to hold the running total of the surcharges. You can also hit the database with a new call for the sum, as other have mentioned. You can also do: <cfset allsurcharges = ArraySum(ListToArray(ValueList([querycolumnname])))> William ----- Excess quoted text cut - see Original Post for more -----