Results 1 to 4 of 4
May 27, 2001, 01:55 #1
- Join Date
- Jan 2001
- Lawrence, Kansas
- 0 Post(s)
- 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 addressCode:
SELECT ssnews.*, user_id, user_nick, user_email FROM ssnews, ssusers, AND news_user = user_id ORDER BY news_timestamp DESCCode:
SELECT ssnews.*, COUNT(sscomments.com_id) AS num_comments FROM ssnews LEFT JOIN sscomments ON com_itemid = ssnews.news_id GROUP BY com_itemid
If anyone can help me sort this out I'd be most grateful