I am pulling out rows from my database which will also show the number of responses to the post. I am able to grab the rows but need to do a count on the posts from a join table which takes the post_id and comment_id - I am not sure on how to approach this or if it is even doable?
function build_forum_featured(){
global $dbc;
$q = "SELECT u.user_id, u.first_name, u.last_name, f.post_created, f.post_id, f.post_title, j.id
FROM users AS u
INNER JOIN user_post_join AS j ON u.user_id = j.user_id
INNER JOIN forum_post AS f ON f.post_id = j.post_id
WHERE featured_post = 2
ORDER BY post_created ASC
";
$r = mysqli_query ($dbc, $q); // Run the query.
// FETCH AND PRINT ALL THE RECORDS
while ($row = mysqli_fetch_array($r)) {
echo '
<div class="post">
<div class="col-group-2">
<a href="post_view.php?post_id='.$row["post_id"].'"><h3>'.$row["post_title"]. '</h3></a>
<p>By: <a href="user_view.php?id=' . $row["user_id"] . '">'.$row["first_name"] . ' ' .$row["last_name"]. '</a> on: ' .date('d-m-Y', strtotime($row["post_created"])). '</p>
</div>
<div class="col-group-2">
<div class="post_count">
<h3 class="answer">0</h3> // count goes here!
<p class="answer">Responses</p>
</div>
</div>
</div>
';
}
Essentially what I a trykgi to do is reference the join table and check to see the post_id and how many comments are associated to that post. so I have a table called comment_post_join
which takes the post_id and the comment_id and just adds rows.