need help with commenting system

[shan2batman]

Guys I'm trying to actually create a commenting system in my example site(for educational purposes). But the problem that I face now is that it is not working as it is supposed to as, the comments for the post with id 116is displaying for all the posts. But should only show for status with the id 116. i created the post system to have only the type “**a**”or “**c**” and the comments to be “b” to be inserted in to mysql. But the comments are showing up for all Posts, no matter where I comment it shows up for all posts. Any help would be appreciated.

Here is the code that has the post logic:

<?php

include '../includes/dbconfig.inc.php';


$status2view=$project->statusView($f_uname);


foreach($status2view as $row){

//print_r($row);

$updateid=$row['update_id'];



//gives output on var_dump



$status_reply_=$project->reply2StatusView($updateid);



foreach ($status_reply_ as $row) {


$status_reply_id=$row['update_id'];

$reply_author=$row['author'];

$reply_d=htmlentities($row['update_body']);

$reply_data= stripslashes($reply_d);

$reply_osid=$row['os_id'];

$account_name=$row['account_name'];

$reply_date=$row['time'];

$reply_delete_button="";

if ($reply_author==$session_uname || $account_name==$session_uname) {

$reply_delete_button="<li><span id='$status_reply_id' class='delete_reply_btn glyphicon glyphicon-remove'><a href='#' title='Delete this comment'>Remove X</a></span></li>";

}

if ($reply_osid!=$updateid) {


$status_replies.=""; 


} else {


$status_replies.="<div class='replyboxes pull-left reply_".$status_reply_id."'><b>Reply by:-<a href='search_results.php?u=".$reply_author."'>".$reply_author."</a>"

. "<span class='pull-right'>".$reply_date 

. "<b class='caret'>

<small><span class='btn-xs btn-danger dropdown-toggle pull-right' data-toggle='dropdown' aria-expanded='true' ><span class='glyphicon glyphicon-edit'></span>

<ul class='dropdown-menu'>".$reply_delete_button

. "<li><a href='#' class='hidden_text_area glyphicon glyphicon-pencil reply_".$status_reply_id."' title='Edit this comment' >Edit</a></li>"

. "<li><a href='report.php?u='".$reply_author."'>Report</a><li></ul>"

. "</span></span></small></b><br><legend>". html_entity_decode($reply_data)."</legend><br></div>";



}

}

} 

foreach ($status2view as $row1) {


//got values here.

$updateid=$row1['update_id'];

$account_name=$row1['account_name'];

$os_id=$row1['os_id'];

$author=$row1['author'];

$post_date=$row1['time'];

$title= stripslashes($row1['title']);

$data= stripslashes($row1['update_body']);

$statusdeletebutton='';

//insert_status_ui script to get message.


if($isowner=="yes"){

$statusui_edit="<div type='".$updateid."' class='hidden_edit_4_session session_editor".$updateid." jumbotron'>"

. "<a href='#' type='".$updateid."' class='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_1' type='".$updateid."' rows='5' cols='50' class='session_edit text_value_".$updateid."' wrap='hard' placeholder='whats up ".$session_uname."'>

".html_entity_decode($data)."</textarea><br>"

. "<button style='float:right;' value='".$updateid."' type='a' class='btn btn-warning btn btn-large btn-lg post-s-edit'>Update</button></div>" ;



}

elseif ($is_friend==TRUE&&$session_uname!=$f_uname) {



$statusui_edit="<div type='".$updateid."' class='hidden_edit_4_friend friend_editor".$updateid." jumbotron'>"

. "<a title='Close without editing' type='".$updateid."' href='#' class='pull-right close_edit_f'>Close X</a>"

. "<input type='text' class='form-control title_f_edit title_f_".$updateid."'' name='status_title' value='".html_entity_decode($title)."' placeholder='Title'><br>"

. "<div> </div>"

. "<textarea id='wall_edit_2' value='' type='".$updateid."' rows='5' cols='50' class='friend_edit update_friend_".$updateid."' placeholder='hi ".$session_uname." want to say something to ".$f_uname.". '>"

.html_entity_decode($data)."</textarea><br>"

. "<button style='float:right;' value='".$updateid."' type='c' class='btn btn-warning btn-large btn-lg post-f-edit'>Update</button></form></div>";

}


if ($author==$session_uname || $account_name==$session_uname) {

$statusdeletebutton='<li>'

. '<a href="#" type="'.$updateid.'" class="delete_4_session hidden_text_delete_'.$updateid.' glyphicon glyphicon-trash delete_reply_btn" title="Delete this status and its replies">Remove</a></li>';

}

if($isowner=="yes"){

$status_list= $statusui_edit.'<div attr="'.$updateid.'" type="'.$updateid.'" class="statusboxes status_'.$updateid.' jumbotron">'

. '<h3 style="color:black; margin-bottom:5px; margin-top:5px;" class="pull-left">'

. '<div id="'.$updateid.'" class="title_s_2copy" value="'.html_entity_decode($title).'">'.html_entity_decode($title).'</div></h3>'

. '<span class="pull-right">'

. '<div class="dropdown">'

. '<button type="button" class="btn btn-danger dropdown-toggle" data-toggle="dropdown" >'

. '<span class="glyphicon glyphicon-edit"></span></button>'

. '<ul class="dropdown-menu">'

. '<li><a href="#" attr="'.$updateid.'" type="'.$updateid.'" class="edit_4_session hidden_text_edit glyphicon glyphicon-pencil" title="Edit this status" >Edit</a></li>'.$statusdeletebutton.'</ul></div></span><br><hr>'

. '<legend><span class=" data_s_2copy" type="'.$updateid.'" value="'.html_entity_decode($data).'">'

. html_entity_decode($data).'</span><br><br></legend><b style="text-align:right; color:black;"><small>Posted by:- <a href="search_results.php?u='.$author.'">'.$author. '</a> '.$post_date.'</small></b>'

. '<br><p>'.$status_replies.'</p><br>';


if ($is_friend==TRUE||$session_uname==$f_uname) {

$status_list.= '<textarea id="'.$updateid.'" class="status_reply_'.$updateid.' input-custom2" placeholder="comment\'s"></textarea>'

. '<button id="reply_btn_'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply_btn reply_'.$updateid.'">Reply</button></div>';

}


}elseif ($is_friend==TRUE&&$session_uname!=$f_uname) {

$status_list= $statusui_edit.'<div attr="'.$updateid.'" type="'.$updateid.'" class="statusboxes status_'.$updateid.' jumbotron">'

. '<h3 style="color:black; margin-bottom:5px; margin-top:5px;" class="pull-left">'

. '<div id="'.$updateid.'" class="title_s_2copy" value="'.html_entity_decode($title).'">'.html_entity_decode($title).'</div></h3><br><hr>'

. '<legend><span class=" data_s_2copy" type="'.$updateid.'" value="'.html_entity_decode($data).'">'

. html_entity_decode($data).'</span><br><br></legend><b style="text-align:right; color:black;"><small>Posted by:- <a href="search_results.php?u='.$author.'">'.$author. '</a> '.$post_date.'</small></b>'

. '<br><p>'.$status_replies.'</p><br>';

$status_list.= '<textarea id="'.$updateid.'" class="status_update input-custom2" placeholder="comment\'s"></textarea>'

. '<button id="reply_btn'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply_btn reply_'.$updateid.'">Reply</button></div>';



}

echo $status_list;

} 

