0

I created two tables and uploaded it to trusted imgur.com to show you an example:

https://i.stack.imgur.com/viIsR.jpg

// EDIT looks like imgur is down, uploaded to another site: http://images.tinypic.pl/i/00761/0ies71okcyxd.png

As it is in the example above, you see, that

  • PANTIES
  • CORSET
  • SOCKS
  • PAJAMA

belongs to parent 5, which belongs to category 5, it means to UNDERWEAR and that category belongs to parent 1, which means that it belong to category 1 called CLOTHES.

I'm reading examples on sites like these below:

www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html

mysql hierarchy storage with large trees

http://www.slideshare.net/billkarwin/models-for-hierarchical-data

https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/

https://pragprog.com/book/bksqla/sql-antipatterns

What is the most efficient/elegant way to parse a flat table into a tree?

and I still can not figure out how to display path of:

  • ALL CLOTHES,
  • ALL UNDERWEAR (PANTIES, CORSET, SOCKS, PAJAMA).

I can only display for example:

  • PAJAMA

        SELECT 
        t.path
    FROM 
        thumbnails t
    LEFT JOIN 
        categories c
    ON
        t.category = c.category
    WHERE
        t.category = 9

where 9 = PAJAMA

Community
  • 1
  • 1
Wojciech Glapa
  • 93
  • 8

1 Answers1

1

This is my all-time favorite way to manage hierarchical data in mysql:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

table example:
id     name     lft    rgt
1      clothes  1      10
2      tops     2      3
3      bottoms  4      9
4      pants    5      6
5      shorts   7      8

this allows you to have a hierarchy like this:

clothes
+--Tops
+--bottoms
+----pants
+----shorts

tito swineflu
  • 51
  • 3
  • I know this article and I like it, but this is not a full answer. I still do not know how to display for example all UNDERWEAR'S path*s. – Wojciech Glapa Feb 29 '16 at 13:08
  • The issue is that you cannot do a path query of unlimited depth on a data structure like this. You need to change the data structure to accommodate paths of any depth. Then, you need to do a self-join: SELECT * FROM table_a as parent join table_a as child. – tito swineflu Feb 29 '16 at 17:32