Bottom-Up Approach - nav of parent and child - PHP Mysql

[andreal]

ScanningI'm looking forward to implement a typical "nav" section for the articles in my website. Something like this:

Folder > SubFolder > SubSubFolder

To this end, I think that the best way is to create a database of two tables.

The first one is called "Category":

id - title - parentid

• 1 - Folder1 - NULL -
• 2 - Folder2 - NULL -
• 3 - Folder3 - NULL -
• 4 - SubFolder1 - 1 -
• 5 - SubFolder2 - 1 -
• 6 - SubFolder3 - 2 -
• 7 - SubFolder4 - 3 -
• 8 - SubSubFolder1 - 6 -
• 9 - SubSubFolder2 - 6 -

The second one is called "Articles":

id - text - lastparentid

• 1 - Lorem Ipsum ... - 7-
• 2 - Lorem Ipsum ... - 3-
• 3 - Lorem Ipsum ... - 5-
• 4 - Lorem Ipsum ... - 2-
• 5 - Lorem Ipsum ... - 3-
• 6 - Lorem Ipsum ... - 5-
• 7 - Lorem Ipsum ... - 8-

The relationship between the two tables is based on the "lastparentid" value in Articles table which will point to the Folder in Category table.
For instance, the article n. 1 is in the Folder which id is n. 7 --> so it is in the SubFolder4 (which is a child of Folder3).
Therefore, when a user lands on an article, I will have only the id of the "folder" that the article is placed in and I will have to recreate the entire tree up to the parent.

To this end and in order to echo the nav, I have the following code (which does not work):

with recursive cteCategories
  AS (select 1 as level,
id,
title,
parentid
from Category
where id = 0
union all
select cte.level+1 as level,
c.id,
c.title,
c.parentid
from Category c
inner join cteCategories cte
on c.parentid = cte.id)
select level,
id,
title,
LPAD(title, '>') as indented_title,
parentid
from cteCategories

However, this follows an approach top-down (from the parent to ALL the children). On the contrary, I need to create the tree from the bottom (represented by the folder pointed by "lastparentid" value of the article) to the wider parent.

For istance: for article n. 7, the nav will be "Folder2 > Subfolder3 > SubSubFolder1"

Please note that I don't have to print all the subfolder that are in each folder, but only go up to the top of thr tree.

[Dani]

Without reading all your code, it's a good strategy to have an articles table that contains records of articles and their category_id, and then a second categories table of category_ids and their parent_id. In this way, for each article, you can traverse the list up to generate its parents.

The way that I am doing this on DaniWeb is by loading all of the categories into memory on every page, and using PHP to generate the breadcrumbs with a recursive function. I find this infinitely easier than using MySQL to generate the breadcrumb list, as MySQL isn't really cut out for this type of recursion.