内部联接不适用于 PDO 和获取 assoc - Inner join doesn't work with PDO and fetch assoc

Inner join doesn't work with PDO and fetch assoc

对于我的新闻 en 帖子评论系统我无法从 2 个表(tbl_news 和 tbl_comments(获取查询结果。在我用mysqli制作的另一个程序中，内部连接工作了，但现在这次使用PDO我无法让它工作。

这是我的代码：

$newsQuery = $db->prepare("SELECT * 
                           FROM tbl_news 
                           INNER JOIN tbl_comments ON tbl_news.news_id = tbl_comments.news_id
                           ORDER BY news_id DESC 
                           LIMIT 5");
$newsQuery -> execute();
while($newsFetch = $newsQuery->fetch(PDO::FETCH_OBJ)) {
echo "<div class='news-post'><h3 class='post-title'>" . $newsFetch->title . "</h3>
        <p style='float:left'>By: <span class='post-author'>" . $newsFetch->author . "</span></p>
        <p style='float:right'>Date: <span class='post-date' style='font-style:italic;'>" . $newsFetch->date . "</span></p>
        <br><p>" . $newsFetch->text . "</p></div>";
        if(isset($_SESSION['user']) && ($newsFetch->comments == '1')) {
            echo "Comments(";
            echo "<div id='commentClick'>Click <a href='#' id='openForm'>here</a> to post a comment</div>";
            echo "<form class='navbar-form' id='commentForm'><input style='margin-right:5px' type='text' size='80%' name='commentText' placeholder='Type your comment here'><input type='submit' class='btn btn-primary btn-xs' value='Send'></form>";
        }elseif(!isset($_SESSION['user']) && ($newsFetch->comments == '1')) {
            echo "Click here to view comments. If you want to post comments please login first";
        }else{
            echo "Comments are disabled for this news item";
        }
}

在数据库中，我有以下值：tbl_news有news_id，tbl_comments有comment_id、news_id和user_id。

提前感谢！

您需要对数据库表名使用别名，例如：

SELECT n.*, c.* FROM tbl_news n 
INNER JOIN tbl_comments c ON n.news_id = c.news_id 
ORDER BY n.news_id DESC LIMIT 5

您的内部联接查询应如下所示

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

我的意思是您需要指定 tablename.columnname 而不是使用 all (*(

编辑

  1052 Column 'news_id' in order clause is ambiguous'

当您在语句中联接 2 个或更多表，并且这些表中存在具有相同名称的列，并且在引用语句中的列时未在列名前面加上表名时，会发生此错误。