I don't know if this sounds like possible in mySQL. Listed here are my tables:-
Groups table - id - title - parent_id (which indicates Groups.id)
I personally use the above mentioned table to map all of the groups and sub-groups.
Items table - id - title - category_id
The course_id within the Items table indicates the sub-category id that goes.
e.g. Basically have Toys > Educational > ABC where ABC is product, Toys is Category and academic is sub Category, then ABC may have category_id as 2.
Now however , I wish to make use of a SQL query to show all of the items (in most the sub-groups as well as their sub-groups.. n level) for the category.
e.g. choose * from groups,items where category.title = 'Toys' and ....
The above mentioned query should display the items from Educational also and all sorts of other sub groups as well as their subcategories.
Is possible utilizing a mySQL query? Otherwise what options have i got? I must avoid PHP recursion.
Update: Essentially I wish to display the very best 10 items within the primary category which I'll be doing with the addition of a hits column to items table.
What I have completed in previous projects where I have required to perform the same factor, I added two new posts.
- i_depth: int worth of how deep the course is
- nvc_breadcrumb: complete path from the category inside a breadcrumb kind of format
After which I added a trigger towards the table that houses the course information to complete the next (the 3 updates have been in exactly the same trigger)...
-- Reset all branches UPDATE t_org_branches SET nvc_breadcrumb = NULL, i_depth = NULL -- Update the root branches first UPDATE t_org_branches SET nvc_breadcrumb = '/', i_depth = 0 WHERE guid_branch_parent_id IS NULL -- Update the child branches on a loop WHILE EXISTS (SELECT * FROM t_branches WHERE i_depth IS NULL) UPDATE tobA SET tobA.i_depth = tobB.i_depth + 1, tobA.nvc_breadcrumb = tobB.nvc_breadcrumb + Ltrim(tobA.guid_branch_parent_id) + '/' FROM t_org_branches AS tobA INNER JOIN t_org_branches AS tobB ON (tobA.guid_branch_parent_id = tobB.guid_branch_id) WHERE tobB.i_depth >= 0 AND tobB.nvc_breadcrumb IS NOT NULL AND tobA.i_depth IS NULL
After which just perform a join together with your items table around the category ID and perform a "LIKE '%/[CATEGORYID]/%' ". Bear in mind this was completed in MS SQL, but it ought to be simple to result in a MySQL version.
It could just be compatible enough for any cut and paste (after table and column title change).
Growth of explanation...
t_groups (because it stands now)...
Cat Parent CategoryName 1 NULL MyStore 2 1 Electronics 3 1 Clothing 4 1 Books 5 2 Televisions 6 2 Stereos 7 5 Plasma 8 5 LCD
t_groups (after modification)...
Cat Parent CategoryName Depth Breadcrumb 1 NULL MyStore NULL NULL 2 1 Electronics NULL NULL 3 1 Clothing NULL NULL 4 1 Books NULL NULL 5 2 Televisions NULL NULL 6 2 Stereos NULL NULL 7 5 Plasma NULL NULL 8 5 LCD NULL NULL
t_groups (after utilisation of the script I gave)
Cat Parent CategoryName Depth Breadcrumb 1 NULL MyStore 0 / 2 1 Electronics 1 /1/ 3 1 Clothing 1 /1/ 4 1 Books 1 /1/ 5 2 Televisions 2 /1/2/ 6 2 Stereos 2 /1/2/ 7 5 LCD 3 /1/2/5/ 8 7 Samsung 4 /1/2/5/7/
t_items (as you've it now, no modifications)...
ID Cat Name 1 8 Samsung LNT5271F 2 7 LCD TV mount, up to 36" 3 7 LCD TV mount, up to 52" 4 5 HDMI Cable, 6ft
Join groups and items (where groups is C, items is P)
C.Cat Parent CategoryName Depth Breadcrumb ID p.Cat Name 1 NULL MyStore 0 / NULL NULL NULL 2 1 Electronics 1 /1/ NULL NULL NULL 3 1 Clothing 1 /1/ NULL NULL NULL 4 1 Books 1 /1/ NULL NULL NULL 5 2 Televisions 2 /1/2/ 4 5 HDMI Cable, 6ft 6 2 Stereos 2 /1/2/ NULL NULL NULL 7 5 LCD 3 /1/2/5/ 2 7 LCD TV mount, up to 36" 7 5 LCD 3 /1/2/5/ 3 7 LCD TV mount, up to 52" 8 7 Samsung 4 /1/2/5/7/ 1 8 Samsung LNT5271F
Now presuming the items table was more complete to ensure that there's stuff in every category with no NULLs, you can perform a "Breadcrumb LIKE '%/5/%'" to obtain the last three products from the last table I provided. Observe that it offers the direct products and kids from the category (such as the Samsung tv). If you would like Just the specific category products, just perform a "c.cat = 5".