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.