Guys im trying to join a comments table to posts table in the following query.
$sql3="
select u.update_body
, u.author
, u.time
, u.title
, c.comment_body
, c.os_id
, c.author
, c.time
from updates as u
join comment_update as c
on c.os_id = :statusid
and u.account_name = :session
and u.type = ('a'&'c')
or u.account_name = :friend
and u.type = ('a'&'c')
order
by u.time asc
, c.time desc";
$stmth=$conn->prepare($sql3);
$stmth->bindValue(":session",$_SESSION['uname']);
$stmth->bindValue(":friend",$friend);
$stmth->bindValue(":statusid",$updateid);
$stmth->execute();
and it returns empty results dont know where i'm going wrong???. Any help would be appreciated.
here is my db schema:
as per arsh's request im posting the whole code here:
<?php
include "includes/dbconfig.inc.php";
$status_replies="";
$status_list="";
$statusui_edit="";
$isowner="";
$is_friend="";
$friends = array();
$stmt= $conn->prepare("select friend_one, friend_two from friends where "
. "(friend_one=:session OR friend_two=:session) and accepted='1'");
$stmt->bindparam(":session",$_SESSION['uname']);
$stmt->execute();
$status2view=$project->statusView($_SESSION['uname']);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $i=> $r ) {
$r["friend_one"] == $_SESSION['uname'] ? $friends[]= $r["friend_two"] : $friends[] = $r["friend_one"];
$friend=$friends[$i];
foreach ($status2view as $val) {
$updateid=$val['update_id'];
//select all relevant comments using the following query and print it.
$sql1="select u.update_body,u.author,u.time,u.title,"
. "c.comment_body, c.os_id,c.author,c.time "
. "from updates as u join comment_update as c "
. "on c.os_id=:statusid and u.account_name = :session and u.type = ('a'&'c') or u.account_name=:friend and u.type = ('a'&'c') order by u.time asc,c.time desc";
$sql2=" select u.update_body,u.author,u.time,u.title,"
. "c.comment_body, c.os_id,c.author,c.time "
. "from updates as u join comment_update as c "
. "on c.os_id=u.update_id where u.account_name = :session and u.type = ('a'&'c') or u.account_name=:friend and u.type = ('a'&'c') order by u.time asc,c.time desc";
$stmth=$conn->prepare($sql2);
$stmth->bindValue(":session",$_SESSION['uname']);
$stmth->bindValue(":friend",$friend);
//$stmth->bindValue(":statusid",$updateid);
$stmth->execute();
$status_reply= $stmth->fetchAll(PDO::FETCH_ASSOC);
print_r($status_reply);
foreach ($status_reply as $row1) {
$status_reply_id=$row1['comment_id'];
$reply_author=$row1['author'];
$reply_d=htmlentities($row1['comment_body']);
$reply_data= stripslashes($reply_d);
$reply_osid=$row1['os_id'];
$reply_date=$row1['time'];
$reply_delete_button="";
if ($reply_author==$_SESSION['uname'] ) {
$reply_delete_button="<li><a href='#'type='".$status_reply_id."' class='delete_reply_btn glyphicon glyphicon-trash delete_reply_".$status_reply_id."' title='Delete this comment'> Remove</a></span></li>";
}
if ($reply_osid==$updateid && $isowner=="yes") {
$status_replies="<div class='replyboxes pull-left reply_".$status_reply_id."'>"
. "Reply by:- "
. "<a href='home.php?u=".$reply_author."'>".$reply_author."</a>"
. "<span class='pull-right'>".$reply_date
. "<b class='dropdown'>
<small><span class='btn btn-xs btn-danger dropdown-toggle pull-right' data-toggle='dropdown' >
<span class='glyphicon glyphicon-edit'></span></span>
<ul class='dropdown-menu'>".$reply_delete_button
. "<li><a class='glyphicon glyphicon-warning-sign' href='report.php?u=".$reply_author."'> Report</a><li></ul></span>"
. "</small></b><br><legend>". html_entity_decode($reply_data)."</legend><br></div>";
}
elseif ($reply_osid==$updateid && $is_friend==TRUE && $session_uname!=$f_uname) {
$status_replies.="<div class='replyboxes pull-left reply_".$status_reply_id."'>"
. "Reply by:-"
. "<a href='home.php?u=".$reply_author."'>".$reply_author."</a>"
. "<span class='pull-right'>".$reply_date."
<b class='dropdown'>
<small><span class='btn btn-xs btn-danger dropdown-toggle pull-right' data-toggle='dropdown' >
<span class='glyphicon glyphicon-edit'></span></span>
<ul class='dropdown-menu'>".$reply_delete_button
. "<li><a class='glyphicon glyphicon-warning-sign report_btn report_".$updateid."' type='".$updateid."' href='report.php?u=".$reply_author."'> Report</a><li></ul></span>"
. "</small></b><br>"
. "</span><br><legend>". html_entity_decode($reply_data)."</legend><br></div>";
}
else {
$status_replies.="";
}
$updateid=$row1['update_id'];
$author=$row1['author'];
$post_date=$row1['time'];
$title= stripslashes($row1['title']);
$data= stripslashes($row1['update_body']);
$statusdeletebutton='';
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>';
}
$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.'" >'
. html_entity_decode($data).'</span><br><br></legend><b style="text-align:right; color:black;"><small>Posted by:- <a href="home.php?u='.$author.'">'.$author. '</a> '.$post_date.'</small></b>'
. '<br><p>'.$status_replies.'</p><br>';
$status_list= '<textarea id="reply_textarea_'.$updateid.'" class="status_reply_'.$updateid.' input-custom2" placeholder="comment\'s"></textarea>'
. '<button id="reply_btn_'.$updateid.'" attr="'.$updateid.'" type="b" class="btn btn-warning pull-right btn-sm reply_btn reply_'.$updateid.'">Reply</button></div>';
echo "$status_list";
} } }
var_dump values of $row1:
array(42) {
[0]=>
array(12) {
["update_body"]=>
string(66) "<p>wsdcv uyhnb pl., dc kl, fvc yhb ikjm </p>"
["author"]=>
string(12) "shan2batman1"
["time"]=>
string(19) "2015-10-29 11:04:28"
["title"]=>
string(3) "wtf"
["account_name"]=>
string(12) "shan2batman1"
["update_id"]=>
string(2) "46"
["comment_body"]=>
string(21) "qwerty 2 shan2batman1"
["os_id"]=>
string(3) "116"
["author_c"]=>
string(12) "shan2batman1"
["time_c"]=>
string(19) "2015-11-04 14:21:35"
["comment_id"]=>
string(1) "5"
["type_c"]=>
string(1) "b"
}
[1]=>
array(12) {
["update_body"]=>
string(66) "<p>wsdcv uyhnb pl., dc kl, fvc yhb ikjm </p>"
["author"]=>
string(12) "shan2batman1"
["time"]=>
string(19) "2015-10-29 11:04:28"
["title"]=>
string(3) "wtf"
["account_name"]=>
string(12) "shan2batman1"
["update_id"]=>
string(2) "46"
["comment_body"]=>
string(16) "hello boogeyman!"
["os_id"]=>
string(3) "116"
["author_c"]=>
string(6) "qwerty"
["time_c"]=>
string(19) "2015-11-04 13:20:32"
["comment_id"]=>
string(1) "3"
["type_c"]=>
string(1) "b"
}
