Ahmed El Sayed Sharaf Eldin Ahmed El Sayed Sharaf Eldin - 6 months ago 13
PHP Question

select the story with its comments and replies and check if current user likes or not in one single query

I have four tables


Posts:


post_id title Userid description
1 test 20 TEXT



Comments:


comment_id post_id Userid content
1 1 5 TEXT
2 1 36 TEXT
3 1 6 TEXT



Replies


reply_id comment_id Userid content
1 1 4 TEXT
2 2 5 TEXT
3 2 30 TEXT



likes:


post_id Userid
1 5


The site starts loading last 20 story in the database.. Now suppose userid (5) is logged in, for example post_id (1) show as liked and all posts show with their comments and replies. I wanna done that in one query instead of nested loops inside loops.


My code:


$sql1 = $conn->prepare("SELECT * FROM posts ORDER BY post_id DESC LIMIT 20");
$sql1->execute();
while($row1=$sql1->fetch(PDO::FETCH_ASSOC)){
....
// <div class="post" id="<?php echo row1['post_id']; ?> </div>
.
.
....
$sql2 = $conn->prepare("SELECT * FROM comments WHERE post_id=:post_id");
$sql2 ->bindValue(':post_id', $row1['post_id']);
$sql2 -> execute();
while($row2=$sql2 -> fetch(PDO::FETCH_ASSOC)){
....
// <div class="comment" id="<?php echo row2['comment_id']; ?> </div>
.
.
....
$sql3 = $conn->prepare("SELECT * FROM replies WHERE comment_id=:comment_id");
$sql3 -> bindValue(':comment_id', $row2['comment_id']);
$sql3 ->execute();
while($row3=$sql3 ->fetch(PDP::FETCH_ASSOC)){ .... }

}
$sql_like=$conn->prepare("SELECT * from likes WHERE post_id = $row1['post_id'] AND user_id=$logged_user_id");
$sql_like->execute();
$sql_like->rowCount() > 1 ? //userlikes : //user does not like ;
}

Answer

Your question is not 100% clear, but you can start from something like this:

$sql1 = $conn->prepare("SELECT p.*, 
                 c.comment_id, 
                 r.reply_id, 
                 COUNT(DISTINCT l.post_id) likes
             FROM (SELECT
                FROM posts 
                ORDER BY post_id DESC LIMIT 20
             ) p
             LEFT JOIN comments c 
             ON p.post_id = c.post_id
             LEFT JOIN replies r
             ON c.comment_id=r.comment_id
             LEFT JOIN likes l
             ON l.post_id = p.post_id
               AND l.user_id=$logged_user_id");
$sql1->execute();
   $last_post_id = 0;
   $last_comment_id = 0;
   while($row = $sql1->fetch(PDO::FETCH_ASSOC)){
     if ($last_post_id != row['post_id'] ) {
        echo '<div class="post" id="'.$row['post_id'].'>'.$row['post_id'].'</div>';
        echo '<div class="like">'.$row['likes'].'</div>';
        ...
        $last_post_id = row['post_id'];
        $last_comment_id = 0;
     }
     ...
     if ($last_comment_id != $row['comment_id']) {
       echo '<div class="comment" id="'.$row['comment_id'].'>'.$row['comment_id'].'</div>';
       $last_comment_id = $row['comment_id'];
     }
     ...                    .
     echo '<div class="reply" id="'.$row['reply_id'].'>'.$row['reply_id'].'</div>';

   }
Comments