Creating breadcrumbs for unlimited category depth - PHP + SQL

Question

I would like to be able to create breadcrumbs for a content page, however, the categories in which some of the content is located can have unlimited depth, so I'm not sure how to do this without getting each category one at a time, and then getting its parent, etc. d. It seems like this might be an easier way, but I can't figure it out.

I have an article table

article_id article_name article_cat_id 

I also have a category table

 cat_id cat_name cat_parent 

Cat parent is an identifier of another category, the category of which is a child.

Imagine an article consisting of 5 categories, as far as I can tell, I would need to build breadcrumbs like this (for example, the code must explicitly be escaped, etc.)

 <?php $breadcrumbs = array( 'Category 5', 'Content Item' ); $cat_parent = 4; while($cat_parent != 0) { $query = mysql_query('SELECT * FROM categories WHERE cat_id = '.$cat_parent); $result = mysql_fetch_array($query, MYSQL_ASSOC); array_unshift($breadcrumbs, $result['cat_name']); $cat_parent = $result['cat_parent']; } ?> 

That would give me

 array( 'Category 1', 'Category 2', 'Category 3', 'Category 4', 'Category 5', 'Content Item' ) 

What can I use for my breadcrumbs, but for this I needed 5 queries, which is not very desirable.

Can anyone suggest any better solutions?

Answer 1

Here are some simple options in order of simplicity:

• Stick to the design you have, use a recursive / iterative approach, and enjoy the benefits of simple code. In fact, it will take you pretty far. As a bonus, it’s easier to move from here to something more productive than from a more complex setup.

• If the nr categories are not very large, you can select all of them and build a hierarchy in PHP. Due to the fact that the number of pages required to extract 1 row compared to a whole series of them (say, several hundred) is almost the same. This minimizes the number of requests / network outages, but increases the amount of data transmitted over the cable. Measure!

• Load the hierarchy and reload it completely in X unit of time or when adding / changing / deleting categories. In this simplest form, a cache can be a PHP file with a nested variable structure containing the entire hierarchy of categories, as well as a simple index for nodes.

• Create an additional table in which you somehow smoothed the hierarchy, or using nested sets, path enumeration, closing table, etc. The table will be supported using triggers in the category table.

I would go for (1) if you are not sure that in the near future you will have a constant load of several users per second. (1 user per second makes 2.5 million visits per month).

There is nothing wrong with the code. Complex acceleration code that is not noticeable is incorrect.

Answer 2

There are two commonly used methods for processing hierarchical data in relational databases: the adjacency list model and the nested dialing model. Your schema is currently behind the adjacency list model. Check this page for some sample queries. See also question here for a lot of good information.