Help counting? Please?

brianb

Hello,

Here's the deal.

I have one table which stores my "people".

Then I have another table which stores the "people's" comments.

I want to COUNT the number of comments each person I have in my database has made. Then list them like so:

Person 1 = 47 comments
Person 2 = 15 comments

etc etc...

How would I write this as a MySQL statement?

Any help, and coding examples are appreciated!

Mordecai

I'd say make a column in the 'people' table that stores it. It'd take a long while to go through the entire database searching for them.

Daveyz1983

There are much better ways doing this like using a proper sql statement, but in the mean time perhaps u can do this.

//get all the names
$query = mysql_query ("select names from people");

//Loop through the names
foreach (mysql_fetch_array($query) as $temp) {
$query2 = mysql_query("select * from comments where name='" . $temp['name'] . "'");

//count the comments
$no_comments = mysql_num_rows($query2);

//print
echo $temp['name'] . ": $no_comments";
}
// Will print
// Mr Lim = 100
// Mrs Wan = 29
// so on.
// Please remember this snip does not include error check, its recommended that u include checks like Unable to connect to database, no results or something within the loop.

There r much better solutions out there, but still, hope this helps.

Daveyz1983

Forgot to add an alternative.
An alternative is to add a "number of comments" field inside the "people" table, so whenever there is a new update, just call a mysql statement "update people set no_comment = no_comment+1 where people = 'somename'"

brianb

Thanks guys!