Guys im trying to join a comments table to posts table using the following query in a method.
public function feedView($session,$friend,$updateid) {
$sql2=" select u.update_body,u.author,u.time,u.title,u.account_name,u.update_id,"
. "c.comment_body, c.os_id,c.author_c,c.time_c,c.comment_id,c.type_c "
. "from updates u join comment_update c "
. "on c.os_id=:statusid WHERE u.account_name = :session or u.account_name=:friend and (u.type = 'a' or 'c') order by u.time asc,c.time_c desc";
$stmth= $this->_db->prepare($sql2);
$stmth->bindValue(":session",$_SESSION['uname']);
$stmth->bindValue(":friend",$friend);
$stmth->bindValue(":statusid",$updateid);
$stmth->execute();
return $stmth->fetchAll(PDO::FETCH_ASSOC);
}
it prints the posts fine but the problem is with comments content where it seems to print the comments in the same posts twice. don't know where the bug is coming from.
here is the DB schema:
here is the code for displaying feeds:-
<?php
include "includes/dbconfig.inc.php";
$status_replies="";
$status_list="";
$statusui_edit="";
$isowner="";
$is_friend="";
$friends = array();
$stmt= $conn->prepare("select friend_one, friend_two from friends where "
. "(friend_one=:session OR friend_two=:session) and accepted='1'");
$stmt->bindparam(":session",$_SESSION['uname']);
$stmt->execute();
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $i=> $r ) {
$r["friend_one"] == $_SESSION['uname'] ? $friends[]= $r["friend_two"] : $friends[] = $r["friend_one"];
$friend=$friends[$i];
//fetch update_id from user table in db and inject it to the feed query.
$status2view=$project->statusView($_SESSION['uname']);
foreach ($status2view as $val) {
$updateid=$val['update_id'];
//select all relevant posts and comments using the following query and print it.
$feedView=$project->feedView($_SESSION['uname'],$friend,$updateid);
foreach ($feedView as $row1) {
$status_reply_id=$row1['comment_id'];
$reply_d=htmlentities($row1['comment_body']);
$reply_data= stripslashes($reply_d);
$reply_osid=$row1['os_id'];
$reply_date=$row1['time_c'];
$reply_author=$row1['author_c'];
$updateid=$row1['update_id'];
$account_name=$row1['account_name'];
$os_id=$row1['os_id'];
$author=$row1['author'];
$post_date=$row1['time'];
$title= stripslashes($row1['title']);
$data= stripslashes($row1['update_body']);
$statusdeletebutton='';
$reply_delete_button="";
if ($reply_author==$_SESSION['uname'] ) {
$reply_delete_button="<li><a href='#'type='".$status_reply_id."' class='delete_reply_btn glyphicon glyphicon-trash delete_reply_".$status_reply_id."' title='Delete this comment'> Remove</a></span></li>";
}
$status_replies="<div class='replyboxes pull-left reply_".$status_reply_id."'>"
. "Reply by:- "
. "<a href='home.php?u=".$reply_author."'>".$reply_author."</a>"
. "<span class='pull-right'>".$reply_date
. "<b class='dropdown'>
<small><span class='btn btn-xs btn-danger dropdown-toggle pull-right' data-toggle='dropdown' >
<span class='glyphicon glyphicon-edit'></span></span>
<ul class='dropdown-menu'>".$reply_delete_button
. "<li><a class='glyphicon glyphicon-warning-sign' href='report.php?u=".$reply_author."'> Report</a><li></ul></span>"
. "</small></b><br><legend>". html_entity_decode($reply_data)."</legend><br></div>";
if ($author==$_SESSION['uname'] || $account_name==$_SESSION['uname']) {
$statusdeletebutton='<li>'
. '<a href="#" type="'.$updateid.'" class="delete_4_session hidden_text_delete_'.$updateid.' glyphicon glyphicon-trash delete_reply_btn" title="Delete this status and its replies">Remove</a></li>';
$edit_btn='<li>'
. '<a href="#" attr="'.$updateid.'" type="'.$updateid.'" class="edit_4_session hidden_text_edit glyphicon glyphicon-pencil" title="Edit this status" >Edit</a></li>';
}
$status_list= $statusui_edit.'<div attr="'.$updateid.'" type="'.$updateid.'" class="statusboxes status_'.$updateid.' jumbotron">'
. '<h3 style="color:black; margin-bottom:5px; margin-top:5px;" class="pull-left">'
. '<div id="'.$updateid.'" class="title_s_2copy" value="'.html_entity_decode($title).'">'.html_entity_decode($title).'</div></h3>'
. '<span class="pull-right">'
. '<div class="dropdown">'
. '<button type="button" class="btn btn-danger dropdown-toggle" data-toggle="dropdown" >'
. '<span class="glyphicon glyphicon-edit"></span></button>'
. '<ul class="dropdown-menu">'
.$edit_btn .' '. $statusdeletebutton .'</ul></div></span><br><hr>'
. '<legend><span class=" data_s_2copy" type="'.$updateid.'" >'
. html_entity_decode($data).'</span><br><br></legend><b style="text-align:right; color:black;"><small>Posted by:- <a href="home.php?u='.$author.'">'.$author. '</a> '.$post_date.'</small></b>'
. '<br><p>'.$status_replies.'</p><br>';
$status_list.= '<textarea id="reply_textarea_'.$updateid.'" class="status_reply_'.$updateid.' input-custom2" placeholder="comment\'s"></textarea>'
. '<button id="reply_btn_'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply_btn reply_'.$updateid.'">Reply</button></div>';
echo "$status_list";
} } }
1 Answer(s)