Re: MySQL - How to create a category tree in a store
I think I'm going to use this tool: http://nstree.riaforge.org/ I've been playing around with it. Seems fairly straightforward and does everything I need. I'm just working on what the admin interface would look like to display the entire tree so you could select multiple categories for a given product. Thanks for everyone's input. Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322755 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
@will Good deal. Haven't used below but it seems the author has written a nice book about it on his site. Good luck. -- Ryan On Sat, May 23, 2009 at 2:27 AM, Will Tomlinson w...@wtomlinson.com wrote: I think I'm going to use this tool: http://nstree.riaforge.org/ I've been playing around with it. Seems fairly straightforward and does everything I need. I'm just working on what the admin interface would look like to display the entire tree so you could select multiple categories for a given product. Thanks for everyone's input. Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322756 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL - How to create a category tree in a store
This might be useful: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Cheers, Dave I built a store application that has a simple product category schema. Right now, the user can select one or more categories to associate to a given product. I have a linking table in between those two tables that resolves the many-to-many. Now, I need the ability to let the user create their own categories and nested categories/subcategories, then associate a product to one or more of them. Much like a category tree. i.e. A shoe needs to be associated to the footwear category, and the shoes category located within the footwear category. Make sense? :) I've looked at recursion, but I'm on MySQL 5.1, and I don't think it supports recursive queries. Does anyone have any suggestions on the best way to go about this? Also, what would you implement for the admin tool that creates the category tree? Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322708 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
This might be useful: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Cheers, Dave Yes, there's a lot of great info there. But it looks like a nightmare if I want to add a node under the parent. Products -baseball --gloves --bats --shin guards -golf --gloves --bags ---nike ---titleist ---ping --shoes If I want to add lacrosse between baseball and golf, that'd be a pain. Wish I could find something easier for this. Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322709 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: MySQL - How to create a category tree in a store
@willr u ok with setting a maximum number of levels? -- Ryan On Fri, May 22, 2009 at 10:14 AM, Will Tomlinson w...@wtomlinson.comwrote: This might be useful: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Cheers, Dave Yes, there's a lot of great info there. But it looks like a nightmare if I want to add a node under the parent. Products -baseball --gloves --bats --shin guards -golf --gloves --bags ---nike ---titleist ---ping --shoes If I want to add lacrosse between baseball and golf, that'd be a pain. Wish I could find something easier for this. Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322710 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
@willr u ok with setting a maximum number of levels? -- Ryan Yep. I could max it out at 4 levels. I had thought about building related tables, 4 of them. Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322711 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
I am assuming you at least have product_id and category_id in your linking table. I think if you added a parent_id accounting for the root level you could build out your tree. Not sure of how you want to go about it but thinking out loud: 1. Query all categories at root level 2. Loop through root level and pull children (all those with a parent as that category) 3. Repeat this for the max levels 4. Pull products at each level (1,2,3,4) Basically you are only assigning one sub/category but you have the hierarchical relationship stored in the db. You would have to account for that when searching on the front end. Front end I would suggest jQuery for an accordion like effect(slidedown/up and/or fadein/out) and pull the products using the ajax or load method. -- Ryan On Fri, May 22, 2009 at 10:41 AM, Will Tomlinson w...@wtomlinson.comwrote: @willr u ok with setting a maximum number of levels? -- Ryan Yep. I could max it out at 4 levels. I had thought about building related tables, 4 of them. Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322714 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: MySQL - How to create a category tree in a store
Also depending on your traffic versus how often the products will get updated versus your hosting plan etc you may want to output the entire tree at the time of it being maintained on the back end rather than every time someone visits the site. Don't know your situation, just an idea if you need to limit hits on the database. -- Ryan On Fri, May 22, 2009 at 11:11 AM, Ryan Letulle bayous...@gmail.com wrote: I am assuming you at least have product_id and category_id in your linking table. I think if you added a parent_id accounting for the root level you could build out your tree. Not sure of how you want to go about it but thinking out loud: 1. Query all categories at root level 2. Loop through root level and pull children (all those with a parent as that category) 3. Repeat this for the max levels 4. Pull products at each level (1,2,3,4) Basically you are only assigning one sub/category but you have the hierarchical relationship stored in the db. You would have to account for that when searching on the front end. Front end I would suggest jQuery for an accordion like effect(slidedown/up and/or fadein/out) and pull the products using the ajax or load method. -- Ryan On Fri, May 22, 2009 at 10:41 AM, Will Tomlinson w...@wtomlinson.comwrote: @willr u ok with setting a maximum number of levels? -- Ryan Yep. I could max it out at 4 levels. I had thought about building related tables, 4 of them. Thanks, Will ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:322715 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
