Jump to content
narutofan

not able to create a facebook like timeline

Recommended Posts

i'm trying to print posts just like facebook timeline with infinite scroll jquery and php, mysql. i wrote a inner join mysql query to fetch posts from groups and updates. The problem is i'm plagued by duplicate posts and unable to separate the group posts from updates in raw data. i tried a couple of times to sepearate and place them in jumbotron classes but i fail. Though the code is lengthy i hope to find some corrections and answers by sharing it. i need to prevent duplicate posts coming up through the query and printing while the ajax request go through's too.

PHP class query:

public function totalUpdates($friend,$session,$var_id, $load) {
    try{
    $sql2="select distinct g.*,u.avatar,u.user_id,up.* from group_posts as g "
            . " join user as u on uname=g.author_gp "
            . " join updates as up on u.uname=up.author"
            . " where g.gname='MEP news' and (up.update_id >:update_id_all or g.gp_id>:update_id_all) and up.author in(:friend, :session) order by time,pdate desc limit $load,5
";

//$sql2="SELECT distinct update_id,update_body,time,title,user_id_u,account_name,author,data FROM updates where author in(:friend,:session)  and update_id not in(:update_id_all)  order by time desc limit $load,5";
        $stmth=  $this->_db->prepare($sql2);//Check here syntax of $db 
    
    $stmth->bindValue(":friend",$friend);
    $stmth->bindValue(":session",$session);
    $stmth->bindValue(":update_id_all",$var_id);
    //$stmth->bindValue(":update_id2",$update_id2);
    $stmth->execute();
    return $stmth->fetchAll();
    } catch (PDOException $ei){
        echo $ei->getMessage();
    }
}

PHP script that prints values:

  $f = array();
  $ids=  array();
$stmt=  $conn->prepare("select friend_one, friend_two from friends where (friend_one=:session OR friend_two=:session) and (friend_one>=:session or friend_two>=:session) and accepted='1'");
$stmt->bindparam(":session",$_SESSION['uname']);
$stmt->execute();
$f=$stmt->fetchAll();
$f_count=count($f);

$ids=  ff($f, $project);
//$k=0;
//for ($k=0;$k<count($ids);$k++){
 //while($i<$f_count){    
//$id=$v;
//$i=0;
   
//foreach ($update_id1 as $i => $v) {
    
     $ex= explode(",", $update_id1[0]);
     
     $unique=array_unique($ex);
     $im= implode(",", $unique);
                                                             
     

//fetch username from update table in db and inject it to the feed query.
   
     $imp_id="'" .join( "','", $ids)."'";
     

  
  
            


