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)