blog-tutorial problem with total_comments

[Dominic28]

Hey,
this is my first post here so i want to thank you for your nice tutorials first! They are great.

But i got a problem in your Blog-Tutorial. In Blog_List.php i cant see the total_comments. everytime its 0.
Here is my code:

echo $post['total_comments'];

and the get_posts function:

	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_commments'] === null) ? 0 : $row['total_comments'],
				'last_comment'		=> ($row['last_commment'] === null) ? 'never' : $row['last_comment']
			);
		}
		
		return $rows;
	}

and with error_reporting(E_ALL); it says:
Notice: Undefined index: total_commments
Notice: Undefined index: last_comment

hope you can help me

[Temor]

Good practice is to post all your code, not just one line.

Also, try adding

echo mysql_error();

under your query, but before the return line.
It should give you a detailed error message.

[jacek]

Hmm, can you post the full code ?

And if you try running the SQL in phpmyadmin do you get the correct result ?

Also, which lined to the undefined index messages refer to ?

thank you for your nice tutorials first! They are great.

[Dominic28]

Hey,
the mysql_error thing doesnt give me another error-message.

here is the full code (the part in the <div>):

<?php
				$posts = get_posts();
				
				foreach($posts as $post) {
					?>
					<h2><a href="blog_read.php?pid=<?php echo $post['id']; ?>"><?php echo $post['title']; ?></a></h2>
					<h4>geschrieben von <?php echo $post['user']; ?> am <?php echo $post['date']; ?></h4>
					<h4>(<?php echo $post['total_comments']; echo noun(1,' Kommentar','e'); ?>)</h4>
					
					<hr />
					
					<p><?php echo nl2br($post['preview']); ?></p>
					<?php
				}
			?>

SQL works fine in phpmyadmin...

the undefined index line are:

'total_comments'	=> ($row['total_commments'] === null) ? 0 : $row['total_comments'],
'last_comment'		=> ($row['last_commment'] === null) ? 'never' : $row['last_comment']

[jacek]

Ah, the variables that you compared to null on those two lines both have too many Ms

very hard to spot !

[Dominic28]

argh thats hard
well tank you