PHP Blog Query

[swibit]

I've been following the blog with commenting tutorial but when it comes to the get posts query, I get 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 '$sql= "SELECT 
				'posts'.'post_id' AS id,
				'posts'.'post_head' AS head,
			' at line 1: 

My query code is:

	$sql = "SELECT 
				'posts'.'post_id' AS 'id',
				'posts'.'post_head' AS 'head',
				LEFT('posts'.'post_body',512) AS 'blogsummary',
				'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";

I can't see what I've done wrong within my code

[ExtremeGaming]

Change all the apostrophes around your column names to backticks `

[swibit]

So the code should now be like this?

	function get_posts() {
	$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_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";

	$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) ? `never` : $row[`last_comment`]
	);
	}

	return $rows;

[ExtremeGaming]

I believe your query should be like this. I have never needed to do such a query before so I don't know.

	function get_posts() {
	$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_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";