1

I was wondering what would be the logic to display all categories and sub-cat and sub-sub categories and so on using PHP and MySQL?

A brief example would be very helpful thanks.

Here is my MySQL database structure.

id  parent_id   category                            url                                             depth
2   0           Arts & Ent                      ?cat=arts-and-entertainment                0
4   0           Automotive & Trans              ?cat=automotive-and-transportation         0
6   0           Business & Finance              ?cat=business-and-finance                  0
sony
  • 11
  • 1
  • 3
  • hi, first of all please describe your question in detail..and where you want to display this categories and subcategories..? in combobox or somewhere else..? – Chandresh M Apr 27 '11 at 07:05
  • combobox is `` – Teneff Apr 27 '11 at 07:11
  • no I am not using comboboxes at all – sony Apr 27 '11 at 07:12
  • See: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – prodigitalson Apr 27 '11 at 07:14
  • @prodigitalson okay and what am I looking for? – sony Apr 27 '11 at 07:15
  • All those are examples of DB structures and how to perform queries on hierarchical data. You first need to figure the best query method... then you can deal with how to get that information in the proper structure with php... which compared to the actual development of the query and schema is trivial. – prodigitalson Apr 27 '11 at 07:20
  • you can see I already have my database structure, I wanted to know how to display my categories and sub-categories – sony Apr 27 '11 at 07:24

2 Answers2

6

See managing hierarchical data in MySQL for example of queries to get your categories. You won't get far with this flat table though, you'll have to run multiple queries to get full list to unknown depth. There are good alternatives in the article I linked, so if it's possible to change the database structure - I really advise you to do that.

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
Slava
  • 2,040
  • 15
  • 15
  • how is my table structure not good and what exactly is wrong with it? – sony Apr 27 '11 at 07:33
  • Read the article I linked. This simpliest `Adjacency List Model` is described at the very top of it with all its limitations. I named only the biggest problem: you can't get full category tree with single query if tree depth is dynamic. There are other problems. – Slava Apr 27 '11 at 07:42
2

Storing Hierarchical Data in a Database is the best explanation of the most efficient way that I've found to store hierarchical data in a database.

Also, consider using a nested set library integrated with an ORM, such as Propel. (See NestedSet Behavior in Propel.)

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
Homer6
  • 15,034
  • 11
  • 61
  • 81
  • how is my table structure not good and what exactly is wrong with it? – sony Apr 27 '11 at 07:34
  • It has to do with the speed of the lookups. When you go to write your library, making calls like "get all ancestors" and "count ancestors" is inefficient with just a parent id. I appreciate your question. I've implemented both ways. The left/right solution is way easier. – Homer6 Apr 27 '11 at 07:34
  • so what would be the most optimal structure? – sony Apr 27 '11 at 07:36
  • The left/right schema is best for lookups. Writes are costly for it (as you have to re-index the entire table). What is optimal really comes down to the specific operations that you'll be running on them. Are they mostly reads? – Homer6 Apr 27 '11 at 07:37
  • Also, how many levels do you anticipate? 3? 4? 10? If it's only 2, you can get away with the parent id. It's easier to implement and with select boxes, you can use the select group. – Homer6 Apr 27 '11 at 07:39
  • By reads, I mean database lookups. Is the data changing constantly? Or does it mostly stay static and database queries just get data from mysql? – Homer6 Apr 27 '11 at 07:40
  • well the categories are static. – sony Apr 27 '11 at 07:42
  • If that's the case, then it's not a tree structure (hierarchical data). If you can avoid trees, try to. There are also a lot of good tree packages out there. If you know you'll only have two levels (ie. product and product category), I'd break them into two separate tables. – Homer6 Apr 27 '11 at 07:44
  • And use the optgroup element in the select box http://www.w3schools.com/tags/tag_optgroup.asp to represent the categories – Homer6 Apr 27 '11 at 07:45
  • Then I'd use a nested set package... such as this one... http://www.propelorm.org/wiki/Documentation/1.5/Behaviors/nested_set – Homer6 Apr 27 '11 at 07:48