feha Posted July 1, 2008 Share Posted July 1, 2008 categories table: items_table items_categories ---------------- ------------------ ----------------- id id category_id parent category_id item_id category_name item_name I have a problem , I need the above structure work as: When selected main category show all items in subcategories also have one item in more than one category ... there is single table for main and sub-sub-sub ... (tree-like) categories ... hope some one can put me in right direction ... thank you Quote Link to comment Share on other sites More sharing options...
br0ken Posted July 1, 2008 Share Posted July 1, 2008 Someone else might be able to help you but unless you word your problem better I'm afraid I can't really do much. Quote Link to comment Share on other sites More sharing options...
feha Posted July 1, 2008 Author Share Posted July 1, 2008 Hi brOken Sorry if could not explain better, i will try now :-) When selected main category , i would like to show-list all items in main category but also in subcategories of selected main category ... When selected sub category i want to show all items in that sub category but also items of sub-sub category to particular sub category etc ... Quote Link to comment Share on other sites More sharing options...
br0ken Posted July 1, 2008 Share Posted July 1, 2008 Well then you would first select the subcategory and loop through all of those items then with each item use the sub-category ID returned to grab the categories listed with that. Try and write some code and show it here and then I can guide you. Quote Link to comment Share on other sites More sharing options...
feha Posted July 1, 2008 Author Share Posted July 1, 2008 i do have a ready a categories tree script with recursive function php ... I need a SQL statement that would select items with just one get parameter (category_id) categories table: ------------------------ "category_id" int(11) NOT NULL auto_increment COMMENT 'Main ID', "category_parent_id" int(11) default '0' COMMENT 'Parent ID', "name" varchar(250) NOT NULL , Items table: ------------------------- "item_id" bigint(20) NOT NULL auto_increment, "category_id" int(11) default '1' COMMENT 'Category Main ID', "name" varchar(250) NOT NULL, "image" varchar(250) default NULL, "description" text, As you see i use single category id ... in items ... So the problem is to show items that belong to sub-categories when a parent (or main) category is selected ... Quote Link to comment Share on other sites More sharing options...
feha Posted July 1, 2008 Author Share Posted July 1, 2008 I'm waiting some MySQL guru for answer ... Thank You Quote Link to comment Share on other sites More sharing options...
fenway Posted July 2, 2008 Share Posted July 2, 2008 The way you've set it up, you'll have to JOIN as many levels deep as you want to display -- you may want to look into the list adjacency model too. Quote Link to comment Share on other sites More sharing options...
feha Posted July 3, 2008 Author Share Posted July 3, 2008 Hi fenway thank you I will have to put all categories in to php array an traverse the tree, put all parents to a string and use in MySQL IN (1,4,3) etc ... I thought if it could be made just with SQL query ... Quote Link to comment Share on other sites More sharing options...
feha Posted July 3, 2008 Author Share Posted July 3, 2008 I think I have found answer else: What you are describing is a classic Many-To-Many relationship. Many sites, attached to Many categories. The correct way to do this is to use 3 tables. A sites table, a categories table, and an "assignments" table which assigns sites to categories. SITES siteid <-- primary key url <-- other fields related to your site description rating CATEGORIES categoryid <-- primary key name description keywords ASSIGNMENTS siteid <-- foreign key to site categoryid <-- foreign key to category I like to make my primary and foreign keys of type MEDIUM INT, because you can make these autoincrement so you don't need to worry about them, but the format of your primary key is up to you, just keep it consistent. You would use embedded select statements to get your data. SELECT * FROM SITES s WHERE '15' IN (SELECT categoryid FROM ASSIGNMENTS a WHERE a.siteid = s.siteid) would get all the sites that are in the category with categoryid = '15'" So it will be suitable ... but i can't use categories directly to items ... but will try as relationship ... thank you And this will not help me display items of subcategory when selected main (parent) category Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.