Query Advice RESOLVED - Printable Version

Query Advice RESOLVED - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query Advice RESOLVED (/showthread.php?tid=7636)

Query Advice RESOLVED - El Forum - 04-17-2008

[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

Query Advice RESOLVED - El Forum - 04-17-2008

[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

Query Advice RESOLVED - El Forum - 04-17-2008

[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

Query Advice RESOLVED - El Forum - 04-17-2008

[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

Query Advice RESOLVED - El Forum - 04-17-2008

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

Query Advice RESOLVED - El Forum - 04-17-2008

[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

Query Advice RESOLVED - El Forum - 04-17-2008

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

All I needed was.

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

Thanks for the help guys.