Codeigniter: extra parameters being inserted into MySQL statement

Question

I have a model with several functions, one of which is supposed to delete some records from several tables that match a specified tag number. However, when I output the MySQL statement to the error log, I see that it's adding some extra parameters, which appears to be taken from another function in the model. It's quite bizarre, and I can't for the life of me figure out what's going on.

Here's the function:

function deleteEquipment() {
    $tag = $this->uri->segment(3);
    $this->db->where('tag', $tag);
    $this->db->delete('equipment');
    error_log('mysql: '.$this->db->last_query());
    $this->db->where('tag', $tag);
    $this->db->delete('process_record');
    $this->db->where('tag', $tag);
    $this->db->delete('hold');
    $this->db->where('tag', $tag);
    $this->db->delete('dpo');
    error_log('mysql: '.$this->db->last_query());
    return;
}

Here is the error log:

WHERE (archived<>'yes' AND (on_lot<>'yes' OR photographs_uploaded<>'yes') AND sold<>'yes') OR (archived<>'yes' AND sold='yes' AND (final_inspection<>'yes' OR jdlink2_registered<>'yes'))
AND `tag` =  '40'
[24-Jun-2015 16:10:21 Europe/Berlin] mysql: DELETE FROM `process_record`
WHERE `tag` =  '40'
[24-Jun-2015 16:10:21 Europe/Berlin] mysql: DELETE FROM `hold`
WHERE `tag` =  '40'
[24-Jun-2015 16:10:21 Europe/Berlin] mysql: DELETE FROM `dpo`
WHERE `tag` =  '40'

So, the last three statements look fine, but the first has all of these extra parameters inserted into the statement. The only other place I see these parameters are in a couple of functions in the same model that relate to pagination, e.g.:

function getRowCount() {
        // to do: edit this
        $q = $this->db->get('equipment');
        $where = "(archived<>'yes' AND (on_lot<>'yes' OR photographs_uploaded<>'yes') AND sold<>'yes') OR (archived<>'yes' AND sold='yes' AND (final_inspection<>'yes' OR jdlink2_registered<>'yes'))"; 
        $this->db->where($where);
        $rowcount = $q->num_rows();
        return $rowcount;       
    }

I don't have a clue why the parameters from a completely separate function are showing up in the first MySQL statement. Note: There is another function in the same model where the same thing is happening: A few updates to three separate tables where the row matches a tag number. The equipment table query pulls in this weird stuff, but the other queries look fine.

Any idea why this might be happening?

You need to create different instances for each query. currently it is being combined in each other. For example: http://stackoverflow.com/questions/21869603/running-multiple-queries-in-model-in-codeigniter#21869647 — Alauddin Ansari, Jun 24 '15 at 14:31

score 2 · Accepted Answer · answered Jun 24 '15 at 14:37

Active Record is caching the WHERE in getRowCount because you are calling the methods out of order.

Your application calls deleteEquipment while the WHERE from getRowCount is still in the Active Record cache.

function getRowCount() {
        // move line below
        // $q = $this->db->get('equipment');
        $where = "(archived<>'yes' AND (on_lot<>'yes' OR photographs_uploaded<>'yes') AND sold<>'yes') OR (archived<>'yes' AND sold='yes' AND (final_inspection<>'yes' OR jdlink2_registered<>'yes'))"; 
        $this->db->where($where);

        // Active record will now clear the WHERE cache after executing get
        $q = $this->db->get('equipment');
        $rowcount = $q->num_rows();
        return $rowcount;       
    }

That worked! For some reason I assumed that the functions were relatively isolated from one another. Thanks so much for the solution and clarification. — Christina Huggins Ramey, Jun 24 '15 at 15:21

score 1 · Answer 2 · answered Jun 24 '15 at 14:37

When you call $this->db->get(), you run the query. So, in your getRowCount() function, you run SELECT * FROM equipment, then you set a where clause.

That where clause is set, but not used until the next query, which is in the deleteEquipment() function.

You need to do this:

function getRowCount() {
    $where = "(archived<>'yes' AND (on_lot<>'yes' OR photographs_uploaded<>'yes') AND sold<>'yes') OR (archived<>'yes' AND sold='yes' AND (final_inspection<>'yes' OR jdlink2_registered<>'yes'))"; 
    $this->db->where($where);

    $q = $this->db->get('equipment');
    $rowcount = $q->num_rows();
    return $rowcount;       
}

score 0 · Answer 3 · answered Jun 24 '15 at 14:43

In the function getRowCount(), $this->db->get must come last. It should be like this:

function getRowCount() {
        // to do: edit this            
        $where = "(archived<>'yes' AND (on_lot<>'yes' OR photographs_uploaded<>'yes') AND sold<>'yes') OR (archived<>'yes' AND sold='yes' AND (final_inspection<>'yes' OR jdlink2_registered<>'yes'))"; 
        $this->db->where($where);
        $q = $this->db->get('equipment');
        $rowcount = $q->num_rows();
        return $rowcount;       
    }

In your code, because $this->db->where() is not followed by $this->db->get() immediately, causing the condition still be kept for next query.

Codeigniter: extra parameters being inserted into MySQL statement

3 Answers3