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! :)
精彩评论