php - mysql 查询返回空结果

Question

伙计们，我正在尝试在以下查询中将评论表连接到帖子表。

$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();

它返回空结果，不知道我哪里出错了？？？任何帮助，将不胜感激。这是我的数据库架构:

根据arsh的要求，我在这里发布整个代码:

  <?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";




}         }        }

$row1 的 var_dump 值:

array(42) {
  [0]=>
  array(12) {
    ["update_body"]=>
    string(66) "<p>wsdcv uyhnb pl., dc kl, fvc yhb ikjm &nbsp;</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 &nbsp;</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"
  }

Answer 1

首先添加您要打印结果的 php 代码以查看结果，到目前为止，请按照以下步骤操作。

您的查询中缺少 where 子句，并且在连接表时 on c.os_id=:statusid 也是错误的

使用我在c.os_id=c.comment_id上的位置，但您必须在c.comment_id的位置插入正确的列名称，因为我可以'不知道你是怎么命名的。 :

$sql3="select  u.update_body,u.author,u.time,u.title,"
                . "c.comment_body, c.os_id,c.author,c.time "
                . "from updates u left join comment_update c "
                . "on c.os_id=c.comment_id WHERE u.account_name = :session or u.account_name=:friend and (u.type = 'a' or 'c') order by u.time asc,c.time desc";
         $stmth=$conn->prepare($sql3);
         $stmth->bindValue(":session",$_SESSION['uname']);
         $stmth->bindValue(":friend",$friend);

         $stmth->execute();

正如op发布的错误一样，他得到的错误指出这些值之一是空的，检查这些值是否需要有一个值并且不为空，并删除$stmth->bindValue(":statusid", $updateid); 因为您不再使用它，它会给您一个 token 和绑定(bind)参数的错误:

 $stmth->bindValue(":session",$_SESSION['uname']);
 $stmth->bindValue(":friend",$friend);