Do it all in one query: SELECT p.product_name, p.weight, ( SELECT (z.au_basic + (z.au_costpk * p.weight)) FROM au_zones z INNER JOIN postCodes c ON z.au_zone_id = c.pc_zone_id WHERE c.postcode = <cfqueryparam= cfsqltype="CF_SQL_VARCHAR" value="#form.postalCode#"> ) AS deliveryCost FROM products p WHERE p.productid IN ( <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.productIDList#" list="Yes"> )
<cfoutput query="queryName"> #product_name# [#weight# lbs]: $#deliveryCost# to ship </cfoutput> That will give you a line for every selected product, with the name, weight, and deliveryCost all computed in one query. > I am trying to create a postal code and delivery cost app for my > company, > > I have set the tables up as > > tbl name au_zones: au_zone_id, au_zone(postal zone), au_basic(min > cost), au_costpk(cost per kilo) > tbl name postCodes: post_code_id, postcode(postal code), > pc_zoneid(link to au-zones) > > tbl name products: productid, product_name, weight. > > The calculation will come from postcode to zone > >>au_basic+(au_costpk*product_weight) > > The user will use a form with a text box for post code and a select > box for products (multiples should be allowed). > > When the user fills out and submits the form my products query will > have 1 or multiple records, so i relise i will have to loop throught > the query to get > the individual product weights. This is where my knowledge needs > enlightening, how do i loop throught the query and and reference the > weight against the > au_costpk field from for every product found, and then display it to > the screen. Would i be on the right track using below? > > > > <cfloop query="qryName"> > > <cfset qryName.pdname = (qryname.weight*otherqryname. > au_costpk)+otherqryname.au_basic> > > </cfloop> > > Any ideas? > > Thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314783 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
