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. http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — Phill Pafford, Nov 05 '10 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, Nov 05 '10 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, Nov 05 '10 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, Nov 05 '10 at 14:33
@all - If I can get/find/create a sensible solution I would most likle turn it to a class. — rmap, Nov 05 '10 at 14:34
@rmap you are doing double coding then. Once script for testing and then another for production? Call me crazy but I think you should have one version production and one version development which should be almost the same as production. Producing bad code for testing is still bad code and a waste of time. my 2 cents/rant — Phill Pafford, Nov 05 '10 at 14:38
Have a look here similar question asked: http://stackoverflow.com/questions/846201/fast-relational-method-of-storing-tree-data-for-instance-threaded-comments-on-ar — Jakub, Nov 05 '10 at 14:52

RobertPitt · Accepted Answer · 2010-11-05T15:03:56.333

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 05 '10 at 15:03

answered Nov 05 '10 at 14:38

RobertPitt

56,863
21
114
161

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). – Not Available Nov 05 '10 at 14:41
@RobertPitt - Playing with it now - thnks, will come back shortly – rmap Nov 05 '10 at 14:44
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 – Jakub Nov 05 '10 at 14:50
@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

rmap

Nov 05 '10 at 14:50

@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. – rmap Nov 05 '10 at 14:52

@jakub, I forgot to say thaanks for comments – rmap Nov 05 '10 at 14:53

try ` if(count($results) > 0)` and change the last `echo "

";` to `echo "

";` adding the `/` to close the tag. – RobertPitt Nov 05 '10 at 15:02

score 0 · Answer 2 · edited May 23 '17 at 11:56

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 '17 at 11:56

Community

1
1

answered Nov 05 '10 at 15:15

Jakub

20,418
8
65
92

@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 Nov 05 '10 at 15:40
@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. – rmap Nov 05 '10 at 15:42
This would slow down the database indexing dramatically. using a Many 2 many structure would be more sufficient – RobertPitt Nov 05 '10 at 17:49
@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. – rmap Nov 06 '10 at 08:22
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. – RobertPitt Nov 07 '10 at 11:31
... and there is, Drupal uses this system. I may not have explained it in the best fashion, however I did use the Drupal id discussion as a reference. – Jakub Nov 09 '10 at 14:20

PHP loop by depth on array like threaded comments

2 Answers2