Skip to content
Advertisement

How to query COMMENTS table for each row, then query REPLIES TABLE and append each result inside COMMENTS before JSON echo?

The data is supposed to be dynamic and loaded with an AJAX request. I have comments, which are being queried just below. That works no problem. The issue im running into here is trying to query the replies table that correspond with each comment. My thinking was to try and put a query inside of the div so that for each COMMENT query, it would query REPLIES for all of its results, then append them automatically for the JSON echo. But I got an error. I am perplexed at this point as to combine them in a way to get a proper hierarchy of comments/replies. Is there a way to get a query inside of the div? Or am I going about this completely incorrect? Any help would be much appreciated, been stuck on this for hours. For simplicity I removed much of the data that is in the divs.

COMMENTS QUERY

    $sql="SELECT c.cid, DATE_FORMAT(c.posttime, '%a, %b %D | %l:%i %p'), c.comment,
    p.profilepic,
    u.fname, u.lname
    from taskcomments c
    inner join users u on u.uid = c.uid
    inner join profile p on c.uid = p.uid
    where c.pid = ?";
$stmt3 = $conn->prepare($sql);
$stmt3->bind_param("i", $_POST['cpid']);
$stmt3->execute();
$stmt3->bind_result($cid,$posttime,$comment,$profilepic,$fname,$lname);
$comments = ''; 
while($stmt3->fetch()){ 
$comments .= '<div class="comment-block" id="'.$cid.'">
                    <div class="comment-all-container">
                        <div class="commenter-info-content">                                  
                        </div>
                    </div>
                     //need each additional query to be appended here before echo
                </div>';
$output=array(
        'comments' => $comments
    );

}
$json=json_encode($output);
echo $json;

REPLIES QUERY + divs that need to be appended

$sql="SELECT r.rid, DATE_FORMAT(r.replydate, '%a, %b %D | %l:%i %p'), r.reply,
    p.profilepic,
    u.fname, u.lname
    from taskcommentreplies r
    inner join users u on u.uid = r.uid
    inner join profile p on r.uid = p.uid
    where r.cid = ?";
$stmt4 = $conn->prepare($sql);
$stmt4->bind_param("i", $cid);
$stmt4->execute();
$stmt4->bind_result($rid,$replydate,$reply,$profilepic,$fname,$lname);
$replies = ''; 
while($stmt4->fetch()){

$replies .= '<div class="reply-block" id="'.$rid.'">
                   <div class="commenter-picture">
                       <img class="commenter-photo" src="/'.$profilepic.'">
                    </div>
                    <div class="commenter-info-content">
                    </div>
                </div>';
}

Advertisement

Answer

Here you go:
You have to put the replies query inside the comments query so that for every comment query, it queries for each reply. You must $stmt->store_result(); the comment query so it can be used in the final results of the output.
Next you must use the $comments .= '' with the comments HTML inside the '' to append the comment-block result to the original $comments = '';. REMOVE the last closing </div> from the comment block, we will use that later. After the while ($stmt2->fetch()) loop you must again use the $comments .= '' with the reply-block HTML inside so that each loop is appended to the comment-block. Lastly, outside of the replies loop, you must use the $comments .= '</div>' which closes the block and seals the comment block with its replies within it.

    $sql="SELECT
    c.cid, DATE_FORMAT(c.posttime, '%a, %b %D | %l:%i %p'), c.comment,
    p.profilepic,
    u.fname, u.lname
    from taskcomments c
    left join users u on u.uid = c.uid
    left join profile p on c.uid = p.uid
    where c.pid =?
    ORDER BY c.posttime ASC";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $_POST['cpid']);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($ccid,$cposttime,$ccomment,$cprofilepic,$cfname,$clname);
$comments = ''; 
while($stmt->fetch()){   
$comments .= '
<div class="comment-block" id="'.$ccid.'">
    <div class="comment-all-container">
       <div class="commenter-picture">
           <img class="commenter-photo" src="/'.$cprofilepic.'">
        </div>
        <div class="commenter-info-content">
            <div class="commenter-info">
                 <div class="commenter-name">
                      '.$cfname.' '.$clname.'
                  </div>
                  <div class="comment-time">
                        <p>'.$cposttime.'</p>
                  </div>
             </div>
             <div class="comment-data-container">
                   <div class="comment-data">
                        <p>'.$ccomment.'</p>
                    </div>
                     <div class="reply-reply-container">
                           <a class="reply-tag" onclick="reply(this)">Reply</a>
                    </div>
              </div>                                   
          </div>
    </div>   
';
$sql2="SELECT
    r.rid, DATE_FORMAT(r.replydate, '%a, %b %D | %l:%i %p'), r.reply,
    p.profilepic,
    u.fname, u.lname
    from taskcommentreplies r
    left join users u on u.uid = r.uid
    left join profile p on r.uid = p.uid
    WHERE r.cid=?
    ORDER BY r.replydate ASC";
$stmt2 = $conn->prepare($sql2);
$stmt2->bind_param('i', $ccid);    
$stmt2->execute();
$stmt2->bind_result($rrid,$rreplydate,$rreply,$rprofilepic,$rfname,$rlname);
while ($stmt2->fetch()){
$comments .= '<div class="reply-block" id="'.$rrid.'">
                   <div class="commenter-picture">
                       <img class="commenter-photo" src="/'.$rprofilepic.'">
                    </div>
                    <div class="commenter-info-content">
                        <div class="commenter-info">
                             <div class="commenter-name">
                                  '.$rfname.' '.$rlname.'
                              </div>
                              <div class="comment-time">
                                    <p>'.$rreplydate.'</p>
                              </div>
                         </div>
                         <div class="comment-data-container">
                               <div class="comment-data">
                                    <p>'.$rreply.'</p>
                                </div>
                                 <div class="reply-reply-container">
                                       <a class="reply-tag" onclick="reply(this)">Reply</a>
                                </div>
                          </div>                                   
                      </div>
                </div>
            ';

}
$comments .= '</div>';    
}
$output=array(
    'comments' => $comments
    );

$json=json_encode($output);
echo $json;
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement