Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • how to stick posts to their respective posts in php&mysql

    • 0
    • 0
    • 0
    • 1
    • 0
    • 0
    • 0
    • 453
    Answer it

    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&#95;replies="";
           $status&#95;list="";
           $statusui&#95;edit="";
           $isowner="";
           $is&#95;friend="";
           $friends = array();
    $stmt=  $conn->prepare("select friend&#95;one, friend&#95;two from friends where "
            . "(friend&#95;one=:session OR friend&#95;two=:session) and accepted='1'");
    $stmt->bindparam(":session",$&#95;SESSION['uname']);
    $stmt->execute();
    
    
    foreach ($stmt->fetchAll(PDO::FETCH&#95;ASSOC) as $i=> $r ) {
        $r["friend&#95;one"] == $&#95;SESSION['uname'] ? $friends[]= $r["friend&#95;two"] : $friends[] = $r["friend&#95;one"]; 
    $friend=$friends[$i];
    //fetch update&#95;id from user table in db and inject it to the feed query.
    $status2view=$project->statusView($&#95;SESSION['uname']);
    foreach ($status2view as $val) {
       $updateid=$val['update&#95;id'];
    
    //select all relevant posts and comments using the following query and print it.
    $feedView=$project->feedView($&#95;SESSION['uname'],$friend,$updateid);
    
              foreach  ($feedView as $row1) {
    
                    $status&#95;reply&#95;id=$row1['comment&#95;id'];
                    $reply&#95;d=htmlentities($row1['comment&#95;body']);
                    $reply&#95;data=  stripslashes($reply&#95;d);
                    $reply&#95;osid=$row1['os&#95;id'];
                    $reply&#95;date=$row1['time&#95;c'];
                    $reply&#95;author=$row1['author&#95;c'];
                    $updateid=$row1['update&#95;id'];
                    $account&#95;name=$row1['account&#95;name'];
                    $os&#95;id=$row1['os&#95;id'];
                    $author=$row1['author'];
                    $post&#95;date=$row1['time'];
                    $title= stripslashes($row1['title']);
                    $data= stripslashes($row1['update&#95;body']);
                    $statusdeletebutton='';     
                    $reply&#95;delete&#95;button="";
            if ($reply&#95;author==$&#95;SESSION['uname'] ) {
                       $reply&#95;delete&#95;button="<li><a href='#'type='".$status&#95;reply&#95;id."' class='delete&#95;reply&#95;btn glyphicon glyphicon-trash delete&#95;reply&#95;".$status&#95;reply&#95;id."' title='Delete this comment'> Remove</a></span></li>";
                   } 
    
    
                             $status&#95;replies="<div  class='replyboxes pull-left reply&#95;".$status&#95;reply&#95;id."'>"
                          . "Reply by:- "
                          . "<a href='home.php?u=".$reply&#95;author."'>".$reply&#95;author."</a>"
                          . "<span class='pull-right'>".$reply&#95;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&#95;delete&#95;button
                          . "<li><a class='glyphicon glyphicon-warning-sign' href='report.php?u=".$reply&#95;author."'> Report</a><li></ul></span>"
                          . "</small></b><br><legend>".  html&#95;entity&#95;decode($reply&#95;data)."</legend><br></div>";
    
    
    
    
                  if ($author==$&#95;SESSION['uname'] || $account&#95;name==$&#95;SESSION['uname']) {
                    $statusdeletebutton='<li>'
                               . '<a href="#" type="'.$updateid.'" class="delete&#95;4_session hidden&#95;text&#95;delete&#95;'.$updateid.' glyphicon glyphicon-trash delete&#95;reply&#95;btn" title="Delete this status and its replies">Remove</a></li>';
                    $edit&#95;btn='<li>'
                            . '<a href="#" attr="'.$updateid.'" type="'.$updateid.'" class="edit&#95;4_session hidden&#95;text&#95;edit glyphicon glyphicon-pencil" title="Edit this status" >Edit</a></li>';
    
                    }
    
    
    
                    $status&#95;list= $statusui&#95;edit.'<div attr="'.$updateid.'" type="'.$updateid.'" class="statusboxes status&#95;'.$updateid.'  jumbotron">'
                            . '<h3 style="color:black; margin-bottom:5px; margin-top:5px;" class="pull-left">'
                            . '<div id="'.$updateid.'" class="title&#95;s_2copy" value="'.html&#95;entity&#95;decode($title).'">'.html&#95;entity&#95;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&#95;btn .' '. $statusdeletebutton .'</ul></div></span><br><hr>'
                            . '<legend><span class=" data&#95;s_2copy" type="'.$updateid.'" >'
                            . html&#95;entity&#95;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&#95;date.'</small></b>'
                            . '<br><p>'.$status&#95;replies.'</p><br>';
    
                        $status&#95;list.= '<textarea id="reply&#95;textarea&#95;'.$updateid.'"  class="status&#95;reply&#95;'.$updateid.' input-custom2" placeholder="comment\'s"></textarea>'
                                . '<button id="reply&#95;btn&#95;'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply&#95;btn reply&#95;'.$updateid.'">Reply</button></div>';
    
    
    
                   echo "$status&#95;list";
    
    
    
    
    }         }        }
    

 1 Answer(s)

  • Welcome to Findnerd. I have go through the code and realize that you need to use left join instead of inner join. It will resolve your issue of duplicate comments with posts.

    Thank you Deepak

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: