Blog, SQL error #1109 - Unknown table 'posts' in field list

[twiggy]

Hi Folks,

I'm following your blog tutorial series currently on video 3 where you say to check the code in the SQL query from video 2. Well I did that and get this error:

SQL query: Documentation

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` ;

MySQL said: Documentation
#1109 - Unknown table 'posts' in field list

Here is my sql:

[syntax=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_comments`
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
"[/syntax]

MY DB is set-up just like in the video DB called blog with two tables one called comments and one called posts

[jacek]

The only thing that can be is either you don’t have the table called "posts" or you have somehow selected the wrong database.

Make sure you didn't typo the table name what creating it and also make sure the database name you pass to mysql_select_db is correct.

[twiggy]

Hi Jacek, double checked everything I thought from what it was saying that it couldn't find a posts table but there is one there and the db select is selecting the correct db called blog so can't see whats going on

[twiggy]

Maybe this could help find the fault because I'm at a lost end can't get it to pass the SQL check

[jacek]

Okay this is a strange one !

But lets take it one step at a time. The field list is the part after the SELECT and before the FROM so lets just look at this

[syntax=sql]`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_comments`[/syntax]

I really don't know if it makes any difference but the space here

[syntax=sql]`comments`. `last_comment`[/syntax]
should not be here.

Also you have a ; at the end of this line

[syntax=sql]DATE_FORMAT(`posts`.`post_date`, '%d/%m/%Y %H:%i:%s') AS `date`;[/syntax]

If those two things don't fix it you can try running the SQL selecting each column one at a time to work out which one is causing the problem.