datetime error for SQL statement in blog tutorial

Post here if you need help with SQL.
Post Reply
guitardude211
Posts: 12
Joined: Tue Dec 06, 2011 6:55 pm

datetime error for SQL statement in blog tutorial

Post by guitardude211 »

Hello,

My name is Gene and I have been following the blog tutorials on YouTube in BetterPHP. However when I write the query statement for the function "get_posts", there is an error in my phpmyadmin that says: "#1054 - Unknown column 'd-m-Y H:i:s' in 'field list' " Please help..here is the sql statement listed from the tutorial. Thanks!! :
"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"
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: datetime error for SQL statement in blog tutorial

Post by jacek »

Your date format (%d-%m-%Y %H:%i:%s) should have ' quotes around it not backticks ` since backticks indicate a table or column name
Image
guitardude211
Posts: 12
Joined: Tue Dec 06, 2011 6:55 pm

Re: datetime error for SQL statement in blog tutorial

Post by guitardude211 »

Here is the entire code for the "posts.inc.php" I have only been following along from you're blog tutorial on YouTube to the point where you inserted your query statement in phpmyadmin and for you it worked but for me it came up with the error:

"#1064 - 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 '"SELECT `posts`.`post_id` AS `id`, `posts`.`post_title` AS `title`, ' at line 1"
<?php

//checks if the given post id is in the table
function valid_pid($pid) {
	$pid = (int)$pid;
	
	$total = mysql_query("SELECT COUNT(`post_id`) FROM `posts` WHERE `post_id` = {$pid}");
	$total = mysql_result($total, 0);
	
	if ($total != 1) {
		return false;
	}else{
		return true;
	}
}

//gets a summary of all blog posts
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();
}

//gets a single post from the table
function get_post($pid) {
	
}

//adds a new blog entry
function add_post($name, $title, $body) {
	
}

?>
guitardude211
Posts: 12
Joined: Tue Dec 06, 2011 6:55 pm

Re: datetime error for SQL statement in blog tutorial

Post by guitardude211 »

Problem solved! Thanks for your time with such a small issue! You're awesome!!
Post Reply