Codeigniter SQL query

Question

Hi there I am trying to do a COUNT that has WHERE clauses in it, the code I am using is

function count_tasks($userId) {
 $this->db->count_all('tasks');
 $this->db->where('tasksAssignedTo', $userId);
 $query = $this->db->where('taskPriority_taskPriorityId !=', 6);

 return $query;

However Codeigniter only seems to be running the following query

SELECT COUNT(*) AS `numrows` FROM `tasks`

Why is not running my where clauses I have a feeling that it is to with me returning $query but I dont know for sure any help on getting the WHERE's working would be a huge help.

Thanks Sico87

Answer 1

Try this approach instead:

function count_tasks($userId) {

    $this->db->select('count(*) as task_count');
    $this->db->from('tasks');
    $this->db->where('tasksAssignedTo', $userId);
    $this->db->where('taskPriority_taskPriorityId !=', 6);

    $query = $this->db->get();

    foreach ($query->result() as $row) {
        echo $row->task_count;
    }

}

which should give you the following query (obviously $userId will be replaced with a real value):

SELECT count(*) as task_count FROM (`tasks`) WHERE `tasksAssignedTo` = $userId AND `taskPriority_taskPriorityId` != 6

We're using CodeIgniter's select() function to explicitly write the SELECT portion of the query. Then we can use "count(*)" which is a SQL function that basically counts all the records in a group (essentially what CodeIgniter's count_all() function does, but we're writing it manually). The "as task_count" portion of the select() function just gives us something to reference from the returned query.

Then we're simply setting the FROM and WHERE parts of our SQL query, respectively. The get() function simply runs the query and returns the result, and we've assigned the result to $query. We can then access the row count via "$row->task_count".

Hope that helps. Let me know if you have any questions.

Answer 2

function count_tasks($userId) {
        $this->db->count_all('tasks');
        $this->db->where('tasksAssignedTo', $userId);
        $query = $this->db->where('taskPriority_taskPriorityId !=', 6);

        return $query;

modify your query. The *count_all* function stops the SQL building of the class.

   function count_tasks($userId)
   {
            $this->db->where('tasksAssignedTo', $userId);
            $this->db->where('taskPriority_taskPriorityId !=', 6);
            $query = $this->db->count_all('tasks');

            return $query;
   }