$totalUpdates=$project->totalUpdates($id,$_SESSION['uname'] ,$im,$load);   

 
    /*

     *we get the $sess_count and $load from javascript in home page and forward the logic if load*2 is greater than 
     * $sess_count variable if it is great then the $total_sess_count is set to 0 else it is fetched from classes.inc.php 
     *     
     */
    
    if($load*5<$sess_count){
        $total_sess_count=0;
    }
 else {
    
  $total_sess_count=$project->totalupdatescount($id);
 }


   foreach ($totalUpdates as $j=>$row1) {
 echo '<pre>';
 print_r($row1);
 echo '</pre>';
 if(isset($row1['update_id'])){
       $updateid=$row1['update_id'];
                
                $account_name=$row1['account_name'];
                $u_id=$row1['user_id_u'];
                $author=$row1['author'];
                $post_date=$row1['time'];
                $title= stripslashes($row1['title']);
                $data= stripslashes($row1['update_body']);
              $data1=  hashtags($data);
              //$data1=  taggingsys($data0);
              $pic=$project->viewByUname($author);
              $uid=$pic['user_id'];
         $datemade = strftime("%B %d, %y", strtotime($post_date));
      $avatar=$pic['avatar'];
        if ($avatar!=""){
          $feed_pic='../user/user/'.($uid?$uid:$post_avatar).'/'.$avatar;
      }  else {
          $feed_pic='img/avatardefault.png';
          }
          
                     $words=explode(" ", ($data1?$data1:$data2));
         $count_words=count($words);
         if($count_words>180){
          $partial_data1= join(" ", array_slice($words,0,150));
          
          $more="<button class='btn btn-link more' data-id='".$updateid."'>...More</button>";
         }else{
             $partial_data1=($data1?$data1:$data2);
             $more="";
         }
          
          $vote_up_count=$project->voteUpdateCheck($updateid, $_SESSION['id']);
          
        if($vote_up_count[0][0]>0){
            $vote_like=TRUE;
            
        } else {
        $vote_like=FALSE;    
        }
          if($vote_up_count[0][1]>0){
            $vote_dislike=TRUE;
            
        } else {
        $vote_dislike=FALSE;    
        }
        include 'ratings/vote_count.php';
          if ( $vote_like==TRUE && $vote_dislike==FALSE) {
           
          
             $like='<a  type="liked" class="btn btn-warning liked status_like_'.$updateid.' " attr="'.$updateid.'">Liked</a>&nbsp;<span class="label label-info">'.$num_likes[0].'</span>';
             $unlike='<a  type="unlike" class="btn btn-danger status_unlike_'.$updateid.' " attr="'.$updateid.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes[0].'</span>';   
          }
 elseif ($vote_dislike==TRUE && $vote_like==FALSE) {
              
             $like='<a  type="like" class="btn btn-warning status_like_'.$updateid.' like" attr="'.$updateid.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes[0].'</span>';
             $unlike='<a  type="disliked" class="btn btn-danger disliked status_unlike_'.$updateid.'" attr="'.$updateid.'">Disliked</a>&nbsp;<span class="label label-info">'.$num_unlikes[0].'</span>';
          }  else {
             $like='<a  type="like" class="btn btn-warning status_like_'.$updateid.' like" attr="'.$updateid.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes[0].'</span>';
             $unlike='<a  type="unlike" class="btn btn-danger status_unlike_'.$updateid.' unlike" attr="'.$updateid.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes[0].'</span>';
             
          }
          $share_button="";
                 
                  $hidden_text= '<textarea class="hidden_textarea hidden_value'.$updateid.'" disabled>'.$data.'</textarea>';
                   $share_button="<a class='btn btn-primary share_btn share_".$updateid."' title='".$title."' type='share' id='".$updateid."'>Copy</a>";
                     
             $reply_btn="";
                if ($author==$_SESSION['uname'] || $account_name==$_SESSION['uname']) {
                $statusdeletebutton='<li>'
                           . '<a type="'.$updateid.'" class="btn delete_4_session hidden_text_delete_'.$updateid.' glyphicon glyphicon-trash delete_status_btn" title="Delete this status and its replies">Remove</a></li>';
                $edit_btn='<li>'
                        . '<a attr="'.$updateid.'" type="'.$updateid.'" class="btn edit_4_session hidden_text_edit glyphicon glyphicon-pencil" title="Edit this status" >Edit</a></li>';
                $statusui_edit="<b class='i-select'><i class='no' style='display:none;' no='".($updateid?$updateid:$postid)."'>".($updateid?$updateid:$postid).",</i> <div type='".($updateid?$updateid:$postid)."' class='hide hidden_edit_4_session session_editor".($updateid?$updateid:$postid)." jumbotron'>"
            . "<a type='".$updateid."' class='btn pull-right close_edit' title='Close without editing'>Close X</a>"
            . "<input type='text' class='form-control title_s_edit title_s_".$updateid."' name='status_title' value='".html_entity_decode($title)."' placeholder='Title' >"
            . "<span>&nbsp;</span>"
            . "<textarea id='wall_edit_".$updateid."' type='".$updateid."' rows='5' cols='50'  class='session_edit text_value_".$updateid."' wrap='hard' placeholder='whats up ".$_SESSION['uname']."'>
             ".html_entity_decode($data1)."</textarea><br>"
            . "<span class='pull-left spinner_edit".$updateid."'></span><button style='float:right;' value='".$updateid."' type='a' class='btn btn-warning btn btn-large btn-lg post-s-edit'>Update</button></div></b>" ;
 
                }else{
                    $statusdeletebutton="";
                    $edit_btn="<li class='posted'>You are not the owner of this Post</li>";
                    $statusui_edit="";
                     $report_btn='<li><a href="#'.$updateid.'" attr="'.$_SESSION['id'].'" account-name="'.$author.'" type="'.$updateid.'" id="'.$f_uid.'"  class="report_offence glyphicon glyphicon-warning-sign" title="Edit this status" >Report</a></li>';
                }
          
       echo $statusui_edit.''. $hidden_text.''
           
               . '<div attr="'.$updateid.'" type="'.$updateid.'" class="statusboxes border border-warning  status_'.$updateid.'  jumbotron">'
                        . '<h3 class="pull-left title">'
                        . '<div id="#'.$updateid.'" attr="'.$updateid.'" class="title_s_2copy posted" 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 .''.$report_btn.'</ul></div></span><br><hr>'
                        . '<legend><span style="font-size: 13.5px;" class=" data_s_2copy" type="'.$updateid.'" >'
                        . '<div class="descrip" data-id="'.$updateid.'">'
                        . html_entity_decode($partial_data1)." <br>".$more.'</div></span></legend><b class="posted"><small>Posted by:-  <a href="home.php?u='.$author.'"><img src="'.$feed_pic.'" height="20px" width="20px"> '.$author.   '</a> on   '.$datemade.'</b>'
                        . '<br><span class="pull-left spinner_status'.$updateid.'"></span><legend>'.$like.' | '.$unlike. ' | '.$share_button.'</legend></small><span class="edit-spinner"></span>';
                       
       ?>
<h4><a id="<?php echo $updateid;?>" class="btn btn-default collap-btn">Comments</a></h4>
    <!-- Collapsible Element HTML -->
  <div id="toggle<?php echo $updateid;?>" class="collapse scroll-comments">
     
   <?php
// Fetch Comments of update
        $totalComments=$project->totalComments($_SESSION['uname'],$friend,$updateid);   
        foreach($totalComments 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'];
                $reply_data1=  hashtags($reply_data);
                //$reply_data1= taggingsys($reply_data0);
                //$reply_data1=  taggingsys($reply_data0);
                $datemade_r = strftime("%B %d, %y", strtotime($reply_date));
                  include 'ratings/vote_count4comments.php';
        
          $vote_up_count_r=$project->voteReplyCheck($_SESSION['id'], $status_reply_id);
          
        if($vote_up_count_r[0][0]>0){
            $vote_like_r=TRUE;
            
        } else {
        $vote_like_r=FALSE;    
        }
          if($vote_up_count_r[0][1]>0){
            $vote_dislike_r=TRUE;
            
        } else {
        $vote_dislike_r=FALSE;    
        }
        
        
          if ( $vote_like_r==TRUE && $vote_dislike_r==FALSE) {
           
          
               $like_r='<a  type="liked" class="btn btn-warning status_liker_'.$status_reply_id.' liked_r" attr="'.$status_reply_id.'">Liked</a>&nbsp;<span class="label label-info">'.$num_likes_r[0].'</span>';
             $unlike_r='<a  type="unlike" class="btn btn-danger status_unliker_'.$status_reply_id.' unlike_r" attr="'.$status_reply_id.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes_r[0].'</span>';
          }
 elseif ($vote_dislike_r==TRUE && $vote_like_r==FALSE) {
              
         $like_r='<a  type="like" class="btn btn-warning status_liker_'.$status_reply_id.' like_r" attr="'.$status_reply_id.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes_r[0].'</span>';
             $unlike_r='<a  type="unliked" class="btn  btn-danger status_unliker_'.$status_reply_id.' disliked_r" attr="'.$status_reply_id.'">Disliked</a>&nbsp;<span class="label label-info">'.$num_unlikes_r[0].'</span>';
          }  else {
             $like_r='<a  type="like" class="btn btn-warning status_liker_'.$status_reply_id.' like_r" attr="'.$status_reply_id.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes_r[0].'</span>';
             $unlike_r='<a  type="unlike" class="btn btn-danger status_unliker_'.$status_reply_id.' unlike_r" attr="'.$status_reply_id.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes_r[0].'</span>';
                     
          }
          $pic=$project->viewByUname($reply_author);
              $uid=$pic['user_id'];
         
           $avatar=$pic['avatar'];
          if ($avatar!=""){
          $feed_r_pic='../user/user/'.$uid.'/'.$avatar;
      }  else {
          $feed_r_pic='img/avatardefault.png';
          }
                 
        if ($reply_author==$_SESSION['uname'] ) {
                   $reply_delete_button='<li><a type="'.$status_reply_id.'" class="btn delete_reply_btn glyphicon glyphicon-trash delete_reply_"'.$status_reply_id.'" title="Delete this comment"> Remove</a></span></li>';
               } else{
                   $reply_delete_button="";
               }
              echo '
                <div  class="replyboxes pull-left reply_' .$status_reply_id.'">'
                      . 'Reply by:- '
                      . '<a href="home.php?u='.$reply_author. '"><img src="'.$feed_r_pic.'" height="20px" width="20px"> '.$reply_author.'</a>'
                      . '<span class="pull-right">'.$datemade_r 
                       . '<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_data1).'<br><span class="pull-left spinner_comment'.$status_reply_id.'"></span>'.$like_r.'|'.$unlike_r.'</legend></div>';
           
              
               } 
        echo '<textarea id="reply_textarea_'.$updateid.'" update-detail="'.$updateid.'" class="atwho-inputor status_reply_'.$updateid.' input-custom2" placeholder="comment\'s"></textarea>'
                            . '<span class="pull-left spinner_comment_post'.$updateid.'"></span><button id="reply_butn_'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply_butn reply_'.$updateid.'">Reply</button><span id="status_spinner_r"></span></div></div>';
    
 }
          /* 
           * group variables
           * 
           */

              
 elseif (isset ($row1['gp_id'])) {
              
         
      $postid=$row1['gp_id'];
        $post_auth=$row1['author_gp'];
        $post_type=$row1['type'];
        $post_title=  html_entity_decode($row1['title']);
        $post_data=  html_entity_decode($row1['data']);
        $data0=  hashtags($post_data);
              $data1=  taggingsys($data0);
        $post_date=$row1['pdate'];
        $post_avatar=$row1['avatar'];
        $post_uid=$row1['user_id'];
      if ($post_avatar != ""){
          $g_pic='../user/user/'.$post_uid.'/'.$post_avatar;
      }  else {
          $g_pic='img/avatardefault.png';
          }
          if ($post_auth==$_SESSION['uname']) {
               $edit="<div type='".$postid."' class='hidden_edit_g_description PB_G_".$postid." jumbotron'><span  id='spinner".$postid."'></span>"
            . "<a type='".$postid."' class='btn pull-right close_edit' title='Close without editing'>Close X</a>"
            . "<span>&nbsp;</span>"
        . "<input type='text' class='form-control title_g_edit title_g_".$postid."' name='g_status_title' value='".html_entity_decode($post_title)."' placeholder='Title' >"
            . "<textarea id='wall_edit_".$postid."' type='".$postid."' rows='5' cols='50'  class='session_edit text_value_".$postid."' wrap='hard' placeholder='update your description'>
             ".$post_data."</textarea><br>"
. "<span class='group_spinner'></span><button style='float:right;' value='".$postid."' type='edit_desc1' class='btn btn-warning btn btn-large btn-lg post-status-edit'>Update</button></div>" ;
$edit_btn= '<li><a attr="'.$postid.'" type="edit_desc1" class="btn edit_g_description hidden_text_edit glyphicon glyphicon-pencil" title="Edit this description" >Edit</a></li>';
 $statusdeletebutton='<li><a  type="'.$postid.'" class="btn delete_4_session hidden_text_delete_'.$postid.' glyphicon glyphicon-trash delete_status_btn" title="Delete this status and its replies">Remove</a></li>';
$report_btn="";         
 }  else {
              $edit="";
              $edit_btn="You are not the owner of this post";
              $statusdeletebutton="";
              $report_btn="<li><a href='#".$postid."' postid=".$postid." reporter-uid=".$_SESSION['id']." reporter-name=".$_SESSION['uname']." offender=".$post_auth." class='report glyphicon glyphicon-warning-sign '>Report</a></li>";
          }
        $user_image="<img src='{$g_pic}' alt='{$post_auth}' title='{$post_auth}' width='30' height='30'>";
        
                    $words=explode(" ", $data1);
         $count_words=count($words);
         if($count_words>180){
          $partial_data1= join(" ", array_slice($words,0,150));
          
          $more="<button class='btn btn-link more' data-id='".$postid."'>...More</button>";
         }else{
             $partial_data1=$data1;
             $more="";
         }
           
        $vote_up_count=$project->voteGroupCheck($postid, $_SESSION['id']);
     
        
        if($vote_up_count[0][0]>0){
            $vote_like=TRUE;
            
        } else {
        $vote_like=FALSE;    
        }
          if($vote_up_count[0][1]>0){
            $vote_dislike=TRUE;
            
        } else {
        $vote_dislike=FALSE;    
        }
        include 'ratings/vote_count_g.php';
        
          if ( $vote_like==TRUE && $vote_dislike==FALSE) {
           
          
             $like='<a  type="liked" class="btn btn-warning  liked status_like_'.$postid.' liked" attr="'.$postid.'">Liked</a>&nbsp;<span class="label label-info">'.$num_likes[0].'</span>';
             $unlike='<a  type="unlike" class="btn btn-danger  status_unlike_'.$postid.' unlike" attr="'.$postid.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes[0].'</span>';   
          }
 elseif ($vote_dislike==TRUE && $vote_like==FALSE) {
              
             $like='<a  type="like" class="btn  btn-warning  status_like_'.$postid.' like" attr="'.$postid.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes[0].'</span>';
             $unlike='<a  type="disliked" class="btn disliked btn-danger status_unlike_'.$postid.'" attr="'.$postid.'">Disliked</a>&nbsp;<span class="label label-info">'.$num_unlikes[0].'</span>';
          }  else {
             $like='<a  type="like" class="btn btn-warning  status_like_'.$postid.' like" attr="'.$postid.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes[0].'</span>';
             $unlike='<a  type="unlike" class="btn btn-danger status_unlike_'.$postid.' unlike" attr="'.$postid.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes[0].'</span>';
             
          }
#build threads.
        echo $edit."<div id='PB_".$postid."' value='".$postid."' class='jumbotron'><span  id='spinner".$postid."'></span>"
                . "<legend>{$post_title}"
                . "<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 ." ".$report_btn. "</ul></div></span><br></legend>"
                         . '<div class="descrip" data-id="'.$postid.'">'
                . "<legend>{$partial_data1}<br>{$more}<br></div><span class='spinner_g_u{$postid}'></span>{$like} | {$unlike} <br>Posted by:<a href='home.php?u={$post_auth}&v={$_SESSION['uname']}'>{$user_image} {$post_auth}</a>-- {$post_date}<hr><br></legend> <h4><a id='{$postid}' class='btn btn-default collap-btn'>Comments</a></h4><div class='collapse scroll-comments' id='toggle{$postid}'>";
                #get replies and user image using inner loop
               
                $num_rows1=$project->groupcount_replies($postid);
                $fetch1=$project->group_replies($postid);
               
                if ($num_rows1>0) {
                    foreach ($fetch1 as $row1) {
                        $reply_id=$row1['gp_id'];
                        $reply_pid=$row1['pid'];
                        $reply_auth=$row1['author_gp'];
                        $reply_data=$row1['data'];
                        $data11=  hashtags($reply_data);
              $data12=  taggingsys($data11);
                        $reply_date=$row1['pdate'];
                        $reply_avatar=$row1['avatar'];
                        $reply_uid=$row1['user_id'];
                       
                        if ($reply_avatar != ""){
          $r_pic='../user/user/'.$reply_uid.'/'.$reply_avatar;
      }  else {
          $r_pic='img/avatardefault.png';
          }
        /*  include 'ratings/vote_count_g4comments.php';
               $like_r='<a  type="like" class="btn status_liker_'.$reply_id.' like_r" attr="'.$reply_id.'">Like</a>('.$num_likes_r[0].')';
             $unlike_r='<a  type="unlike" class="btn status_unliker_'.$reply_id.' unlike_r" attr="'.$reply_id.'">Dislike</a>('.$num_unlikes_r[0].')';
     */
        $vote_up_count_r=$project->voteGroupReplyCheck($_SESSION['id'],$reply_id);
        
        if($vote_up_count_r[0][0]>0){
            $vote_like=TRUE;
            
        } else {
        $vote_like=FALSE;    
        }
          if($vote_up_count_r[0][1]>0){
            $vote_dislike=TRUE;
            
        } else {
        $vote_dislike=FALSE;    
        }
        include 'ratings/vote_count_g4comments.php';
        
          if ( $vote_like==TRUE && $vote_dislike==FALSE) {
           
          
             $like_r='<a  type="liked" class="btn btn-warning  status_like_'.$reply_id.' liked_r" attr="'.$reply_id.'">Liked</a>&nbsp;<span class="label label-info">'.$num_likes_r[0].'</span>';
             $unlike_r='<a  type="unlike" class="btn btn-danger  status_unlike_'.$reply_id.' unlike_r" attr="'.$reply_id.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes_r[0].'</span>';   
          }
 elseif ($vote_dislike==TRUE && $vote_like==FALSE) {
              
             $like_r='<a  type="like" class="btn btn-warning  status_like_'.$reply_id.' like_r" attr="'.$reply_id.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes_r[0].'</span>';
             $unlike_r='<a  type="unliked" class="btn btn-danger  disliked_r status_unlike_'.$reply_id.'" attr="'.$reply_id.'">Disliked</a>&nbsp;<span class="label label-info">'.$num_unlikes_r[0].'</span>';
          }  else {
             $like_r='<a  type="like" class="btn btn-warning status_like_'.$reply_id.' like_r" attr="'.$reply_id.'">Like</a>&nbsp;<span class="label label-info">'.$num_likes_r[0].'</span>';
             $unlike_r='<a  type="unlike" class="btn btn-danger status_unlike_'.$reply_id.' unlike_r" attr="'.$reply_id.'">Dislike</a>&nbsp;<span class="label label-info">'.$num_unlikes_r[0].'</span>';
             
          }          
                    $reply_img="<img src='{$r_pic}' alt='{$reply_auth}' title='{$reply_auth}' width='15' height='15'>";
      #build replies
                  
          echo "<div id='reply_body'>"
                . "<small>Posted by:<a href='home.php?u={$reply_auth}&v={$_SESSION['uname']}'>{$reply_img}  {$reply_auth}</a>-- {$reply_date}</small><br>"
                . "<legend>{$data12}<br><span class='spinner_g_r{$reply_id}'></span>{$like_r} | {$unlike_r} </legend></div>";
                
                }
          }
echo '<br>'
                  . '<textarea id="reply_textarea_'.$postid.'" update-detail="'.$postid.'" class="group_reply1 input-custom2" placeholder="comment\'s"></textarea>'
                            . '<br><span class="group_reply"></span><button  group-id="'.$_SESSION['g_id'].'"  user-id="'.$_SESSION['id'].'" action="post_reply" id="'.$postid.'" attr="'.$postid.'" type="b" class="btn btn-warning pull-right btn-sm reply_btn reply">Reply</button><br></div></div>';        
 }
     break;
        
               }
