mysql_fetch_assoc problem

[gifron]

Hello everyone, I have a problem with my Blog script.

 

This is code:

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 
				`posts_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();
[b]while (($row = mysql_fetch_assoc($posts)) !==false){   [/b]                                        //this is line where is error
	$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;

 

When in file where I want to show what I wrote to the database enter this code:

$posts = get_posts();

print_r($posts);

 

I got this error, Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\blog_system\core\inc\posts.inc.php on line 42

Everything is ok with my database, there is a table posts from it should read but i get this error.

Please help.

[premiso]

Change this line:

 

$posts =  mysql_query($sql);

 

To be:

 

$posts =  mysql_query($sql) or trigger_error('SQL Failed: ' . mysql_error());

 

And see what SQL error is being thrown.

[gifron]

Now i got this error:

 

Notice: SQL Failed: FUNCTION blog.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual in C:\wamp\www\blog_system\core\inc\posts.inc.php on line 39

 

What does it mean?

[AbraCadaver]

And after you fix that, you can eliminate all of that array building since the $row array will only contain the fields that you specified in your query:

 

$rows = array();

while ($row = mysql_fetch_assoc($posts)) {
   $rows[] = $row;
}
return $rows;

[premiso]

It seems that the blog.COUNT function does not exist. Looking at the query, either you didn't paste the whole thing, something is overwriting your query or something in your database has gone funky. If you can I would run a few simple count queries, like

 

SELECT COUNT(*) FROM comments;

 

And see if that works. If it does, well make sure that the $sql is what it should be:

 

$posts =  mysql_query($sql) or trigger_error('SQL Failed: ' . $sql . '<br>Error: ' . mysql_error());

 

[gifron]

SQL is ok, I got this: "Your SQL query has been executed successfully"

 

When i put that another code it write me an error:

Notice: SQL Failed: 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

Error: FUNCTION blog.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual in C:\wamp\www\blog_system\core\inc\posts.inc.php on line 39

 

[gifron]

Anyone with idea how to solve this