0

Table Name: ITEM

+-----+--------+----------+--------------------+--------------------------------------------+
| id  | parent | category | heading            |                    name                    |
+-----+--------+----------+--------------------+--------------------------------------------+
| 228 |      0 |        0 | Website            |                                            |
| 233 |      0 |        0 | Search Engine      |                                            |
| 245 |    228 |        0 | Optional Features  |                                            |
| 234 |      0 |        0 | Domain and Hosting |                                            |
| 232 |      0 |        0 | Design             |                                            |
| 411 |      0 |      232 |                    | Logo Design                                |
| 412 |      0 |      233 |                    | Search Engine Optimisation (SEO) - Monthly |
| 413 |      0 |      233 |                    | Search Engine Marketing (SEM)              |
| 414 |      0 |      245 |                    | Blog                                       |
| 415 |      0 |      245 |                    | Newsletter                                 |
| 416 |      0 |      245 |                    | Favicon                                    |
| 410 |      0 |      228 |                    | Shopping Cart                              |
| 409 |      0 |      228 |                    | Back End (CMS)                             |
| 408 |      0 |      228 |                    | Front End                                  |
| 422 |      0 |      234 |                    | SSL Certificate                            |
| 421 |      0 |      245 |                    | Payment Gateway                            |
| 420 |      0 |      234 |                    | Dedicated IP                               |
| 419 |      0 |      234 |                    | Hosting - Yearly                           |
| 418 |      0 |      234 |                    | Domain Name                                |
| 417 |      0 |      245 |                    | Multiple Languages                         |
| 864 |      0 |      865 |                    | New One                                    |
+-----+--------+----------+--------------------+--------------------------------------------+

-I've created a menu which looks like this:

Website ()
   Optional Features ()
Design ()
Search Engine ()
Domain and Hosting ()

-In the brackets, I would like to add the number of items per category INCLUDING child items

-So for example, 'Website' should 8 items (have 3 + 5 from 'Optional Features')

-Since the number of levels/parents are not limited, I need a recursive query to get this number.

-The tree is currently functioning properly and can be seen here: https://www.mekoowebdesign.com.au/cart/

THE QUESTION -How do I write a recursive MySQL query which will return the number of items in each menu INCLUDING the number of items in its children?

MeKoo Solutions
  • 271
  • 6
  • 5
  • You can use the search on the upper right corner and get http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Solarflare May 05 '16 at 11:21
  • Thanks mate, the previous thread is quite useful and answers part of my question in returning all children elements of a parent item recursively. However, it does not show how we can perform a count on the number of items within those menus. At the moment, I'm running an additional query using the results returned from the recursive call to retrieve the number of items per category. This can be a little demanding in terms of DB calls if the menu has many parent/child elements. It would be wonderful to be able to do it all in the one query. I'm certain it's possible... – MeKoo Solutions May 08 '16 at 04:27
  • Since you haven't provided any code, since you use an unusual approach to not include the "heading"-entries in the count, since you always need some kind of trick/procedure/temp table to simulate "recursive" in mysql (if you don't want to use nested sets as suggested in the linked tread) and since you seem to already have a function that can calculate it but just is demanding, I'd suggest that you just add a column "cnt" to your table and update it everytime you change the tree using your "demanding" function - that just has to run once, so it's the fastest option when you display the tree. – Solarflare May 08 '16 at 10:30

0 Answers0