类别和子类别

Question

I've asked a question few hours ago - afterwards decided to change my sql structure.

I have 2 tables:

categories (id,title,parent_id)
articles (id,title,content,parent_id)

each article can have only 1 category,
each category can have 1 parent category

an example:

article (id = 1) has : parent_id = 3
category (id = 3,'root') has: parent_id = 2
category (id = 2,'sub') has: parent_id = 1
category (id = 1,'subsub') has: parent_id = 0

I want to generate breadcrumbs for - article (id=1)
That would be shown as: Root->sub->subsub

What is the efficient way to accomplish this?

Thanks!

Answer 1

1. When loading article, read all categories recursively and display breadcrumbs.

2. If you need get all parents more than one time per script (e.g. when you are loading 50 articles and need parents for each), use the nested set model, as explained in Managing Hierarchical Data in MySQL.