SQL Help, get numbers of comments made by users

Databases
1

I have two tables

Table users

user_id
user_name

Table comments

comment_id
comment_text
user_id

I want to get all country and number of films made in the country.

Currently i am doing


$data = array();

$result = mysql_query('select * from users');

while($users = mysql_fetch_assoc($result)) {
    $user_id = $users['user_id'];
    $result2  = mysql_query('select count(*) as total from comments where user_id = $user_id");
    $comment = mysql_fetch_assoc($result2);
    $data[] = array('id' => $users['user_id'], 'username' => $users['user_name'], 'total_comments' => $comment['total']);
}

Can i get this done in one sql ?

2

actually, no

you want to get all users and number of comments made by each user

:smiley:

SELECT users.user_name
     , COUNT(*) AS total 
  FROM comments 
INNER
  JOIN users
    ON users.user_id = comments.user_id
GROUP  
    BY users.user_name
3

Thanks r937 for the reply. Actually i was coming up with country/film example, then rewrite my post to make it users/comment to make it more general :slight_smile: