output nested comment based on three tables

Question

i have three tables, 1.comment, 2.nested comment, 3. username i'm currently struggling how to output the nested comment the best way

$sql = "SELECT comment.*, nested_comment.subject AS nc_subject, users.username
    FROM comment
    INNER JOIN users
      ON comment.user_id = users.id AND comment.blog_id = $id
    LEFT JOIN nested_comment
      ON comment.id = nested_comment.comment_id
    ORDER BY comment.id DESC";

my current view-code

<? while($comment = $result->fetch_object()) { ?>

<p> <?=$comment->subject . ' by ' . $comment->username; ?> </p>

<hr>
<div class="nested_comment">
  <p> <?=$comment->nc_subject; ?> </p>
</div>

all data i wanted is shown, but say i have -2 nested comment- in -1 parent comment-, the parent comment still showing up 2 times insted only once.

I can check with if-else statement on the while-loop, if the previous comment id is the same, but i'm looking for more elegant/efficent solution for this, I'm not sure how to 'group' it on sql

score 2 · Answer 1 · edited May 23 '17 at 11:44

2

For a more elegant way, you can fetch those comments into an array by using parent comments as the array's keys and nested comments as values.

$array[ $comment->subject ][] = $comment->nc_subject;

Then you can iterate over this array with "foreach" and build up your div.

foreach( $array as $key => $value )

$key will be your parent comment and $value will be an array that includes your nested comments. So you need to iterate over $value too in order to get your nested comments.

You can check this answer for key-value array usage: https://stackoverflow.com/a/1951708/2698754

edited May 23 '17 at 11:44

Community

1
1

answered Jul 31 '16 at 00:18

tyb

201
4
13

1

Good option. I've actually done it this way before but once we had 10k rows of result for each page, it seemed a bad idea to store all of that in the array since it has to be built first before any output. Note that `comment->id` would be a better key than `comment->subject` since subjects can be the same across diff comments. – aneroid Jul 31 '16 at 00:26
thanks for the solution, interesting topic, which is the best way to do it on higher volume, is splitting in two query faster? – MisterCat Jul 31 '16 at 00:35
@aneroid Yeah you're right, I totally missed the point about subjects. Thought it would be good idea to use them as keys, since he'll print them out. – tyb Jul 31 '16 at 00:44
Also, If the array is created as `$array[ $comment->id ][] = array('nc_subject' => $comment->nc_subject, 'nc_subject_id' => $comment->nc_subjectid)` etc. with the row associated array as the value, then he can have all the required data of the `nested_comment` as retrievable values to use later. – aneroid Jul 31 '16 at 17:01

score 1 · Answer 2 · answered Jul 31 '16 at 00:03

1

Since you're retrieving all the columns for comments and nested comments in the same query, each row of the result will contain comments and as many times as there are nested comments.

Either split the query into 2 separate queries to get comments and nested comments separately, or, as you said, put an if clause to check if the current comment.id is the same as the last.

With the if, do something like:

<? curr_id = -1;
   while($comment = $result->fetch_object()) {
       if $comment->id != curr_id {
               curr_id = $comment->id ?>
           <p> <?=$comment->subject . ' by ' . $comment->username; ?> </p>
       <? } ?>
       <hr>
       <div class="nested_comment">
         <p> <?=$comment->nc_subject; ?> </p>
       </div>

answered Jul 31 '16 at 00:03

aneroid

12,983
3
36
66

thanks for ur reply, hmm.. is that not possible with 1 query? maybe there is something like grouping all with the same -comment parent id- from the sql query, maybe.. – MisterCat Jul 31 '16 at 00:06
Even if you group by, you still get the _grouped_ column in [every row of the result](http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_groupby_2). Btw, in your `ORDER BY` clause, add `nested_comment.id`. – aneroid Jul 31 '16 at 00:11
i dont' mean 'group by' on the query, i mean 'grouping it' i'm sure you know what i mean. Yeah still debatable which way is the best, thanks anyway! – MisterCat Jul 31 '16 at 00:33

output nested comment based on three tables

2 Answers2