narutofan Posted January 1, 2020 Share Posted January 1, 2020 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> <span class="label label-info">'.$num_likes[0].'</span>'; $unlike='<a type="unlike" class="btn btn-danger status_unlike_'.$updateid.' " attr="'.$updateid.'">Dislike</a> <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> <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> <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> <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> <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> </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> <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> <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> <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> <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> <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> <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> </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> <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> <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> <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> <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> <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> <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> <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> <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> <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> <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> <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> <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> </div> <div>Fusce ac felis sit amet ligula !qwerty pharetra condimentum. Integer ante arcu, accumsan a, consectetuer eget, posuere ut, mauris.</div> <div> </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> </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] => ) Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/ Share on other sites More sharing options...
Psycho Posted January 2, 2020 Share Posted January 2, 2020 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 Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573048 Share on other sites More sharing options...
narutofan Posted January 2, 2020 Author Share Posted January 2, 2020 @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. Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573069 Share on other sites More sharing options...
Psycho Posted January 2, 2020 Share Posted January 2, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573076 Share on other sites More sharing options...
narutofan Posted January 3, 2020 Author Share Posted January 3, 2020 (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 January 3, 2020 by narutofan correction Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573089 Share on other sites More sharing options...
Barand Posted January 3, 2020 Share Posted January 3, 2020 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); Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573093 Share on other sites More sharing options...
narutofan Posted January 3, 2020 Author Share Posted January 3, 2020 @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: Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573095 Share on other sites More sharing options...
Barand Posted January 3, 2020 Share Posted January 3, 2020 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. Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573096 Share on other sites More sharing options...
narutofan Posted January 4, 2020 Author Share Posted January 4, 2020 @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. Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573105 Share on other sites More sharing options...
narutofan Posted January 6, 2020 Author Share Posted January 6, 2020 @Barand sir if u need further data to work on i can arrange exporting the individual tables from my side. the call is yours. Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573143 Share on other sites More sharing options...
narutofan Posted January 6, 2020 Author Share Posted January 6, 2020 @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) Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573148 Share on other sites More sharing options...
Psycho Posted January 6, 2020 Share Posted January 6, 2020 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? Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573149 Share on other sites More sharing options...
narutofan Posted January 6, 2020 Author Share Posted January 6, 2020 (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 January 6, 2020 by narutofan Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573150 Share on other sites More sharing options...
narutofan Posted January 6, 2020 Author Share Posted January 6, 2020 @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 Quote Link to comment https://forums.phpfreaks.com/topic/309779-not-able-to-create-a-facebook-like-timeline/#findComment-1573155 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.