1

I have the need to render a threaded view of a 2-levels hierarchical commenting system. The comments are stored in a database. The information about the hierarchy is given by field 'parent_id' (which is 0 for the top-level comments). I cannot change the structure of the database. The present solution is by means of multiple SQL queries:

  1. an SQL query is performed to fetch all top-level comments
  2. the code loops through the top-level comments and for each of them performs an SQL query to fetch its children

Now I wonder if a solution with only one SQL query to fetch all the comments as they are followed by the code suggested here to sort them by threads could be more efficient. Any reccomendation?

Thanks, Luciano

Community
  • 1
  • 1
user1797295
  • 11
  • 1
  • possible duplicate of [PHP Create a Multidimensional Array from an array with relational data](http://stackoverflow.com/questions/11239652/php-create-a-multidimensional-array-from-an-array-with-relational-data) – Yoshi Nov 08 '12 at 11:14

1 Answers1

0

I've done similar scripts and from my point of view it's better to do a first query to fetch all the 'parents' (parent_id==0) and then for each one of them do another query to get all its 'sons' information.

If you have to retrieve a HUGE ammount of threats using a single query you have to wait for the query to complete to work with the data. If you divide the search in different and smaller queries you can start formating and printing them before looking for the 'sons'. Also doing it in a single query could make the query slower since using more tables in the same query could make it halt due to a lock_table when someone is creating a new threat.

Another solution, which I would only recommend if the query is very slow due to being forced to use multiple JOINs or using WHERE with non-indexed fields (you should never do that, but if you can't change the database...), is to retrieve ALL the threats in a single query (both parents and sons, without any of those WHERE or JOIN that makes the query slower) and then organize them using PHP. This is by no means practical, and you should never use this method unless the time to complete the query is very long.

Naryl
  • 1,878
  • 1
  • 10
  • 12