Ugh! This may be a hard one to chase down…
(Hopefully you guys can help without having to see all of my code?!)
So I finally converted my old static website full of Articles to one that is now database-driven.
Beneath the current Article, logged in Members can add comments.
The problem is that while I can add Comments, they are not all being displayed below a given Article. (Up until today, I would swear that all Comments for a given Article were being displayed, but now it just seems like it is Articles that I added today?!)
Here are some snippets of my code… (All in one file.)
// ******************************
// Attempt to Retrieve Article. *
// ******************************
if (isset($_GET['slug']) && $_GET['slug']) {
// Slug found in URL.
// ************************
// Find Article Record. *
// ************************
// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt)==1){
// Article was Found.
$articleExists = TRUE;
// Bind result-set to variables.
mysqli_stmt_bind_result($stmt, $articleID, $title, $description, $keywords,
$heading, $subHeading, $publishedOn, $author,
$body, $referenceListing, $endnoteListing);
// Fetch record.
mysqli_stmt_fetch($stmt);
// Close prepared statement.
mysqli_stmt_close($stmt); // ????
// @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
// **********************
// Build Comments Query *
// **********************
// Build query.
$q2 = 'SELECT m.first_name, c.created_on, c.body, c.status
FROM member AS m
INNER JOIN comment AS c
ON m.id = c.member_id
WHERE c.article_id=?';
// Prepare statement.
$stmt2 = mysqli_prepare($dbc, $q2);
// Bind variable to query.
mysqli_stmt_bind_param($stmt2, 'i', $articleID);
// Execute query.
mysqli_stmt_execute($stmt2);
// Store results.
mysqli_stmt_store_result($stmt2);
// Check # of Records Returned.
if (mysqli_stmt_num_rows($stmt2)>=1){
// Comment(s) Found.
$commentExists = TRUE;
// Bind result-set to variables.
mysqli_stmt_bind_result($stmt2, $firstName, $createdOn, $body, $status);
}else{
// Comments Not Found.
$commentExists = FALSE;
}//End of FIND COMMENT RECORD
}else{
// Article Not Found.
header("Location: " . BASE_URL . "articles/index.php");
}//End of FIND ARTICLE RECORD
}else{
// This will never fire!!
// Mod_Rewrite catches missing slug and re-routes to "articles/index.php"
}//End of ATTEMPT TO RETRIEVE ARTICLE
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
Then farther down in the <body> of my page is the PHP that should display the Comments we found above…
<!-- @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ -->
<!-- COMMENTS SECTION -->
<div id="boxComments">
<!-- MEMBER COMMENTS -->
<?php
// Fetch Comment record.
while (mysqli_stmt_fetch($stmt2)){
echo '<div class="comment">';
echo '<p class="commentAuthor">By <a href="'. BASE_URL . 'members/profile.php">' . $firstName . '</a></p>';
echo '<p class="commentDate">' . date('g:ia', strtotime($createdOn)) . ' on ' . date('M j, Y', strtotime($createdOn)) . '</p>';
echo '<p>' . $body . '</p>';
echo '</div>';
}
?>
</div><!-- End of COMMENTS SECTION -->
I think the problem is that I am not retrieving all of the Comments associated with the Article the User is currently reading. (Honestly, I still feel shaky on this whole record-set thing in the Prepared Statements?!)
Also, what should be happening is that I don’t want to show Comments unless they have been approved by me (i.e. status=‘Approved’). But one problem at a time?!
What is wrong with my code above?
And why does it seem like only Comments that I just added a little while ago are being shown, even though I can see 20+ Comments in phpMyAdmin from the last few weeks of development and testing?!
Thanks,
Debbie