Jump to content

having trouble with a JOIN


tjhilder

Recommended Posts

Hi,

 

I have been creating a news script using MySQL and it's coming along great so far, but a bit stuck on a join.

 

basicly I have 2 tables, called 'News' and 'Comments' both have a field called news_id which is so I can match the comments to the news item, I want to be able to display how many comments a news item has by matching how many items in table: comments, have a news_id of XX, I'm using the pagination script (as easy as 123 Next) etc but right now it only shows the news items with have comments, using the following mysql query below: I am able to show items but with comments, my question is what do I change on this code to make it display all news items (comments or not) but also showing how many comments it has via a join.

 

$query = "SELECT news.*, COUNT(comment_id) AS count FROM news, comments WHERE news.news_id=comments.news_id GROUP BY news_id DESC LIMIT $limitvalue, $limit";

 

so if anyone could help me with this it would make my weekend (got the flu, so not a good weekend :s:p)

 

thanks in advance.

 

you can check out what I mean at my testing demo site (http://tjhilder.f2s.com/personal/news.php) I also have this problem with the home page where "latest news" area is at the bottom, using the same code as mentioned above.

 

 

Link to comment
Share on other sites

[!--quoteo(post=320519:date=Nov 20 2005, 09:14 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 20 2005, 09:14 PM) 320519[/snapback][/div][div class=\'quotemain\'][!--quotec--]

use left/right joins

$query = "SELECT news.*, COUNT(comment_id) AS count FROM news LEFT JOIN comments ON news.news_id=comments.news_id GROUP BY news_id DESC LIMIT $limitvalue, $limit";

 

ah thanks alot, it works really well! although I am a little confused how 'LEFT JOIN' and 'ON' works.

 

I just have one other thing I need help with, again with comment counting, but this is when someone clicks on 'view comments' it shows the news item, then it's comments, but I don't know how I would get it to show how many comments it has, it's a little different as the query goes like this:

 

$query = "SELECT * FROM news WHERE news_id={$_GET['id']}";

 

I figured maybe it would be something like:

 

$query = "SELECT news.*, COUNT(comment_id) AS count FROM news LEFT JOIN comments ON news.news_id=comments.news_id GROUP BY news_id WHERE news_id={$_GET['id']}";

 

but it didn't work, maybe some help with this? :)

 

thanks in advance.

Link to comment
Share on other sites

 SELECT news.*, COUNT(comment_id) AS count FROM news LEFT JOIN comments ON news.news_id=comments.news_id WHERE news.news_id='$_GET[news_id]' GROUP BY news.news_id 

 

for a better understanding of joins, read this article. [a href=\"http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html\" target=\"_blank\"]http://www.wellho.net/mouth/158_MySQL-LEFT...OUTER-JOIN.html[/a]

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.