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.

Thanks

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".