使用php对mysql记录进行高级/技术排序

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

Answer 1

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.