Jquery script:

 var load=0; 
     var sess_uname="<?php echo $session_uname;?>";
     var f_uname="<?php echo htmlspecialchars($_GET['u']);?>";
     var sess_count="<?php echo $total_sess_count;?>";
     var f_count="<?php echo $total_friend_count;?>";
    var funame="<?php echo $f_uname0;?>";
$(".message").html("<div style='display: flex; justify-content: center;'><h1>Loading Posts...</h1>    "+spinner1+"</div>").show();
   
    if(sess_uname==f_uname){
        
        $(window).scroll(function(){
       
       //alert($(window).scrollTop()); 
          // alert($(window).scrollTop()+" "+$(document).height()-$(window).height());
             if($(window).scrollTop()==$(document).height()-$(window).height()){
                 var sub=$(document).height()-$(window).height();
      //lert(sub);
           // alert($(window).scrollTop()+"  "+sub); 
            load++;      
            var ids1=[];
             var ids=$(".i-select i").text();
           ids1.push(ids);
            //var arr=$(ids).serializeArray();
         var arrJSON=JSON.stringify( ids1);  
        var strids=ids.toString();
       var newids = strids.split(",");
      
        var len=newids.length;
       // alert(newids);
          //  for(x=0;x<len;x++){
         // alert('ids:- ' + newids[x] +" len:-  "+ len);
         var newid1=newids[load];
    // alert(newids[x]);
   var first=$(".i-select i:first").text();      
   var last= $("i:last").text();
    //console.log(first+"--1---2-- "+last);     

                // alert(load+" "+sess_count);
             if(load * 5< sess_count){
                 $(".message").html("<h1>no more posts to show </h1>");
             }  else{
                 $.post("st&com.php", {"load":load,"sess_count":sess_count,"update_id2":last,"update_id1":arrJSON},function(data){
        $(".message").html(spinner1).show();         
        $("#status_area").append(data);
                    $(".hide").hide();
        $(".message").html(spinner1).hide();            
  
    }); }
             }
         });
     }else {
         $(window).scroll(function(){
           
             if($(window).scrollTop()==$(document).height()-$(window).height()){
             load++;
   // alert($(window).scrollTop()+"  "+$(document).height()-$(window).height());
             if(load *2 >f_count){
                 $(".message").html("<h1>no more posts to show here</h1>");
             }else{
                
                 $.post("status_list.php", {funame:funame, load:load},function(data){
        $(".message").html(spinner1).show();         
        $("#status_area").append(data);
                    $(".hide").hide();
        $(".message").html(spinner1).hide();            
        
                 });
             }
             }
         });    
     } 

