Howto: mysql obtain the right sorting for nested records from the same table

In those latest days I was wondering how to sort a table in mySQL that has an id a parent_id and a order column like the one that follows:

id | parent_id | ordr | title
---+-----------+------+----------------------------------
1  | 0         | 1    | first section
2  | 0         | 2    | second section
3  | 1         | 1    | title of first subitem (of first section)
4  | 2         | 2    | title of second subitem (of second section)
5  | 3         | 1    | title of third level item
6  | 2         | 1    | title of first subitem (of second section)
7  | 2         | 1    | title of first subitem (duplicated order index)

Where:

  • id is an autoincrement key,
  • parent_id is the id of the parent node for the current record, we are on the root it will be 0 (Zero).
  • ordr is the sorting relative to the current nesting level
  • title is just a simple description of the record.

The above informations would be represented in this way:

+-+- First section
| |
| +-+- title of first subitem (of first section)
|   |
|   +--- title of third level item
|
+-+- second section
  |
  +--- title of first subitem (of second section)
  |
  +--- title of first subitem (duplicate order index)
  |
  +--- title of second subitem (of second section)

(altro…)

%d blogger hanno fatto clic su Mi Piace per questo: