BetterPHP

i'm having problem with the last comment in the blog_list page because it's showing 'never' even though there's already comments in that blog entry, wht do you think is the problem ? here are the codes

posts.inc

<?php

function valid_pid($pid){
	$pid = (int)$pid;
	
	$total = mysql_query("SELECT COUNT(post_id) FROM posts WHERE post_id = {$pid}")
	or die(mysql_error());
	$total = mysql_result($total , 0);
	
	if($total != 1){
		return false;
	}else{
		return true;
	}
}



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


function get_post($pid){
	$pid = (int)$pid;
	
	$sql = "SELECT
				post_title AS 'title',
				post_body AS 'body',
				post_user AS 'user',
				post_date AS 'date'
			FROM  posts
			WHERE post_id = {$pid}";
			
	$post = mysql_query($sql);
	
	$post = mysql_fetch_assoc($post);
	
	$post['comments'] = get_comments($pid);
	
	return $post;
	
}



function add_post($name , $title , $body){
	$name	= mysql_real_escape_string(htmlentities($name));
	$title	= mysql_real_escape_string(htmlentities($title));
	$body	= mysql_real_escape_string(nl2br(htmlentities($body)));
	
	mysql_query("INSERT INTO posts (post_user , post_title , post_body , post_date) VALUES ('{$name}' , '{$title}' , '{$body}' , NOW())")
	or die(mysql_error());


}
?>

blog_list

<?php
						
						$posts = get_posts();
						
						foreach($posts as $post){
						?>
						
						<h2><a href="blog_read.php?pid=<?php echo $post['id']; ?>"><h2><?php echo $post['title']; ?></h2></a>
						<h4>By <?php echo $post['user']; ?> on <?php echo $post['date'];?></h4>
						<h4>(<?php echo $post['total_comments']; ?> comments), last comment <?php echo $post['last_comment']; ?></h4>
						
						<hr>					
					
					
						<p><?php echo $post['preview']; ?></p>
					<?php
					}
					?>	
					

comments.inc

<?php

function get_comments($pid){ 
	$pid = (int)$pid;
	
	$sql =	"SELECT
			comment_body AS 'body',
			comment_user AS 'user',
			DATE_FORMAT(comment_date, '%d/%m/%Y %H:%i:%s') AS 'date'
		 FROM comments
		 WHERE post_id = {$pid}";
		 
	$comments = mysql_query($sql);
	
	$return = array();
	while (($row = mysql_fetch_assoc($comments)) != false){
		$return[] = $row;
	}

	return $return;

}

function add_comment($pid, $user , $body){
	if(valid_pid($pid) == false){
		return false;
	}

	$pid	= (int)$pid;
	$user	= mysql_real_escape_string(htmlentities($user));
	$body	= mysql_real_escape_string(nl2br(htmlentities($body)));
	
	mysql_query("INSERT INTO comments (post_id, comment_user , comment_body , comment_date) VALUES ('{$pid}' , '{$user}' , '{$body}' , NOW())");

	return true;
}




?>

Does it show the right number of comments ?

yah it does ..

kunowbi wrote:yah it does ..

hmm, okay...

Try running this part of the SQL in phpmyadmin and see if you get the expected result

                                SELECT
                                        post_id,
                                        COUNT('comment_id') AS 'total_comments',
                                        MAX('comment_date') AS 'last_comment'
                                FROM comments
                                GROUP BY post_id

it's showing 'comment_date' in the last_comment column, is it really like that ? it's not showing any dates or time

OH ! Okay I am being blind.

You need to use backticks ` around your column names not quotes '