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/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: How to get correct Number of Comments (with 4 connected tables: Art./Cat./Users/Comm.) (/showthread.php?tid=20714) |
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) Everything works but there is one (I hope) problem: Quote:If an Article has 0 Comments, it shows 1 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): ?> 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. |