count_all_results returns 1 every time in 3.0.6 |
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();;
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.
@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/ge...iling.html Another suggestion...you could just create a view which would decrease the amount of code that you need for this query selection.
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.
You could also update to the newest version of CodeIgniter.
What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
Updated the version and now it appears to be working. Thank you for all of the help
|
Welcome Guest, Not a member yet? Register Sign In |