CodeIgniter Forums
How to get correct Number of Comments (with 4 connected tables: Art./Cat./Users/Comm.) - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: How to get correct Number of Comments (with 4 connected tables: Art./Cat./Users/Comm.) (/thread-20714.html)



How to get correct Number of Comments (with 4 connected tables: Art./Cat./Users/Comm.) - El Forum - 07-19-2009

[eluser]sigork[/eluser]
4 standard connected tables:

1) Articles (Articles.categories; Articles.userid; Articles.articleid)
2) Categories (Categories.categoriid)
3) Users (Users.userid)
4) Comments (Comments.com_articleid)

Front page should show

1) Articles - info
2) Categories - info
3) Users - info
4) Comments - comments per article

My code is:

Code:
$this->db->limit(2); // 2 articles per page (pagination test)
$this->db->orderby('articleid', 'desc');

$this->db->select('*');
$this->db->select('Categories.categoryid', 'Categories.category_name');
$this->db->select('Users.userid', 'Users.username');
$this->db->select('com_articleid, COUNT(*) AS `comments_count`');

$this->db->group_by('articleid');

$this->db->from('Articles');

$this->db->join('Categories', 'Articles.categories = Categories.categoryid', 'left');
$this->db->join('Users', 'Articles.userid = Users.userid', 'left');
$this->db->join('Comments', 'articleid = com_articleid', 'left');

$data['query'] = $this->db->get();

Everything works but there is one (I hope) problem:
Quote:If an Article has 0 Comments, it shows 1 Comment (!)
If an Article has 1 Comments, it shows 1 Comment
...
If an Article has 9 Comments, it shows 9 Comment

For example,

- Articles.articleid= 7
- Comments.com_articleid has no 7 (No comments for this Article)
- Article shows it has 1 Comment.

View:

Code:
<?php foreach($query->result() as $row): ?>
...
<?=anchor ... $row->comments_count ... ;?>
...
<?php endforeach; ?>

Is there any obvious problem/error in my code, or how to debug this code?

Thanks!


How to get correct Number of Comments (with 4 connected tables: Art./Cat./Users/Comm.) - El Forum - 07-24-2009

[eluser]quibstar[/eluser]
I'm having pretty much the same issues,but instead of categories I have tags...I'm new to CI so be kind.

The issue is getting the comment count. I actually made this work in procedural PHP, but don't know how to port it over to code igniter. I nested two query inside of the initial query to get comments and tags:

Initial query:
Code:
$q = "SELECT user_id,blog, blog, blog, date_format(blog.date,'%Y %d %M ') AS date, first_name, last_name,time_stamp FROM blog INNER JOIN users USING ( user_id ) ORDER BY time_stamp DESC, time_stamp DESC LIMIT $start, $display";

Here is a snippet of getting the comments:
Code:
$q1 = "SELECT COUNT(blog) FROM blog_comment WHERE blog =" . $row['blog'] .  " AND submit ='approved'  ";

and here is a snippet of getting the tags:
Code:
$q2 = "SELECT * FROM blogtagjoin inner join tags USING(tag_id) WHERE blog=" . $row['blog'] .  "  ";

what needs to be done in CI is to run the queries withing the first query. I don't know enough about CI yet to figure this out...can you use a helper? or maybe a custom function?

Any help appreciated.


How to get correct Number of Comments (with 4 connected tables: Art./Cat./Users/Comm.) - El Forum - 07-26-2009

[eluser]sigork[/eluser]
[quote author="sigork" date="1248032105"]Is there any obvious problem/error in my code, or how to debug this code?

Thanks![/quote]

I think I found the problem:

Code:
$this->db->select('com_articleid, COUNT(*) AS `comments_count`');

should be replaced with:

Code:
$this->db->select('com_articleid, COUNT(com_articleid) AS `comments_count`');

But I don't understand why that is important.