Here is syntax I used (changed table names to blogcomments and blogposts)
function get_posts(){
$sql = "SELECT
`blogposts`.`post_id` AS `id`,
`blogposts`.`post_title` AS `title`,
LEFT (`blogposts`.`post_body`, 512) AS `preview`,
`blogposts`.`post_user` AS `user`,
DATE_FORMAT(`blogposts`.`post_date`, '%d/%m/%Y %H:%i:%s') AS date,
`blogcomments`.`total_comments`,
DATE_FORMAT(`blogcomments`.`last_comment`, '%d/%m/%Y %H:%i:%s') AS `last_comment`
FROM `blogposts`
LEFT JOIN (
SELECT
`post_id`,
COUNT(`comment_id`) AS `total_comments`,
MAX(`comment_date`) AS `last_comment`
FROM blogcomments
GROUP BY post_id
) AS comments
ON blogposts.post_id = blogcomments.post_id
ORDER BY blogposts.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) ? 'No comments have been recorded': $row['total_comments']
);
}
}
Please help!Thanks
