PHP loop by depth on array like threaded comments

Question

I am trying to create a "depth" based threaded comment type of thing for a job I am working on. The code (below) works but is prob. cumbersome and also I would like to go to $x depths which the way I have done it would mean "lots" of loops, any suggestions about how to create a simpler/better way?

    $sql = $azdb->get_row("SELECT * FROM ".$table_prefix."_content WHERE ID='".$_GET['ID']."'");

if($sql): 

echo  '<h2>'.$sql->content_title.'</h2>';

echo  date('d m Y',strtotime($sql->content_modified));

echo  '<br />';

echo  $sql->content;

endif;



$sql = $azdb->get_results("SELECT * FROM ".$table_prefix."_content WHERE content_parent='".$_GET['ID']."'");

if($sql): foreach($sql as $sql):

echo  '<div class="comments">';

echo  '<h2>Main '.$sql->content_title.'</h2>';

echo  date('d m Y',strtotime($sql->content_modified));

echo  '<br />';

echo  $sql->content;

echo  '<br />';




$sql1 = $azdb->get_results("SELECT * FROM ".$table_prefix."_content WHERE content_parent='".$sql->ID."'");

if($sql1): foreach($sql1 as $sql1):

echo  '<div class="comments">';

echo  '<h2>'.$sql1->ID.' - '.$sql1->content_title.'</h2>';

echo  date('d m Y',strtotime($sql1->content_modified));

echo  '<br />';

echo  $sql1->content;

$sql2 = $azdb->get_results("SELECT * FROM ".$table_prefix."_content WHERE content_parent='".$sql1->ID."' ");

if($sql2): foreach($sql2 as $sql2):

echo  '<div class="comments">';

echo  '<h2>'.$sql2->content_title.'</h2>';

echo  date('d m Y',strtotime($sql2->content_modified));

echo  '<br />';

echo  $sql2->content;

echo  '</div>';

endforeach; endif;

echo  '</div>';

endforeach; endif;

echo  '</div>';

endforeach; endif;

help appreciated. Thanks

I would suggest looking into SQL injection and how to prevent it. stackoverflow.com/questions/60174/… — Phill Pafford
– Phill Pafford, Commented Nov 5, 2010 at 14:21
Can you give us more details on your table Schema? How is it laid out? We can only guess now since all is SELECT * FROM, bad way of doing SQL anyway, as well as unchecked $_GET['id'] vars in SQL — Jakub
– Jakub, Commented Nov 5, 2010 at 14:22
Hi Jakub table scheme is "open" to debate as they say the ID ($_GET['id']) is the primary key and content_parent is an INT within the table all other fields would be text or date based so should not affect the functionality I am after. What I would like to avoid is a 3rd col for children_id if you follow that one. — rmap
– rmap, Commented Nov 5, 2010 at 14:32
@Phill Pafford don't worry about injection, this is NOT the final php/sql just a way for me to test/create the actual. — rmap
– rmap, Commented Nov 5, 2010 at 14:33
@all - If I can get/find/create a sensible solution I would most likle turn it to a class. — rmap
– rmap, Commented Nov 5, 2010 at 14:34

RobertPitt · Accepted Answer · 2010-11-05 15:03:56Z

0

Use a recursive function like so

$id = isset($_GET['id']) ? $_GET['id'] : 0;
$root_sql = $azdb->get_results("SELECT * FROM categories WHERE cat_parent = " . $id);

recursive_categories($root_sql);

function recursive_categories($results)
{
     if(count($results))
     {
         echo "<ul>";
         foreach($results as $res)
         {
             echo "<li>" . $res->category;
             //Rest of what ever you want to do with each row

             //Check this category for children
             $rows = $azdb->get_results("SELECT * FROM categories WHERE cat_parent  = " . $res->id);

             recursive_categories($rows);

             //has to be after the inner loops
             echo "</li>";
         }
         echo "</ul>";
     }
}

So for each iteration of the root it then finds another category that is a parent of the id, and then runs the same function at that point so creating another inner loop.

Modify accordingly but you get the idea.

edited Nov 5, 2010 at 15:03

answered Nov 5, 2010 at 14:38

RobertPitt

57.3k21 gold badges117 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Not Available Over a year ago

This is indeed how it can be done, and I don't think there's another way (Well perhaps Recursive iterators but that's just making things unnecessary complicated).

rmap Over a year ago

@RobertPitt - Playing with it now - thnks, will come back shortly

Jakub Over a year ago

This makes the code simple but you are still faces with the same x amount of SQL calls as before, this is just done properly. Not sure if this is what @rmap wanted as an answer, I would hope for a more optimized SQL approach as that is where you have processing cost

rmap Over a year ago

@RobertPitt - works well with one little exception and instantly I can't find it. If there are NO children (on your code) I get an empty <li>

rmap Over a year ago

@Jakub, yes this is "what I wanted" I agree with you " I would hope for a more optimized SQL approach" but as I said above my code was most certainly not optimised/safe etc. more a way to see how.what could be done.

|

Community · Accepted Answer · 2017-05-23 11:56:09Z

0

What I would do in this situation is first and foremost review HOW you will insert your comments into the DB. The Schema you define is the most critical component to this whole piece.

I referenced a similar question on SO here: Fast Relational method of storing tree data (for instance threaded comments on articles)

The user is stumped as to how to store this comment data, and the Drupal format is really something efficient. And by looking at it this way you can create a clean approach, which will eliminate your multiple x SQL queries.

Simply create your comment ID's like so (string):

1 - main
1.1 - comment 1
1.2 - comment 2
1.2.1 - nested comment for comment 2
1.2.1.1 - nested comment for comment 2, nested comment 1
1.2.2
1.2.3
1.2.2.1
1.1.1 - (this comment you could order by your sql call to be 3rd from the top, etc)

That allows you to make a single SELECT * FORM call (get all ids that are x.% etc), and then order all your comments properly, with the ability to use the ID as a conditional check to nest or not.

I think you need to re-think your schema first, then tackle the PHP code which becomes just a bunch of <li> entries it looks like.

@RobertPitt 's answer is 'ok' but it does nothing for you, as it just cleans up your code from sequential if conditions to a proper recursive function.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Nov 5, 2010 at 15:15

Jakub

20.5k8 gold badges67 silver badges93 bronze badges

6 Comments

rmap Over a year ago

@Jakub Many thanks for this approach, I like it. a very "non IT" and very"paper based filing reference" type of system. Makes complete sense.

rmap Over a year ago

@Jakub - sorry, pressed return before I could finish - from 1 question I actually get 2 GREAT obvious solutions that sometimes I forget to look at i.e. the obvious. I suppose too long at the PC and not enough time thinking how I used to think - paper based.

RobertPitt Over a year ago

This would slow down the database indexing dramatically. using a Many 2 many structure would be more sufficient

rmap Over a year ago

@Jakub I agree with RobertPitt, but that still does not take away from the "nice" logical functionality of this type of system. I like both solutions.

RobertPitt Over a year ago

Logically this does look a good idea, if there was only some way of computing the ids into something that can be accessed at the same speeds as using binary, integers for index ect.

|

Collectives™ on Stack Overflow

PHP loop by depth on array like threaded comments

2 Answers 2

12 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related