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

FaderMonkey · 08-04-2004, 02:53 PM

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?

Latch · 08-04-2004, 03:26 PM

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.

sailor · 08-06-2004, 05:51 AM

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.

insidious_machinae · 08-07-2004, 09:44 PM

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.

sailor · 08-09-2004, 04:56 AM

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

}

FaderMonkey · 08-10-2004, 08:23 AM

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

Thanks everybody for your help!

insidious_machinae · 08-10-2004, 11:39 PM

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

sailor · 08-11-2004, 05:20 AM

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

08-04-2004, 02:53 PM	#1 (permalink)
FaderMonkey Psycho Location: Orlando, FL	[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?

08-06-2004, 05:51 AM	#3 (permalink)
sailor beauty in the breakdown Location: Chapel Hill, NC	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. __________________ "Good people do not need laws to tell them to act responsibly, while bad people will find a way around the laws." --Plato

08-04-2004, 03:26 PM	#2 (permalink)
Latch In Your Dreams Location: City of Lights	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.

08-07-2004, 09:44 PM	#4 (permalink)
insidious_machinae Psycho	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.

08-10-2004, 08:23 AM	#6 (permalink)
FaderMonkey Psycho Location: Orlando, FL	Yeah, mysql_num_rows() works well. Didn't think it was going to be that easy. Thanks everybody for your help!

08-10-2004, 11:39 PM	#7 (permalink)
insidious_machinae Psycho	Sailor, personally I'd rather just not make any errors at all and avoid complexity