blog tutorial

[nfalcon001]

While going throught the tutorial I started building the blog as you did. On part03 you tested the get_posts sql and got no error however when I did the same thing I got an error (#1054 - Unknown column 'blogcomments.total_comments' in 'field list').

Here is syntax I used (changed table names to blogcomments and blogposts)

[syntax=php]
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']
);
}
}
[/syntax]

Please help!
Thanks

[ExtremeGaming]

That error simply means the column you are trying to select from, doesn't exist. You need to create it first.

[nfalcon001]

Once I changed the table names to what the tutorial calls them the problem went away.

Thanks for the quick response to this issue. Although I don't understand why the change in names made it go away when I made sure the table names were spelled correctly... I guess is all magic...

[ExtremeGaming]

Because the tables in the database needs to match the tables you are selecting. This error means that you were selecting from a table/column that didn't exist, therefore it couldn't complete and gave an unknown column error.