SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    India
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Help, get numbers of comments made by users

    I have two tables

    Table users

    Code:
    user_id
    user_name
    Table comments

    Code:
    comment_id
    comment_text
    user_id

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

    Currently i am doing

    Code:
    $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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by HostOnNet View Post
    I want to get all country and number of films made in the country.
    actually, no

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

    Code:
    SELECT users.user_name
         , COUNT(*) AS total 
      FROM comments 
    INNER
      JOIN users
        ON users.user_id = comments.user_id
    GROUP  
        BY users.user_name
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    India
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    actually, no

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

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •