0

I'm creating a basic shopping cart in PHP and was wondering, what is the best way of storing and generating a list of categories in a hierarchy format with unlimited subcategories?

Currently I load the main categories and then foreach I get the subcategories from the database and so on, which on a large site would create over 100 queries.

So whats a better way of doing things?

Heres my current MySQL table structure:

cat_id  
cat_name
cat_desc
cat_parent_id
status
priority

I'm aiming for 1 query.

Adam
  • 1,957
  • 3
  • 27
  • 56
  • 4
    See my [answer](http://stackoverflow.com/a/11497724/1446794) to [this question](http://stackoverflow.com/q/11497202/1446794) for some recursive functions you could use that will eliminate the need to call your database for each category in a loop. – Zane Bien Jul 16 '12 at 23:10
  • How many sub category levels does your code support? Unlimited? – Adam Jul 16 '12 at 23:12
  • 1
    Yes, there is no "max-depth", and it will recurse as deep as the deepest nested category in your data. – Zane Bien Jul 16 '12 at 23:15

2 Answers2

0

Wordpress does this pretty well:

see: http://codex.wordpress.org/File:WP3.0-ERD.png and http://codex.wordpress.org/WordPress_Taxonomy

If you go this route This codex page has some question query logic for grabbing categories that you might find useful (related stack post)

The key point is that the table with categories has an entry so you can let it know if the category has a parent category (Allowing you to join the table to itself).

Community
  • 1
  • 1
fideloper
  • 12,213
  • 1
  • 41
  • 38
0

Check out this post: Category Hierarchy (PHP/MySQL)

It uses one query to get all your categories from the database then manipulates the array to create child categories. I modified the answer to use in Codeigniter and it works great. Can post the modified code here if you're wanting to use Codeigniter.

Community
  • 1
  • 1
Scanmaster
  • 100
  • 1
  • 8