here is the class method:

public function statusView($f_uname) {

$sql="select * from updates where account_name=:either and type='a' or account_name=:either and type='c' order by time desc limit 20";

$stmth=$this->_db->prepare($sql);

$stmth->bindValue(":either",$f_uname);

$stmth->execute();

return $stmth->fetchAll(PDO::FETCH_ASSOC);


}

public function reply2StatusView($updateid){

try{

$stmth= $this->_db->prepare("select * from updates where os_id=:statusid and type='b' order by time asc");

$stmth->bindparam(":statusid", $updateid);

$stmth->execute();

return $stmth->fetchAll(PDO::FETCH_ASSOC);

}

catch (Exception $exc){

echo $exc->getMessage();

}

}

[gizmola]

We don't know what your database structure looks like.

 

Are we to assume that this query is the one in question?

 

select * from updates where account_name=:either and type='a' or account_name=:either and type='c' order by time desc limit 20

You mention ID, but then you query for account_name? Is account_name the ID?

 

You should verify what is being bound to the query in the :either variable.

 

You can also simplify your query using IN.

 

 

select * from updates where account_name=:either and type IN ('a', 'c') order by time desc limit 20

[shan2batman]

thanks, gizmola, actually the first query is to actually fetch the posts and the second query is to actually fetch comments since im not that good in mysql i dont know how to join them both in my code. any help would be appreciated. once again thanks.

 

actually there is no id involved in procuring the posts that is just the author name.

Edited November 3, 2015 by shan2batman

[shan2batman]

update:

i did do a "union all" query in a separate page to get the comments attached to the posts but alas the comments just appear for all posts where it shouldn't to. can anyone shed any lights on this ???

the database structure is like this :

the user posts an article which is of type"a" or "c", and the other users can reply to the post which is of type "b". which is inserted in a single table called updates. The os_id is created using the "last insert id" through php and update_id is auto increment the rest are session variables for account_name &author and user input for update_body and title.

Edited November 3, 2015 by shan2batman

[shan2batman]

i'm attaching a screenshot of how the DB looks like.

[Jacques1]

The problem is that you're stuffing all kinds of different entities into this weird updates table, and then you try to separate them again using cryptic type identifiers like “a”, “b”, “c”.

 

I've checked your problem a couple of times, but then I just gave up, because none of this makes sense to me. Even if we could somehow fix your query, that wouldn't really help, because the underlying design would still be broken.

 

So I strongly recommend that you fix your database schema before you write any line of code. SQL isn't Excel. When you have different entities, you need to put them into different tables rather than using some kind of gigantic spreadsheet for everything. You should also use clear, unambiguous identifiers which other people (and your future self) can actually understand. What is “os_id” supposed to mean? Why is there both an “account_name” and an “author”? What do “a”, “b” and “c” represent again? This is just incredibly confusing and makes it difficult to understand even this trivial task.

[shan2batman]

so you are suggesting to redesign the way it works completely @jacques1 (sorry my code is messy im still learning), ill do what u suggested. and will come here if i couldn't find any resources online. thanks anyway bro.

[Jacques1]

When you design a database schema, it's usually good idea to forget about PHP code, MySQL queries etc. and just look at the data. Write down what you want to store and how the different entities are related. Diagrams are also helpful.