sample output: 

Array
(
    [gp_id] => 103
    [0] => 103
    [pid] => 0
    [1] => 0
    [gname] => MEP news
    [2] => MEP news
    [author_gp] => aboutthecreator
    [3] => aboutthecreator
    [type] => a
    [4] => 0
    [title] => qwertyu
    [5] => 2
    [data] => 
    [6] => <div>Etiam iaculis nunc ac metus. Praesent egestas tristique nibh.</div>
<div>&nbsp;</div>
<div>Fusce ac felis sit amet ligula !qwerty pharetra condimentum. Integer ante arcu, accumsan a, consectetuer eget, posuere ut, mauris.</div>
<div>&nbsp;</div>
<div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien !bart, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div>
<div>&nbsp;</div>
<div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero. !qwerty</div>
    [pdate] => 2019-12-19 22:28:04
    [7] => 2019-12-19 22:28:04
    [vote_up] => 0
    [8] => 0
    [vote_down] => 0
    [9] => 0
    [group_id] => 25
    [10] => 25
    [author_id] => 142
    [11] => 142
    [avatar] => ThuJul1821235420191075.jpg
    [12] => ThuJul1821235420191075.jpg
    [user_id] => 142
    [13] => 142
    [update_id] => 381
    [14] => 381
    [update_body] => Lorem ipsum dolor sit amet,consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Lorem ipsum dolor sit amet, <a href="home.php?u=shan2batman">@shan2batman</a><br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam voluptua. !qwerty Lorem ipsum dolor sit amet, consetetur sadipscing elitr, Lorem ipsum dolor sit amet, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, consetetur sadipscing elitr, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. no sea takimata sanctus est Lorem ipsum dolor sit amet. <br /><br />Lorem ipsum dolor sit amet,sed diam voluptua. Lorem ipsum dolor sit amet, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, consetetur sadipscing elitr, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam voluptua. Stet clita kasd gubergren, Lorem ipsum dolor sit amet, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, <br /><br />Lorem ipsum dolor sit amet,At vero eos et accusam et justo duo dolores et ea rebum. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, sed diam voluptua. Stet clita kasd gubergren, <br /><br />
    [15] => Lorem ipsum dolor sit amet,consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Lorem ipsum dolor sit amet, <a href="home.php?u=shan2batman">@shan2batman</a><br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam voluptua. !qwerty Lorem ipsum dolor sit amet, consetetur sadipscing elitr, Lorem ipsum dolor sit amet, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, consetetur sadipscing elitr, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. no sea takimata sanctus est Lorem ipsum dolor sit amet. <br /><br />Lorem ipsum dolor sit amet,sed diam voluptua. Lorem ipsum dolor sit amet, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, consetetur sadipscing elitr, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam voluptua. Stet clita kasd gubergren, Lorem ipsum dolor sit amet, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, <br /><br />Lorem ipsum dolor sit amet,At vero eos et accusam et justo duo dolores et ea rebum. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, sed diam voluptua. Stet clita kasd gubergren, <br /><br />
    [url] => 
    [16] => 
    [time] => 2018-11-10 22:58:36
    [17] => 2018-11-10 22:58:36
    [host] => 
    [18] => 
    [19] => 0
    [20] => 0
    [21] => qwertyu
    [user_id_u] => 142
    [22] => 142
    [account_name] => aboutthecreator
    [23] => aboutthecreator
    [author] => aboutthecreator
    [24] => aboutthecreator
    [25] => a
    [26] => 
)

 

