I am taking some online courses and in one of the exercises we are to create two tables for a blog - blog articles and blog posts - and connect them through a foreign key, then display all content from both. Comments should be linked to a specific article only, while also allowing 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'm inserting comments in 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 alright and the comments are linked to a specific article. I want to transpose this on a web page. When I insert a new article on the page it works alright, but when I try to insert a comment for that 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 field name for the foreign key) - it will display all the comments for an article - but it duplicates that article for each comment associated with it. Does that make any sense? I tried explaining it as best as I can.. Please let me know if you need further clarification. Thanks
By the way, this is the statement 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 I get with 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 every comment inserted. So I end up with two duplicate articles that hold different comments. If If I'll have 100 comments, the article will get replicated 100 times
The behavior I am expecting:
Article title: LOREM IPSUM
Content: LOREM IPSUM DOLOR SIT AMET....
-------------------------------------- \\ COMMENTS \\
Name: DSK
Comment: Great article!
--------------------------------------
Name: DSK
Comment: Great article! - 2nd comment
phpMyAdmin has a function for this, but you must be using the InnoDB table type. You must also have pmadb configured. Select the table that contains the foreign key (child). Click "Relation view". Under the foreign key constraints, click the "Choose column to display" drop down and make a choice.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table. Look at the following two tables:
Foreign key relationships specify how tables relate to each other and indicate relationships between tables. PhpStorm recognizes foreign key relationships in your database schema and uses them to construct JOIN clauses. You can see these relationships in the auto-completion list, data navigation, and diagrams.
SQL FOREIGN KEY Constraint. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the ...
Try this:
$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/>';
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With