我正在上一些在线课程,在其中一个练习中,我们要为一个博客创建两个表-博客文章和博客帖子-并通过外键连接它们,然后显示两者的所有内容。评论应该只链接到一个特定的文章,同时也允许多个评论。
我的尝试:
function list_articles() {
include('core/db/db_connection.php');
$sql = "SELECT blog.title, blog.content, blog.posted_by, blog.date, article_comments.comments, article_comments.comment_by
FROM blog LEFT OUTER JOIN article_comments
ON blog.content_id = article_comments.content_id
WHERE blog.content != ''
ORDER BY blog.content_id DESC";
$result = mysqli_query($dbCon, $sql);
while ($row = mysqli_fetch_array($result)) {
echo
"<h5 class='posted_by'>Posted by " . $posted_by = $row['posted_by'] . " on " . $row['date'] . "</h5>" .
"<h1 class='content_headers'>" . $title = $row['title'] . "</h1>" .
"<article>" . $content = $row['content'] . "</article>" .
"<div class='commented_by'>Posted by: " . $row['comment_by'] . "</div>" .
"<div class='comments'>Comments: " . $row['comments'] . "</div>";
}
}
我是这样在数据库中插入注释的:
function insert_comments($comment_by, $comments) {
include('core/db/db_connection.php');
$sql = "SELECT blog.content_id, article_comments.blog_id
FROM blog AS blog
INNER JOIN article_comments AS article_comments ON article_comments.blog_id > blog.content_id";
mysqli_query($dbCon, $sql);
}
在PHPMyAdmin外键工作正常,评论链接到一个特定的文章。我想把它放到网页上。当我在页面上插入一篇新文章时,它工作正常,但当我试图为该文章插入评论时,它将不显示它。
如果我将ON blog.content_id = article_comments.content_id
更改为ON blog.content_id = article_comments.blog_id
(blog_id是外键的字段名)-它将显示文章的所有评论-但它会为与之相关的每个评论复制该文章。这说得通吗?我已经尽我所能解释清楚了。如果你需要进一步的澄清,请告诉我。由于
ALTER TABLE article_comments ADD CONSTRAINT comment_blog_fk FOREIGN KEY (blog_id) REFERENCES wt.blog(content_id) ON DELETE NO ACTION ON UPDATE CASCADE;
EDIT: ON blog.content_id = article_comments.blog_id
的结果
Article title: LOREM IPSUM
Content: LOREM IPSUM DOLOR SIT AMET....
--------------------------------------
Name: DSK
Comment: Great article!
-- HERE IT DUPLICATES THE ARTICLE TO INSERT A NEW COMMENT --
Article title: LOREM IPSUM
Content: LOREM IPSUM DOLOR SIT AMET....
--------------------------------------
Name: DSK
Comment: Great article! - 2nd comment
可以看到,它为插入的每个评论复制文章。所以我最后写了两篇评论不同的重复文章。如果我有100条评论,这篇文章将被复制100次
我期望的行为:
Article title: LOREM IPSUM
Content: LOREM IPSUM DOLOR SIT AMET....
-------------------------------------- '' COMMENTS ''
Name: DSK
Comment: Great article!
--------------------------------------
Name: DSK
Comment: Great article! - 2nd comment
试试这个:
$posts = array();
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'password');
// for example all fields
$query = $pdo->query('
SELECT *
FROM blog AS blog
INNER JOIN article_comments AS article_comments ON article_comments.blog_id = blog.content_id
');
while ($row = $query->fetch()) {
$idContent = $row['content_id'];
if (!isset($posts[$idContent])) {
$posts[$idContent] = array(
'posted_by' => $row['posted_by'],
'title' => $row['title'],
'content' => $row['content'],
'comments' => array()
);
}
$posts[$idContent]['comments'][] = array(
'comment_by' => $row['comment_by'],
'comment' => $row['comment'],
);
}
foreach ($posts as $post) {
echo '
Post: ' . $row['title'] . ' . Posted by: ' . $row['posted_by'] .
'<br/>Content: ' . $row['content'] .
'<br/>Comments: ';
;
foreach ($post['comments'] as $comment) {
echo $comment['comment'] . '. Comment by: ' .$row['comment_by'] . '<br/>';
}
}