Share this post


Link to post
Share on other sites

I doubt anyone is going to try to read through and comprehend all of that code to try and deduce why you are having a problem. YOU need to do some debugging to narrow down the problem and then you are likely to get some responses.

 

I'm certainly not going to read through all that and try to figure out what the code is doing in and where your problem may be, but one thing that jumps out at me is the query you are using and the use of LIMIT with a start index. I don't know if you have people actively using the application while you are seeing duplicates, but the use of a start index will not work if new posts are being created. Let's say you grab the first five records (indexes 0-4), then before you grab the next five records, someone adds another record. When you go to get records based on a start index of 5, the record that was index of 4 on your first query will now be an index of 5 and cause a duplicate to be displayed.

I would suggest passing the ID or timestamp of the last record that was previously obtained. Then fashion your query something like this

SELECT ....

FROM ...

WHERE ...

  AND date_created < (SELECT date_created FROM ... WHERE id = [id of last record])

LIMIT 5

 

Share this post


Link to post
Share on other sites

@Psycho thanks but your mysql suggestion didn't bring up a single post. i'm sorry for making it lengthy some ppl in the forum expect full code instead of shortened one's thats why i posted it so lengthy sorry again if it irritated or made you angry.

Share this post


Link to post
Share on other sites
3 hours ago, narutofan said:

