I am receiving this mysql error and i don't know why. I am following a tutorial and i have copied the syntax from the video word for word. Can anybody spot whats wrong?
Here is the error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN ( SELECT post_id, COUNT(comment_id) AS `total_co' at line 11
$query = "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";
Your help is much appreciated. Thank you.
Remove the comma , at line 10:
FROM `posts`
LEFT JOIN
The correct syntax is:
FROM
<table a>
LEFT JOIN
<table b>
ON <join condition>
Unrelated to the problem:
For a (small) improvement on efficiency, change the COUNT(comment_id) to:
COUNT(*) AS total_comments,
You could also add a (post_id, comment_date) index on table comments
In the main SELECT list, if you prefer to have 0s than NULLs for posts without comments, change comments.total_comments, to:
COALESCE(comments.total_comments, 0) AS total_comments,