php / Mysql best tree structure

by

You can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set Model.

If you’re using an ORM like Doctrine, it includes nested set capabilities.

It can be difficult for some to grasp the nested set concepts of left and right. I have found that using those numbers as an analogy for the line numbers of open/close tags in an XML document, folks find it easier to grasp.

For instance, take the data example from the MySQL link above:

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

If you take the lft, rgt fields and use them as line numbers for an XML document, you get:

1. <electronics>
2.    <televisions>
3.        <tube>
4.        </tube>
5.        <lcd>
6.        </lcd>
7.        <plasma>  
8.        </plasma> 
9.     </televisions>
10.    <portable electronics>
11.        <mp3 players>
12.            <flash>
13.            </flash>
14.        </mp3 players>
15.        <cd players>
16.        </cd players>
17.        <2 way radios>
18.        </2 way radios>
19.    </portable electronics>
20. </electronics>

Seeing it this way can make it much easier for some to visualize the resulting nested set hierarchy. It also makes it clearer why this approach improves efficiency as it makes it possible to select entire nodes without the need for multiple queries or joins.

More Related Contents:

  1. Turn database result into array
  2. Convert a series of parent-child relationships into a hierarchical tree?
  3. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  4. PHP / MySQL build tree menu
  5. Get all child, grandchild etc nodes under parent using php with mysql query results
  6. Comma separated numeric values – PHP CodeIgniter
  7. PHP PDO and Mysql [closed]
  8. Parse error: syntax error, unexpected T_STRING in C:\wamp\www\b_php\project\login_save.php on line 14 [closed]
  9. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  10. strange character encoding of stored data , old script is showing them fine new one doesn’t
  11. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  12. How to loop through a mysql result set
  13. Save PHP array to MySQL?
  14. implement LIKE query in PDO
  15. PHP PDO prepared statement — MySQL LIKE query
  16. How to successfully rewrite old mysql-php code with deprecated mysql_* functions?
  17. How can I use mysqli_fetch_array() twice?
  18. Executing multiple SQL queries in one statement with PHP
  19. mysql_num_rows() expects parameter 1 to be resource, boolean given in [duplicate]
  20. Easy way to export a SQL table without access to the server or phpMyADMIN
  21. using nulls in a mysqli prepared statement
  22. How can I count the numbers of rows that a MySQL query returned?
  23. How to run the PHP script at scheduled time
  24. How to handle error for duplicate entries?
  25. How to handle PDO exceptions [duplicate]
  26. MySql PHP select count of distinct values from comma separated data (tags)
  27. mysql_insert_id() returns 0
  28. Eloquent many-to-many-to-many – how to load distant relation easily
  29. How can I insert large files in MySQL db using PHP?
  30. Add Prefix to auto-increment in mysql db

Leave a Comment