A different approach would be to list each SKU instead of listing the independent options. So instead of say a select box for colour and a select box for size, you'd have just one select...
Red XS Red S Red M Red L Red XL Blue XS Blue S Blue M Blue L Blue XL .... This would make the queries easy. But also if you had no inventory of "Blue M" then you could support that easily! -----Original Message----- From: Will Tomlinson [mailto:[EMAIL PROTECTED] Sent: Wednesday, 7 September 2005 8:45 a.m. To: CF-Talk Subject: Have brain lock, need schema/query help! Hey guys, I'll get right to it. I'm working on my new cfc cart app and using a new db schema. Here's the schema: http://wtomlinson.com/newcartschema.gif You add a productid/model, then add SKU'S to it. Where I'm having trouble is with the options. They're twisting my brain up! I've made it to the details page where I query for options, then loop through them producing cfselects in my addtocart form. Keep in mind no code I'm posting is finished at all, just trying to make the basics work. Here's my post to the cart: <cfform name="addtocartform" action="addtocart.cfm" format="html" width="400" height="200"> <cfoutput query="options" group="optiontypename"> <cfselect name="option_#options.currentrow#"> <cfoutput><option value="#theoptionID#">#optionname#</option></cfoutput> </cfselect> </cfoutput> This is working fine. It's dynamic and might not even show up if there are no options available for a given item. I'll check it with if's and process accordingly. My problem is on the cart page where I'm having trouble querying the tblprod_skus to add a specific item to the cart. I've tried looping through form.formfields to find the options present, but can't seem to make anything work. Used some list functions and such. Like this: <cfquery name="getitem" datasource="blahblah" result="thesql"> SELECT tblprod_SKUS.SKUID, tblprod_SKUS.SKU_prodID, tblprod_SKUS.SKUprice,tblprod_SKUS.merchSKUID, tblSKUoptions_rel.optionID, tblSKUoptions_rel.optionSKUID, tblSKUoptions_rel.option_optionID FROM tblprod_SKUS, tblSKUoptions_rel WHERE tblprod_SKUS.SKUID = tblSKUoptions_rel.optionSKUID <cfloop list="#FORM.fieldnames#" index="i"> AND option_optionID = #ListLast(i, "_")# </cfloop> </cfquery> Secondly, once I even make that work I can't figure out what my query needs to look like to extract a specific item. If an item was added to the cart, size = small, color = red, I can pull those out of tblSKUoptions_rel, but how do I get the specific item out of tblprod_SKUS? I'm just mixed up! UGHHHHHHH! Will ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217516 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
