SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL help needed: Problem with a LEFT JOIN

    I'm currently working on a PHP/mySQL news system, which I intend to release in the near future as 'ssNews' (to complement my links script ssLinks, while also adding a multiple user permissions system to the ssLinks script). Everything is going pretty well, but I'm having problems with the news comment system.

    Visitors to the site can post comments on news articles, which will then be displayed on a seperate page for that news story. The problem I am having is fetching the "number of comments" for each news article from the database.

    Here's a simplified run down of the two database tables in question:
    Code:
    sscomments - the comments added by site visitors
    ==========
    com_id  int(11)
    com_itemid  int(11)	ID of news story the comment is attached to
    com_nick  varchar(20)	Name of comment author
    com_email  varchar(50)	E-Mail address of comment author
    com_text  text		Comment text
    com_time  int(11)	date/time comment was posted
    
    ssnews - the actual news stories
    ======
    news_id  int(11)
    news_user  int(11)	ID of user that posted the comment
    news_timestamp  int(11)	date/time article was created
    news_headline  varchar(255) news headline
    news_text  text		Text of news story
    
    ssusers - the users who can post news on the site
    =======
    user_id  int(11)
    user_nick  varchar(20)	Name of user
    user_email varchar(255)	user's e-mail address
    Here's the basic SQL statement I use to get all of the information I need:
    Code:
    SELECT ssnews.*, user_id, user_nick, user_email
    FROM ssnews, ssusers,
    AND news_user = user_id
    ORDER BY news_timestamp DESC
    So far, so simple. The problem is that I need to fetch the number of comments listed for that news story as well. Here's what I've tried:
    Code:
    SELECT ssnews.*, COUNT(sscomments.com_id) AS num_comments
    FROM ssnews LEFT JOIN sscomments
    ON com_itemid = ssnews.news_id
    GROUP BY com_itemid
    The first problem with this is it doesn't get the data from the user table as well - I'm not sure how to combine a standard join with a left join in the same query. The second problem is that for some reason it doesn't return every news story in the database. Instead it returns all news stories that have at least one comment attached to them and the first news story (news_id = 1) with num_comments displayed as 0. It should be returning ALL of the news stories with num_comments displayed as 0 on any stories with no comments yet.

    If anyone can help me sort this out I'd be most grateful

    Cheers,

    Skunk

  2. #2
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I experimented with this and it seems that your group by clause is wrong.

    try this SQL:
    Code:
    SELECT ssnews.*, count(sscomments.com_id) AS num_comments
    FROM ssnews left join sscomments
    on sscomments.com_itemid = ssnews.news_id
    group by ssnews.news_id
    You want to group by the news items, in the news table, not the comments table.

    As well, here is the MYSQL output from that query:
    Code:
    mysql> SELECT ssnews.*, count(sscomments.com_id) AS num_comments
        -> FROM ssnews left join sscomments
        -> on sscomments.com_itemid = ssnews.news_id
        -> group by ssnews.news_id;
    +---------+-----------+----------------+-------------------+----------------+--------------+
    | news_id | news_user | news_timestamp | new_headline      | news_text      | num_comments |
    +---------+-----------+----------------+-------------------+----------------+--------------+
    |       1 |         1 |       20010527 | Test Number One   | This is a test |        0 |
    |       2 |         1 |       20010527 | Test Number Two   | This is a test |        2 |
    |       3 |         1 |       20010527 | Test Number Three | This is a test |        1 |
    +---------+-----------+----------------+-------------------+----------------+--------------+
    3 rows in set (0.00 sec)
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  3. #3
    SitePoint Columnist Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks And congrats on spookily spotting my cross forum post (I put this up on a forum on http://forums.gameplay.com and got a reply from Alarion as well )

  4. #4
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That wasn't me

    that was someone named Atomsk
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m


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
  •