SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: number of rows

  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    number of rows

    hello

    ive got two rows

    post( post_id, user_id, post)
    comment (comment_id, post_id, comment)

    I've got a loop setup to echo each post in my php.

    How do I echo the number of comments that each post have?
    I've already setup a join which connects the comment to the post.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    ive got two rows
    those look like tables, not rows

    Quote Originally Posted by Jaynesh View Post
    I've already setup a join which connects the comment to the post.
    can i see it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, yes sorry I meant tables.

    Here is the join

    Code:
    LEFT JOIN comments
    ON
    dbPosts.post_id = comments.comment_post_id
    here is the whole query

    Code:
    "SELECT dbUsers.*, dbPosts.*, dbKarma.*, comments.*
    
    FROM dbPosts 
    LEFT JOIN dbUsers
    ON dbPosts.username_id = dbUsers.id
    
    
    LEFT JOIN dbKarma
    ON (dbKarma.karma_post_id = dbPosts.post_id)
    AND
    (dbKarma.karma_user_id = $user)
    
    LEFT JOIN dbPosts_share
    ON (dbPosts.post_id = dbPosts_share.share_post_id)
    OR (dbPosts.username_id = dbPosts_share.share_user_id)
    
    LEFT JOIN dbFriends
    ON
    dbPosts.username_id = dbFriends.my_id
    OR 
    dbPosts.username_id = dbFriends.friend_id
    OR
    dbPosts_share.share_user_id = dbFriends.friend_id
    OR 
    dbPosts_share.share_user_id = dbFriends.my_id
    
    LEFT JOIN comments
    ON
    dbPosts.post_id = comments.comment_post_id
    
    WHERE
    (dbFriends.my_id = $user)
    OR
    (dbFriends.friend_id = $user)
    AND
    (dbFriends.status = 1)
    OR
    dbPosts.username_id = $user
    
    
    GROUP BY
        dbUsers.username
      , dbUsers.id
      , dbPosts.post_id
      , dbPosts.username_id
      
      ";

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it appears that you have a GROUP BY that is not really required

    plus, you're still using the dreaded, evil "select star"

    and then there is the complexity of all those ORs that drive me nuts, both in the join but especially in the WHERE clause, where the ORs are mixed with ANDs...

    sigh

    in any case, you should remove the GROUP BY, and replace the join to the comments table with this --
    Code:
    LEFT OUTER
      JOIN ( SELECT comment_post_id
                  , COUNT(*) AS num_comments
               FROM comments
             GROUP
                 BY comment_post_id ) AS sq
        ON sq.comment_post_id = dbPosts.post_id
    and then replace the dreaded comments.* in the SELECT clause with sq.num_comments
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!!! works perfectly.

    working on those dreaded ORs and *'s


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
  •