我有两个单独的表,第一个是“blog”,它显然包含已发布的blog。第二个表是“búu comments”,它包含了博客上发布的所有评论。每个博客都有一个唯一的ID,它与每个评论一起存储。
从现在起,我使用两个独立的查询来获取完整的博客文章和所有评论。有没有办法把这两个表放在一起,在一个查询中得到一篇完整的博客文章和所有评论?
我从未使用过MySQL的JOIN
或JOIN
函数,所以我不确定这是否可能。
下面是我的两个问题,如果有帮助的话:
$getBlog = $link->query("SELECT * FROM blogs WHERE blog_id = '" .$blogID. "'");
$getComments = $link->query("SELECT * FROM b_comments WHERE blog_id = '".$blogID."' ORDER BY date DESC")
编辑:这里有更多的代码可以帮助您更好地理解我要做的事情:
//get blog posts
$blogID = intval($_GET['id']);
$sql = $link->query("SELECT * FROM blogs WHERE blog_id = '" .$blogID. "'");
$row = mysqli_fetch_assoc($sql);
$blogTitle = $link->real_escape_string($row['blog_title']);
$pubDate = $link->real_escape_string($row['pub_date']);
$blogCat = $link->real_escape_string($row['category']);
$blogAuthor = $link->real_escape_string($row['author']);
$blogImage = $link->real_escape_string($row['image']);
$blogContent = $link->real_escape_string($row['content']);
//get comments
$getComments = $link->query("SELECT * FROM b_comments WHERE blog_id = '".$blogID."' ORDER BY date DESC");
if(!($getComments->num_rows > 0)) {
echo "<div id='commentArea' style='display: none;'>";
echo "</div>";
} else {
echo "<div id='commentArea'>";
while($comRow = mysqli_fetch_assoc($getComments)) {
$commentID = $link->real_escape_string($comRow['id']);
$comAuthor = $link->real_escape_string($comRow['user_name']);
$comDate = $link->real_escape_string($comRow['date']);
$comContent = $link->real_escape_string($comRow['content']);
$comDate = date("F jS, Y H:ia", strtotime($comDate));
利用这些变量,我将数据回送到一个HTML页面中。
最佳答案
在不知道表结构的情况下,您可以尝试以下操作:
SELECT *
FROM blogs
JOIN b_comments ON blogs.blog_id = b_comments.blog_id
如果要返回没有评论的博客,您可能需要使用
LEFT JOIN
。更新
//get blog posts
$blogID = intval($_GET['id']);
$blogTitle = null;
$pubDate = null
$blogCat = null;
$blogAuthor = null;
$blogImage = null;
$blogContent = null;
//get comments
$getComments = $link->query("SELECT a.blog_title, a.pub_date, a.category, a.author, a.image, a.content AS blog_content, b.id, b.user_name, b.date, b.content FROM blogs a JOIN b_comments b ON a.blog_id = b.blog_id WHERE a.blog_id = " . $blogID . " ORDER BY b.date DESC");
if(!($getComments->num_rows > 0)) {
echo "<div id='commentArea' style='display: none;'>";
echo "</div>";
} else {
while($comRow = mysqli_fetch_assoc($getComments)) {
if(empty($blogTitle)) $blogTitle = $link->real_escape_string($row['blog_title']);
if(empty($pubDate)) $pubDate = $link->real_escape_string($row['pub_date']);
if(empty($blogCat)) $blogCat = $link->real_escape_string($row['category']);
if(empty($blogAuthor)) $blogAuthor = $link->real_escape_string($row['author']);
if(empty($blogImage)) $blogImage = $link->real_escape_string($row['image']);
if(empty($blogContent)) $blogContent = $link->real_escape_string($row['blog_content']);
$commentID = $link->real_escape_string($comRow['id']);
$comAuthor = $link->real_escape_string($comRow['user_name']);
$comDate = $link->real_escape_string($comRow['date']);
$comContent = $link->real_escape_string($comRow['content']);
$comDate = date("F jS, Y H:ia", strtotime($comDate));
}
}
关于php - 在MySQL中联接“博客”表和“评论”表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/14513790/