Advanced/technical sorting of mysql records with php

Question

I am having an issue trying to determine how to sort some data from a mysql database. I have a content items table, and the records contain an id (auto increment), a parent_id (so one page can be a child of another), url, and title.

I want to sort the data in sort of a nested list style, where the main non-child page is first level, and those with that pages id as a parent id are listed below it. Is there a special order to use here, or would this need to be sorted by php?

So, as an example:

id   |  parent_id  | title
---------------------------
1       0            Page 1
3       0            Page 3
6       1            Page 6
7       3            Page 7

The list should look as such:

Page 1
    Page 6
    Any others with parent id of 1
Page 3
    Page 7
    Any others with parent id of 3

A 3rd level, I'm trying to understand how to sort this kind of data, if it canbe done via mysql or if i have to loop the results and sort them manually. — WeaponsTheyFear, Aug 01 '19 at 02:57
Hierarchical data in a relational database is not simple. The best way is using closure tables: https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql — Sammitch, Aug 01 '19 at 03:26
When I had to do this for arbitrary number of levels, I did the sorting in PHP. You can write a recursive procedure to do it. — Barmar, Aug 01 '19 at 16:54

score 2 · Answer 1 · answered Aug 01 '19 at 02:18

2

For a table with 2 nested levels without any JOIN:

SELECT * FROM table ORDER BY IF(parent_id = 0, id, parent_id), id

answered Aug 01 '19 at 02:18

user1597430

1,138
1
7
14

2

Clever. You should mention that the line should be indented when `$row['parent_id'] != 0` – Barmar Aug 01 '19 at 02:21
@Barmar, indeed. – user1597430 Aug 01 '19 at 02:34

score 1 · Answer 2 · answered Aug 01 '19 at 02:18

Join the table with itself, joining the parent ID with the ID.

SELECT parent.title AS parent_page, child.title AS child_page
FROM yourTable AS parent
JOIN yourTable AS child ON child.parent_id = parent.id
ORDER BY parent.id

In your PHP loop, print the parent page heading whenever it changes. See How can i list has same id data with while loop in PHP? for the structure of that loop.

Advanced/technical sorting of mysql records with php

2 Answers2