在PHP中显示和链接外键关键字

我参加了一些在线课程，在其中一项练习中，我们将为博客创建两个表格 - 博客文章和博客文章 - 并通过外键连接它们，然后显示来自两者的所有内容。 评论应该仅与特定文章相关联，同时也允许多个评论。

我的尝试：

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;

编辑 ：我得到的结果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/>';
            }

        }

链接地址: http://www.djcxy.com/p/87459.html

上一篇: Displaying and linking Foreign Key content in PHP

下一篇: programming Cortana