@Psycho thanks but your mysql suggestion didn't bring up a single post. i'm sorry for making it lengthy some ppl in the forum expect full code instead of shortened one's thats why i posted it so lengthy sorry again if it irritated or made you angry.

My suggestion was just an example. It will work if implemented correctly. People want to see all the relevant code for a problem. It is incumbent upon the person with the problem to do the basic trouble shooting to determine the area of the code where the problem exists and provide that code. The person should also provide what the code is expected to do and what is IS doing differently than the expectation. I gave a potential explanation of why you would get duplicates if you are simply basing the additional records to get based on an index when ordered by date/time. When you are seeing duplicates - are there other records being added to the system that would be included in the user's feed? If so, that is likely why you are seeing duplicates. Here is an illustrative example:

Let's say there are 6 records added from A - I. When you retrieve the first five records in reverse chronological order using LIMIT 0, 5  (which gets five records starting at index 0, i.e. 0 - 4) you will get I-E, because the query will see something like this (number in parenthesis represent the index of the records matching the criteria).

Quote

I (0)
H (1)
G (2)
F (3)
E (4)

D (5)
C (6)
B (7)
A (8)

Now, let's say a new record is added (J) THEN you attempt to get the next five records using LIMIT 5, 5 (i.e. indexes 5-9). You will get a duplicate of record E, because it will have moved from index 4 to 5.

Quote

J (0)
I (1)
H (2)
G (3)
F (4)
E (5)
D (6)
C (7)
B (8)
A (9)

