Geek Answers Handbook

Displaying and linking foreign key contents in PHP

I take several online courses, and in one of the exercises, we need to create two tables for the blog - blog articles and blog posts - and connect them through a foreign key, and then display all the content from both. Comments should only be associated with a specific article, as well as allow multiple comments.

My attempt:

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

And this is how I insert comments into the database:

 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); }

In PHPMyAdmin, the foreign key works well, and the comments are related to a specific article. I want to transfer this to a web page. When I insert a new article on the page, it works fine, but when I try to insert a comment for this article, it will not display it.

If I change ON blog.content_id = article_comments.content_id to ON blog.content_id = article_comments.blog_id (blog_id is the name of the field for the foreign key), it will display all comments for the article, but it duplicates this article for each comment associated with it. Does this make sense? I tried to explain it as best as possible. Please let me know if you need clarification. Thanks

By the way, this is the operator that I used to create the foreign key:

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 : The result is obtained using 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

As you can see, it duplicates the article for each comment added. So I get two duplicate articles containing different comments. If I have 100 comments, the article will replicate 100 times

The behavior that I expect:

 Article title: LOREM IPSUM Content: LOREM IPSUM DOLOR SIT AMET.... -------------------------------------- \\ COMMENTS \\ Name: DSK Comment: Great article! -------------------------------------- Name: DSK Comment: Great article! - 2nd comment
+7
sql php mysql view
source share
1 answer

Try the following:

  $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/>'; } }
+4
source share

More articles:


All Articles