I.e. to display the "(0 comments, last comment never)" part.
I have the database and tables the exact same names, only thing that differs I have all the functions and a DB connect and selection function defined earlier on. Not that any of this matters because it's connecting and getting some data, just no the comment part.
http://betterphp.co.uk/video.html?vid=dUKT0Va4ZhQ
<div class="facts_bg">
<h1>FACTS</h1>
<hr />
<?php
$posts = get_posts();
foreach ($posts as $post){
?>
<h2><?php echo $post['title']; ?></h2>
<h4>By <?php echo $post['user']; ?> on <?php echo $post['date']; ?></h4>
<h4>(<?php echo $post['total_comments']; ?> comments, last comment <?php echo $post['last_comment']; ?>)</h4>
<hr />
<p><?php echo $post['preview']; ?></p>
<?php
}
?>
</div>
//fetches a summary of all the blog posts
function get_posts(){
connect();
selectDB();
$sql = "SELECT
`posts`.`post_id` AS `id`,
`posts`.`post_title` AS `title`,
LEFT(`posts`.`post_body`, 512) AS `preview`,
`posts`.`post_user` AS `user`,
DATE_FORMAT(`posts`.`post_date`, '%d/%m/%Y %H:%i:%s') AS `date`,
`comments`.`total_comments`,
DATE_FORMAT (`comments`.`last_comment`, '%d/%m/%Y %H:%i:%s') AS `last_comment`
FROM `posts`
LEFT JOIN (
SELECT
`post_id`,
COUNT(`comment_id`) AS `total_comments`,
MAX(`comment_date`) AS `last_comment`
FROM `comments`
GROUP BY `post_id`
) AS `comments`
ON `posts`.`post_id` = `comments`.`post_id`
ORDER BY `posts`.`post_date` DESC";
$posts = mysql_query($sql);
$rows = array();
while (($row = mysql_fetch_assoc($posts)) !== false){
$rows[] = array(
'id' => $row['id'],
'title' => $row['title'],
'preview' => $row['preview'],
'user' => $row['user'],
'date' => $row['date'],
'total_comments' => ($row['total_comments'] === null) ? 0 : $row['total_comments'],
'last_comment' => ($row['last_comment'] === null ) ? 'never' : $row['last_comment']
);
}
return $rows;
}
