Blog Tutorial (While loop problem)

[zman]

Hi there, I get an error when 'n run the blog_list.php page.

The page takes long to load then it displays the same error in a loop
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\blog_commenting\core\inc\posts.inc.php on line 43"

I have only done the tutorial until vid 04.

I added a zipped file with the code

Thanks

[Temor]

Please post your code here using the code tags.
I don't feel too comfortable downloading zip files from strangers.

Also, if you add

echo mysql_error();

under the SQL query that is failing it should tell you what's wrong.

[zman]

<?php

// checks if given id is in 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;
	}
	
}

// Fetches summary of all blog post
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;
}

// fetches single post from the table
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;
	
}

// adds a new blog entry
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())");
	
	
}
?>

[Temor]

add

echo mysql_error();

after running the query on line 40.

it should tell you why it's not working.

[zman]

Hi, I did try to add the echo mysql_error(); , but it gives me the same php error block.
I have attached one image (I hope you can view it) with all the error's combined on one image. (Two errors are labelled by number).
1.When I run the blog_list.php it gives me this error below.
Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\comment_blog\core\inc\posts.inc.php on line 43
Call Stack
# Time Memory Function Location
1 0.0005 674224 {main}( ) ..\blog_list.php:0
2 0.0645 719696 get_posts( ) ..\blog_list.php:14
3 0.0651 720704 mysql_fetch_assoc ( ) ..\posts.inc.php:43

Also it stops looping the block error when I place the return true one bracket towards the top just after the array (this enabled me to capure a screenshot without slowing down my browser)

2. I took the while loop and inserted it into myphpadmin. I got an error
ERROR: Unknown Punctuation String @ 41
STR: !==

The last thing I tested was the SELECT qeury from get_posts(). I get this error
#1054 - Unknown column '%d/%m/%Y %H:%i:%s' in 'field list'

Hope this helps. Thx for your time.

[Temor]

try swapping the backticks ( `) for semiquotes ( ' ) around your date formatting.

`%d/%m/%Y %H:%i:%s`

¨

'%d/%m/%Y %H:%i:%s'

[jacek]

All of the quotes ' that are around a table or columns name need to be changed to backticks `

[zman]

Hi, I have changed the all the single quotes to backticks and changed the bacticks near the date formating to normal single quotes. I have also combined the coding onto one page. When i load the page it takes a long time to load, then it gives the same error on the image I previously attached (numbered 1.) "mysql_fetch_assoc(expects parameter 1to be resource.... (the error is repeated)
I took the $sql and array code and pasted it into myphpadmin SQL and ran a check, it gives me the following error on the attached image.

Here is the code:

<?php
mysql_connect('localhost','root', '');
$connect=mysql_select_db('blog');

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



?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Blog</title>
</head>

<body>
	<?php
	
	$posts = get_posts();
	
	foreach ($posts as $post){
		?>
        <h2><?php echo $post['title'];?></h2>
        <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
	}
	
	?>


</body>
</html>

[jacek]

You don't want to enter the php bits into phpmyadmin, just the SQL, so this part

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

I can see one error

 MAX(`comment_date`) AS `last_comment`,

There should not be a , at the end of this line.

[zman]

Hi,

Thats one problem solved. It does'nt loop anymore and no errors. Now I have put data into my database, but it's not showing it? It only shows this
"By on
(0 comments, last comment never)".

Any idea?

Thanks

[zman]

Hi ive found the problem, It was the brackets near mysql_fetch_assoc.
Everything is working now.

Thanks for the help, I really appreciate it.

[jacek]

It was the brackets near mysql_fetch_assoc.

How did I miss that ?! Oh well, as long as you got it working