Query Advice RESOLVED

[eluser]OES[/eluser]
Hi Peeps

Would like some advice on a query or a better way to do it.

I have a simple news table and second with comments ie.

Code:

Table news
news_id
news_title
news_body
news_date

Table news_comments
newscomment_id
newscomment_news_id
newscomment_title
newscomment_body

If I done a query to loop the latest 10 news items what would be the bast way to count how many comments are associated to them.

ie Comment Title (2 Comments)

Or would it be best to include in the news table a line for comments and increase the number when someone submits a comment.

Sorry but a newbie to this :-).

Hope you can advise on the best way.

Thanks in advanced

[eluser]webthink[/eluser]
two ways to do it:
You could grab a your list of news items with one query and then for each one do a query to count comments.
That's 11 round trips to the database.

In a single query you could do something like this

SELECT news.news_id, COUNT(news_comments.newscomment_id) FROM news, news_comments WHERE news.news_id = news_comments.newscomment_news_id GROUP BY news.news_id

[eluser]OES[/eluser]
Hi Webthink.

Thanks for the respone I managed to scratch my head for a while but got it in the end. I done this query.

Code:

SELECT
`ci_newsentries`.`newsentry_id`,
`ci_newsentries`.`newsentry_user_id`,
`ci_newsentries`.`newsentry_date`,
`ci_newsentries`.`newsentry_views`,
`ci_newsentries`.`newsentry_title`,
`ci_newsentries`.`newsentry_body`,
`ci_newsentries`.`newsentry_approved`,
Count(`ci_newscomments`.`newscomment_id`) AS `no_comments`
FROM
        ci_newsentries
        LEFT JOIN ci_newscomments ON ci_newsentries.newsentry_id = ci_newscomments.newscomment_newsentry_id
GROUP BY
`ci_newsentries`.`newsentry_id`

And works perfect. Now just to put it into CI. that should be a laugh.

Thanks

[eluser]OES[/eluser]
Ok I think I converted it to an Active Record Query but its obviously wrong.

Code:

$this->db->select("ci_newsentries.*, COUNT(ci_newscomments.newscomment_id) AS no_comments");
$this->db->from('ci_newsentries');
$this->db->join('ci_newscomments', 'ci_newsentries.newsentry_id = ci_newscomments.newscomment_newsentry_id', 'left');
$this->db->where('ci_newsentries.newsentry_approved', 1);
$this->db->group_by("ci_newsentries.newsentry_id");
$this->db->order_by("ci_newsentries.newsentry_id", "desc");
$this->db->limit(10);
$query = $this->db->get();
        
$news_tiems = $query->row_array();
        
print_r($news_items);

but just trying to print the array its obvioulsy not working.

Is the a way to echo the SQL statement to see whats going on ??

Hope you can help

[eluser]xwero[/eluser]
$this->db->last_query()

[eluser]OES[/eluser]
Thanks for that.

My query is perfect from what I can see and it works in MYSQL.

so would this be wrong

$news_tiems = $query->row_array(); ?

Get this then Im on a roll :-)

Thanks

[eluser]OES[/eluser]
Ok I was being thick

All I needed was.

$data['news'] = $query->result_array();

Thanks for the help guys.