having trouble with a JOIN

[tjhilder]

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.

 

 

[ryanlwh]

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";

[tjhilder]

[!--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.

[ryanlwh]

 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]

[tjhilder]

thanks alot for your help, had to change '$_GET[news_id]' to {$_GET['id']} but it worked.

 

also thanks for the article, this has helped better my understanding of joins