Codeigniter SQL query


Question

I am trying to do a COUNT that has WHERE clauses in it with the code below:

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 are the where clauses not executed? I guess it has something to do with returning $query but I dont know for sure.

1
0
11/4/2017 1:36:19 AM

Accepted Answer

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;
   }
1
10/26/2009 1:31:20 AM

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.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon