Tilted Forum Project Discussion Community - [MySQL] number of weblog comments

- Tilted Technology (https://thetfp.com/tfp/tilted-technology/)

- - [MySQL] number of weblog comments (https://thetfp.com/tfp/tilted-technology/64853-mysql-number-weblog-comments.html)

[MySQL] number of weblog comments

So, I've created a weblog with PHP and MySQL. I've set it up so that people can comment on each weblog entry. Now, like most weblogs, I want the comment link under each weblog entry to show how many comments have been posted so far. Any ideas on how to best do that? As I've set it up now, whenever I post a blog entry my site creates a new table in MySQL for the comments for that entry. Is there a way of reading the number of rows in that table? I was also thinking of having a INT column in the table and setting it to AUTO_INCREMENT, but then how to I read the value of the last entry in that column?

I'd create two tables. One would be called "blog_entries" or whatever, and each row would contain a blog entry (title, date, contents). It would have a unique primary id as well (likely an 'int' with auto_increment on).

I'd then create another table "blog_comments", it'd have two int fields: blog_number, the blog number it's commenting on [the int autoincrement from blog_entries]; and comment_number, the number of the comment for that particular entry. Those two fields combined would create a unique primary key for every entry. The blog_comments table would also have fields like date, title, comment....

You could do a COUNT(tablename) to count the number of rows in a table.

For your last question, about getting the last number, in an auto_increment sequence when you have a newly created row (I think you mean that), check out here:

MySQL Manual | 21.2.12.3 How to Get the Unique ID for the Last Inserted Row

edit:

If you just want to find out what the latest auto_increment number is, just select MAX(auto_inc_field) from TABLE; That'll give you the highest (latest) entry number.

Id also create two tables. In the posts table, make sure you give each post a unique ID number. Then, for the comments table, have a column that you put the corresponding post ID in. Then you can sort and select by post ID (SELECT * FROM comments WHERE post_id = 'x') and go from there. PHP has a function, mysql_num_rows() (at least I think thats what its called) that will return the number of rows returned by a query that would be useful for getting the number of comments that correspond to each post.

lmao, it's so easy you'll want to strangle yourself

given your comments table for blog ID number 15

PHP Code:


		
			
$query = "SELECT * FROM blog_comments_table WHERE blog_ID = '15'";

$result = mysql_query($query);

$numberOfRows = mysql_num_rows($result);

Hope I did the php tags right.

Quote:

Originally posted by nwlinkvxd
lmao, it's so easy you'll want to strangle yourself

given your comments table for blog ID number 15

PHP Code:

$query = "SELECT * FROM blog_comments_table WHERE blog_ID = '15'"; $result = mysql_query($query); $numberOfRows = mysql_num_rows($result);

Hope I did the php tags right.

Looks about right. You might want to do a little error catching:

PHP Code:


		
			
$result = mysql_query($query);

if(!$result){

echo "Error: ".mysql_error();

}

else {

$numRows = mysql_num_rows($result);

}

Yeah, mysql_num_rows() works well. Didn't think it was going to be that easy.

Thanks everybody for your help!

Sailor, personally I'd rather just not make any errors at all and avoid complexity ;)

Quote:

Originally posted by nwlinkvxd
Sailor, personally I'd rather just not make any errors at all and avoid complexity ;)

Haha, sounds like a plan ;)