Re: Have brain lock, need schema/query help!
If anyone has any more suggestions on the db schema and/or queries I'd appreciate it. Notice I'm just focusing on the items themselves right now. I figured once I got this tougher stuff knocked out I'd add tblcategories and such. Thanks, Will ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217528 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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# cfoutputoption 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! UGHHH! 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:217505 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
Re: Have brain lock, need schema/query help!
I done something similar to your drop down menu and had to end up using the Evaluate functions to figure out the name of the form field. Gave me a major headache as I recall. The biggest question on my mind is why does the productID have multiple SKU's (1 to many) on your db schema? Isn't the SKU for each product going to be unique? Matt - Original Message - From: Will Tomlinson [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, September 06, 2005 3:31 PM Subject: SPAM-LOW: 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# cfoutputoption 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! UGHHH! Will ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217506 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
re: Have brain lock, need schema/query help!
wow it's amazing how that code looks just like cartweaver! You guys must be on the same wave length!! I can't wait too see it after you spagetti it :) haha ~Dave the disruptor~ Some people just don't appreciate how difficult it is to dispense wisdom and abuse at the same time. From: Will Tomlinson [EMAIL PROTECTED] Sent: Tuesday, September 06, 2005 4:31 PM To: CF-Talk cf-talk@houseoffusion.com 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: #optionname# 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: 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 AND option_optionID = #ListLast(i, _)# 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! UGHHH! 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:217507 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
RE: Have brain lock, need schema/query help!
An SKU incorporates options such as colour and size A product could be ColdFusion coffee mug while an SKU might be ColdFusion Coffee Mug, Size XXL, white I guess a product is a promotional unit, while an SKU is an inventory unit. -Original Message- From: Matthew Blatchley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 7 September 2005 9:16 a.m. To: CF-Talk Subject: Re: Have brain lock, need schema/query help! I done something similar to your drop down menu and had to end up using the Evaluate functions to figure out the name of the form field. Gave me a major headache as I recall. The biggest question on my mind is why does the productID have multiple SKU's (1 to many) on your db schema? Isn't the SKU for each product going to be unique? Matt - Original Message - From: Will Tomlinson [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, September 06, 2005 3:31 PM Subject: SPAM-LOW: 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# cfoutputoption 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! UGHHH! 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:217511 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Have brain lock, need schema/query help!
An SKU incorporates options such as colour and size A product could be ColdFusion coffee mug while an SKU might be ColdFusion Coffee Mug, Size XXL, white I guess a product is a promotional unit, while an SKU is an inventory unit. Exactly! Thanks! This is definitely a brain buster for me. 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:217512 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=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Have brain lock, need schema/query help!
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# cfoutputoption 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! UGHHH! 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
Re: Have brain lock, need schema/query help!
The combination of those options or attributes (size, color, make, model, whatever) about the product all together make up a unique SKU The way Will has the structure of the database table one could infer that there is a one product to many SKU relationship and also a one SKU to many Option relationship. The SKU_prodID and prodID should be a one to one relationship, not a one to many relationship otherwise you'll get one product representing multiple SKUs and multiple options for one product, when it should be one SKU with multiple options for each single product. Unless I missed something? Which is totally possible... - Original Message - From: Will Tomlinson [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Tuesday, September 06, 2005 7:27 PM Subject: SPAM-LOW: Re: Have brain lock, need schema/query help! An SKU incorporates options such as colour and size A product could be ColdFusion coffee mug while an SKU might be ColdFusion Coffee Mug, Size XXL, white I guess a product is a promotional unit, while an SKU is an inventory unit. Exactly! Thanks! This is definitely a brain buster for me. 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:217518 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