Based on your function totalUpdates() that returns five records based on an arbitrary $load variable to indicate the limit start index. I can't say that IS your problem, but it will be a problem if records are being added in-between the load calls. In any event, I suggest you add some logging to see what is going on. I would start by logging the calls to the totalupdates() function to verify that the $load value is what you expect (0, 5, 10, 15, etc.). I would also log the total number of records that match the conditions of the query (w/o the limit condition). That requires adding a second copy of the query with a count() instead of returning the results, but it would only be used for debugging purposes.

Share this post


Link to post
Share on other sites
Posted (edited)

@Psycho don't get agitated on me for my poor words(its not my native tongue pl bear with me). i stated the problem clearly i couldn't separate update table results with group_post table results from the join query i tried to separate them using isset() in a if condition but i fail, to make things easier i even posted a sample output from the query. since i couldn't comeup with an answer i came here to get your and others expert opinion and by the learning way i would remove duplicate results too. 

Edited by narutofan
correction

Share this post


Link to post
Share on other sites
On 1/1/2020 at 11:13 PM, narutofan said:

    $sql2="select distinct g.*,u.avatar,u.user_id,up.*

            from group_posts as g "

            . " join user as u on uname=g.author_gp "

            . " join updates as up on u.uname=up.author"

Do not use "SELECT * ", specify the columns you need.

This is especially true when joining tables and those tables have columns with the same names. In this case you should use column aliases if you need both.

EG

SELECT g.type as gtype
       u.type as utype
FROM ...

 

The default fetch mode is PDO::FETCH_BOTH so your output with contain each column value with both a column name  and a column number as array keys

Examining you example output, this is the case for the first few columns ...

