count_all_results returns 1 every time in 3.0.6 - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6) +--- Forum: Issues (https://forum.codeigniter.com/forumdisplay.php?fid=19) +--- Thread: count_all_results returns 1 every time in 3.0.6 (/showthread.php?tid=68201) |
count_all_results returns 1 every time in 3.0.6 - crouchkl - 06-08-2017 I have the following code to get results from a mySQL database. Every time I run the code, the result is 1. If I run a get() on the code, 3 rows are returned. $this->db->select('Customer.obj_id as customerObjId, Customer.last_name as lastName, Customer.first_name as firstName, concat_ws(", ", Address.address1, Address.city, Address.state, Address.zip) as defaultAddress, Phone.phone as defaultPhone, Email.email as defaultEmail, sum(Rewards.points_earned) as pointsEarned, sum(Rewards.points_redeemed) as pointsRedeemed') ->from('Customer') ->join('Address', 'Address.customer_obj_id = Customer.obj_id and Address.default_address = 1', 'left outer') ->join('Rewards', 'Rewards.customer_obj_id = Customer.obj_id', 'left outer'); $this->db->join('Phone', 'Phone.customer_obj_id = Customer.obj_id and Phone.default_phone = 1', 'left outer'); $this->db->join('Email', 'Email.customer_obj_id = Customer.obj_id and Email.default_email = 1', 'left outer'); $this->db->like('upper(Customer.last_name)', 'A'); $this->db->group_by('Customer.obj_id'); return $this->db->count_all_results();; RE: count_all_results returns 1 every time in 3.0.6 - skunkbad - 06-08-2017 You might debug by checking: $this->db->last_query() to see what your SQL is. All of the selected columns, as well as the grouping are doing nothing when you use count_all_results. Try dropping them from your query, and see if that makes a difference. You should upgrade CodeIgniter to the latest version. You will eventually, so if you think there is a problem with 3.0.6, now's a good time. RE: count_all_results returns 1 every time in 3.0.6 - php_rocs - 06-08-2017 @crouchkl, You could also turn on the profiler on the page which would give you useful information about the query. http://www.codeigniter.com/user_guide/general/profiling.html Another suggestion...you could just create a view which would decrease the amount of code that you need for this query selection. RE: count_all_results returns 1 every time in 3.0.6 - crouchkl - 06-08-2017 I echoed the last_query and got the following: SELECT COUNT(*) AS `numrows` FROM `Customer` LEFT OUTER JOIN `Address` ON `Address`.`customer_obj_id` = `Customer`.`obj_id` and `Address`.`default_address` = 1 LEFT OUTER JOIN `Rewards` ON `Rewards`.`customer_obj_id` = `Customer`.`obj_id` LEFT OUTER JOIN `Phone` ON `Phone`.`customer_obj_id` = `Customer`.`obj_id` and `Phone`.`default_phone` = 1 LEFT OUTER JOIN `Email` ON `Email`.`customer_obj_id` = `Customer`.`obj_id` and `Email`.`default_email` = 1 WHERE upper(Customer.last_name) LIKE '%A%' ESCAPE '!' GROUP BY `Customer`.`obj_id` I then ran this against the DB and got 5 rows, which is currently the correct answer. count_all_results still returns the value 1. RE: count_all_results returns 1 every time in 3.0.6 - InsiteFX - 06-09-2017 You could also update to the newest version of CodeIgniter. RE: count_all_results returns 1 every time in 3.0.6 - crouchkl - 06-09-2017 Updated the version and now it appears to be working. Thank you for all of the help |