• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
count_all_results returns 1 every time in 3.0.6

#1
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();;
Reply

#2
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.
Reply

#3
@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.
Reply

#4
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.
Reply

#5
You could also update to the newest version of CodeIgniter.
What did you Try? What did you Get? What did you Expect?

Joined the CodeIgniter Community in 2009.          ( Skype: insitfx )
Reply

#6
Updated the version and now it appears to be working. Thank you for all of the help
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
3 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.