grabbing results and subresults in 1 query php/mysql

What if I have a table as following

id | mother_id | subject | date
1 | 0 | test1 | 122344
2 | 0 | test2 | 122335
3 | 1 | reply1totest1 | 122499
4 | 1 | reply2totest1 | 122734
5 | 2 | reply1totest2 | 126887

And I would like PHP to produce HTML which would look like this:

reply1totest1
 - reply1totest1 
 - test1 
reply1totest2 
 - test2 

What I am trying to say is, I would like them grouped together, but with the last reply on top. Also would like this to be done in 1 query, coz I could do it and then reverse the query, but I wann开发者_JAVA技巧a do it nice and neat with 1 query...

---

I must say that table structure does not allow one single query, i mean 2 queries by (select from (select)) because that is not a single query, there is a missing key to identify all items from one thread, so you have to crawl thorw hierarchical design.

I think we got left some question.

Can't you modify the thable structure? and/or insertion method? - I though no because you are asking to solve this sort.

I figured an hypothetical single query, just in case there is no "reply to reply", first you have to run this sql statement

update 'table' set mother_id=id where mother_id=0;

Next, you can do the sort query by

select mother_id,subject from 'table' order by mother_id asc,id desc

I add 'id' to the query so you can do the visual presentation with value change detect when looping to array result

I hope it helps a little

---

1 Query would be impossible because retrieval itself would require multiple queries. Have you checked out join? tigzag references it pretty nicely, just google it! :)