#fetches a summary of all blog posts
function get_posts(){
$sql = "SELECT
`posts`.`id` AS `id`,
`posts`.`title` AS `title`,
LEFT(`posts`.`body`, 512) AS `preview`,
`posts`.`user` AS `user`,
DATE_FORMAT(`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
`id`,
COUNT(`id`) AS `total_comments`,
MAX(`date`) AS `last_comment`
FROM `comments`
GROUP BY `id`
) AS `comments`
ON `posts`.`id` = `comments`.`id`
ORDER BY `posts`.`date` DESC";
$posts = mysql_query($sql) or die(mysql_error());
$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']===null) ? 0 : $row['total_comments'],
'last_comment' => ($row['last_comment'] === null ? 'never' : $row['last_comment'])
);
}
return $rows;
}
# fetches a single post from the table
function get_post($pid){
$pid = (int)$pid;
$sql = "SELECT
`title` AS `title`,
`body` as `body`,
`user` as `user`,
`date` as `date`,
FROM `posts` WHERE `posts`.`id` = {$pid}";
$post = mysql_query($sql);
$post = mysql_fetch_assoc($post) or die(mysql_error());
$post['comments'] = get_comments($pid);
return $post;
}
blog_list.php:
<?php
include("core/init.inc.php");
?>
<html>
<body>
<div>
<?php
$posts = get_posts();
foreach($posts as $post){
?>
<h1><?php echo $post['title']; ?></h1>
<?php echo "<h3>By ".$post['user']." on ".$post['date']; ?>
<h4><?php echo $post['total_comments']; ?> comment(s); last one on <?php echo $post['last_comment']; ?></h4>
<hr />
<h5>Post:</h5>
<p><?php echo $post['preview']; ?></p>
<?php
}
?>
</div>
</body>
</html>
for my table, i dropped the "post_" prefix.mysql_error wrote:Unknown column 'id' in 'field list'
Please help! I can't find the problem!