Array
(
    [gp_id] => 103
    [0] => 103
    [pid] => 0
    [1] => 0
    [gname] => MEP news
    [2] => MEP news
    [author_gp] => aboutthecreator
    [3] => aboutthecreator

but then you have ...

    [type] => a
    [4] => 0
    [title] => qwertyu
    [5] => 2

and the relationship has broken down because a column "type" from a second table has overwritten the value for column "type" in the first table"

Checking the later columns in your example output finds numeric keys without corresponding name keys (the duplicated names that were overwritten) ..

    [19] => 0
    [20] => 0
    [21] => qwertyu

Depending on how you process these results you will get different outcomes.

When you open your PDO connection, specify a default fetch mode EG

$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

then, on occasions when you specifically need the numeric keys, do ...

$row = $stmt->fetch(PDO::FETCH_NUM);

 

Share this post


Link to post
Share on other sites

@Barand thanks for the soln. now i face a new problem with the query. it produces only one update_id which is duplicating all posts. i'll attach a screen shot from the phpmyadmin below and advice me on how to approach and solve this problem. 

here is the query:

select distinct gp.gp_id as gp1,gp.pid,gp.author_gp,gp.gname,gp.type,gp.title,gp.data,gp.pdate,gp.group_id,gp.author_id,
         u.avatar,u.user_id ,
            up.update_id as up1,up.update_body,up.time,up.title,up.account_name,up.author,up.type,up.data  
            from group_posts as gp
           join user as u on u.uname=gp.author_gp 
            join updates as up on u.uname=up.author
            where 
            gp.gname='MEP news' 
            and up.update_id >391  and up.author in("shan2batman", "aboutthecreator") order by time,pdate desc limit 0,5

here is the screenshot:

 

Screenshot from 2020-01-03 21-50-28.png

Share this post


Link to post
Share on other sites

In my previous post I outlined the dangers of having duplicate column names in your tables and to use aliases to differentiate when this occurs.

But it appears that you couldn't be bothered to read it because you immediately came up with this query with three sets of duplicates...

select distinct 
        gp.gp_id as gp1
        ,gp.pid
        ,gp.author_gp
        ,gp.gname
        ,gp.type                                --+
        ,gp.title                       --+       |
        ,gp.data                          | --+   |
        ,gp.pdate                         |   |   |
        ,gp.group_id                      |   |   |
        ,gp.author_id                     |   |   |
        ,u.avatar                         |   |   |
        ,u.user_id                        |   |   |
        ,up.update_id as up1              |   |   |
        ,up.update_body                   |   |   |
        ,up.time                          |   |   |
        ,up.title                       --+   |   |
        ,up.account_name                      |   |
        ,up.author                            |   |
        ,up.type                              | --+
        ,up.data                            --+
from group_posts as gp
    join user as u on u.uname=gp.author_gp 
    join updates as up on u.uname=up.author
where 
    gp.gname='MEP news' 
    and up.update_id >391  
    and up.author in("shan2batman", "aboutthecreator") 
order by time,pdate desc limit 0,5

Stop wasting our time.

Share this post


Link to post
Share on other sites

@Barand i tried that combination in mysql and goit the same duplicate entry earlier. so, tried a new one incase you don't believe me see the latest execution of that query here. i get the same duplicate posts problem.750910045_Screenshotfrom2020-01-0422-02-40.thumb.png.4235057621d79360179cf4e693cea1ab.png

Share this post


Link to post
Share on other sites

@Barand sir if u need further data to work on i can arrange exporting the individual tables from my side. the call is yours.

Share this post


Link to post
Share on other sites

@Barand tried a new union query and got this response which is weird.

query:

  (select DISTINCT
           up.update_id,
           up.update_body,
           up.time,
           up.title as tit2,
           up.account_name,
           up.author,
           up.type as ty2,
           up.data as dat2 
           from updates as up 
           where 
           up.author in('shan2batman', 'aboutthecreator') and up.update_id not in(381,393)
           order by time desc limit 0,5)
           
           UNION
           (
           select distinct 
               gp.gp_id as gp1,
gp.pid,
gp.author_gp,
gp.gname,
gp.type as ty1,
gp.title as tit1,
gp.data as dat1,
gp.pdate


 from group_posts as gp
join user as u on u.uname=gp.author_gp

where 
            gp.gname='MEP news' and u.uname='aboutthecreator'            
    
           
 order by pdate DESC   limit 0, 5)

 

Screenshot from 2020-01-06 15-42-32.png

Share this post


Link to post
Share on other sites

What you are calling duplicates are not, in fact, duplicates. I'm curious what made you think a UNION clause was needed?

In the response to @Barand's query you showed a result set of five records each having a different gp_id value In other words, they are unique records. I can see that they contain much of the same data, but they are distinct records. What makes you consider them "duplicates"? I think your problem may stem from a schema problem. I see multiple things in what you are doing that don't make sense. For example, you are joining the user and updates tables using the name of the author? You would typically be joining tables based on IDs rather than arbitrary string values. Is the group_posts table for conversations between multiple individuals and are you adding a new record for each response in that conversation? What determines which records are form the same conversation?

Share this post


Link to post
Share on other sites
Posted (edited)

@Psycho i'm calling the update table values as duplicate records. The group posts show unique values as you mentioned sir. i'll try to do the changes you asked and post the output here. yes the group_posts is between multiple people(but the site isn't live its my hand you see in the posts from different acc's i created to test the code and query sir) the pid you see in group posts is comment to a parent data. if you see number instead of zero in pid column then it is likely to be a comment for a parent post ,thats , how a conversation goes in groups you comment to a parent post..

(i'm also posting the sql dump to see anything odd in it)

@barand let me know if you see anything odd. 

Group_posts sql DUMP:-

CREATE TABLE `group_posts` (
  `gp_id` int(255) NOT NULL,
  `pid` varchar(16) NOT NULL,
  `gname` varchar(100) NOT NULL,
  `author_gp` varchar(255) NOT NULL,
  `type` enum('0','1') NOT NULL,
  `title` varchar(500) NOT NULL,
  `data` varchar(10000) NOT NULL,
  `pdate` datetime NOT NULL,
  `vote_up` int(255) NOT NULL,
  `vote_down` int(255) NOT NULL,
  `group_id` int(255) NOT NULL,
  `author_id` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `group_posts`
  ADD PRIMARY KEY (`gp_id`),
  ADD KEY `group_id` (`group_id`),
  ADD KEY `author_id` (`author_id`),
  ADD KEY `group_id_2` (`group_id`,`author_id`),
  ADD KEY `pid` (`pid`),
  ADD KEY `gp_id` (`gp_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `group_posts`
--
ALTER TABLE `group_posts`
  MODIFY `gp_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=105;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `group_posts`
--
ALTER TABLE `group_posts`
  ADD CONSTRAINT `group_posts_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`g_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `group_posts_user_id` FOREIGN KEY (`author_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

updates table DUMP:-

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `project`
--

-- --------------------------------------------------------

--
-- Table structure for table `updates`
--

CREATE TABLE `updates` (
  `update_id` int(255) NOT NULL,
  `update_body` varchar(10000) NOT NULL,
  `url` varchar(100) NOT NULL,
  `time` datetime NOT NULL,
  `host` varchar(100) NOT NULL,
  `vote_up` int(255) NOT NULL,
  `vote_down` int(255) NOT NULL,
  `title` varchar(1000) NOT NULL,
  `user_id_u` int(255) NOT NULL,
  `account_name` varchar(255) NOT NULL,
  `author` varchar(255) NOT NULL,
  `type` enum('a','b','c') NOT NULL,
  `data` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `updates`
--
ALTER TABLE `updates`
  ADD PRIMARY KEY (`update_id`),
  ADD KEY `user_id_fk_upd` (`user_id_u`),
  ADD KEY `user_id_fk_upd_2` (`user_id_u`),
  ADD KEY `user_id_u` (`user_id_u`),
  ADD KEY `user_id_u_2` (`user_id_u`),
  ADD KEY `update_id` (`update_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `updates`
--
ALTER TABLE `updates`
  MODIFY `update_id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=399;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `updates`
--
ALTER TABLE `updates`
  ADD CONSTRAINT `updates_ibfk_1` FOREIGN KEY (`user_id_u`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;

 

Edited by narutofan

Share this post


Link to post
Share on other sites

@Psycho here is a execution of the query you asked to run with ids instead of strings.

 

select distinct gp.gp_id as gp1,gp.pid,gp.author_gp,gp.gname,gp.type as ty1,gp.title as tit1,gp.data as dat1,gp.pdate,gp.group_id,gp.author_id,
   
            up.update_id as up1,up.update_body,up.time,up.title as tit2,up.account_name,up.author,up.type as ty2,up.data as dat2 
            from group_posts as gp
          
           inner join updates as up on gp.author_id=up.user_id_u
            where 
            gp.group_id=25 and gp.author_id=127 and up.update_id not in(386)
           order by time,pdate desc limit 0,5

here is the output's screenshot you can notice up1 and the rest of the table coming up with duplicate values eg)288

1131059762_Screenshotfrom2020-01-0703-23-34.thumb.png.42117064f49bd57af30a95cd8a2a1fd2.png1270772601_Screenshotfrom2020-01-0703-24-07.thumb.png.46717056c8f94879845ea29d8a7f0265.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.