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
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.
For a table with 2 nested levels without any JOIN:
SELECT * FROM table ORDER BY IF(parent_id = 0, id, parent_id), id