Geek Answers Handbook

Create crackers of categories stored in MySQL

In MySQL, I store categories this way:

categories: - category_id - category_name - parent_category_id

What will be the most efficient way to generate a trail / palette for this category_name?

For example crackers (category_id): General> Sub 1> Sub 2

In theory, there can be unlimited levels. I am using php.

UPDATE: I saw this article ( http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ ) about the nested set model. It looks interesting, but how could you learn about dynamic category management? It looks easier on paper, for example, when you know the categories in advance, but not when the user can create / delete / edit categories on the fly ... What do you think?

+5
source share
3 answers

I like to use the Materialized Path , since it essentially contains your footprint in the form of a rusk and makes it easy to do things like select all the descendants of a node without using recursive queries.

Materialized Path Model

The idea with the Materialized path model is to associate each node in the hierarchy with its position in the tree. This is done with a combined list of all node ancestors. This list is usually stored in a separation line. Pay attention to the Lineage field below. CAT_ID NAME CAT_PARENT Lineage 1 Home . 2 product 1 .1 3 CD’s 2 .1.2 4 LP’s 2 .1.2 5 Artists 1 .1 6 Genre 5 .1. 5 7 R&B 6 .1. 5.6 8 Rock 6 .1. 5.6 9 About Us 1 .1

Moving table

Select lpad('-',length(t1.lineage))||t1.name listing
From category t1, category t2
Where t1.lineage like t2.lineage ||'%'
    And t2.name = 'Home';
Order by t1.lineage;

Listing

Home
-product
–CD’s
–LP’s
-Artists
–Genre
—R&B
—Rock
-About Us
+1
source

( ) . , " ", , . , , . ( , , , ).

0

, / SQL. , , .

If you use PHP (or even if you do not), you can look at this code to see a fairly straightforward implementation of adding nodes to the nested set model ( archive.org backup ). Removing (or even moving) is similarly simple.

0
source

More articles:


All Articles