Jump to content


Photo

having trouble with a JOIN


  • Please log in to reply
4 replies to this topic

#1 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 20 November 2005 - 06:06 PM

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....rsonal/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.



#2 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 20 November 2005 - 09:14 PM

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

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#3 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 20 November 2005 - 09:52 PM

[!--quoteo(post=320519:date=Nov 20 2005, 09:14 PM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Nov 20 2005, 09:14 PM) View Post[/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";
[/quote]

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.

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 21 November 2005 - 01:48 AM

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]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 21 November 2005 - 03:23 AM

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 :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users