counting specific post issues

[mattwal]

Morning all,

 

I'm trying to display the the number comment there for a specific article. e.g. there are 2 comments for the article "doing it my way".

 

table overview for posts:

post_id, author, category,title, teaser, post, date

 

table overview for comments:

comment_id, post_id, name, comment, date_entered

 

 

I am using this code:

<?php //Script 3.0 Display Entries
	//This  script displays blog entries in DESC order.

	//address error handling
	ini_set ('display_errors', 1);
	error_reporting (E_ALL & ~E_NOTICE);

		//Connect and select
		require_once ("./assets/mysql_connect.php");

		//Define the query
		$query2 = 'SELECT * FROM comments JOIN posts ON ( comments.post_id = posts.post_id);';
		$result = @mysql_query ($query2);

		$num = mysql_num_rows($result);

		$query = 'SELECT * FROM posts ORDER BY date DESC'; 
		if ($r = mysql_query ($query)) { //Run Query

			//Retrieve and print every record.
			while ($row = mysql_fetch_array ($r)) {
				print "
				<h2><span>{$row['title']}</span></h2>
				Posted by: {$row['author']}<br />
				Posted on: {$row['date']}<br />

				<p>{$row['teaser']}</p>
				<p>| <img src=\"./images/readmore.png\" /> 
				<a href=\"article.php?aid={$row['post_id']}\" title=\"Read The Entire Article\">More</a> | 
				<img src=\"./images/comment.png\" /> <a href=\"javascript:comment('comment_form.php?aid={$row['post_id']}');\" 
				title=\"Make A Comment On This Post\">Comment</a>($num)</p>
				";
			}

		} else { //query didnt run

			die ('<p>Could not retieve the data because: <b>' . mysql_error() . "</b>. 
			The query was $query.</p>");
		} // end of IF query.

		mysql_close();

		?>

 

More specifically:

$query2 = 'SELECT * FROM comments JOIN posts ON ( comments.post_id = posts.post_id);';
		$result = @mysql_query ($query2);

		$num = mysql_num_rows($result);

 

and using the $num to display the number of comments for the posts...

 

It works  BUT when 2 or more articles are displayed it is displaying the same number of comments forthe 1st returned record. I was wondering if there was anything i can do link the count to the post_id or check it by each record?

 

I apologize if i dont express myself clearly. Ive been up all night and very tired.

 

oh also using the code above does anyone have any idea how i could get the count to display zero if there are no comments for the specific post ?

 

any help at all would be much appreciated

 

[xtopolis]

This is by no means the best way, nor probably the right way to do things, but:

SELECT *,COUNT(comment_id) FROM comments RIGHT JOIN posts ON ( comments.post_id = posts.post_id)
GROUP BY posts.post_id

The reason I am using a right join is so that it doesn't leave out posts with no comments.  If you were to do this, you should do it differently, such as not using the * wildcard, only selecting the columns you need, etc.

 

Check it out in your mysql to see if it gives you the correct values first before implementing it.

[mattwal]

Thank you for that! It works perfectly for me.... all I had to do was add "AS num" to reference it in my script.

 

Im not sure how you came up with that code although I am just starting to tread water in php...

 

I also got this code as well although it didnt show all my posts im guessing because i did not have any comments for it.

 

'SELECT posts.*, COUNT(comment_id) AS num FROM posts JOIN comments ON ( comments.post_id = posts.post_id) GROUP BY (comments.post_id) ORDER BY posts.date DESC';

 

Thank you again!

[xtopolis]

posts.date is the problem.  You are using a reserved word for a column name.  you need to make it posts.`date` to quick fix it, a better solution would be to rename the column to a non reserved word.