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: stackoverflow.com/questions/21869603/… — Alauddin Ansari
– Alauddin Ansari, Commented Jun 24, 2015 at 14:31

user20232359723568423357842364 · Accepted Answer · 2015-06-24 14:37:31Z

2

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;       
    }

answered Jun 24, 2015 at 14:37

user20232359723568423357842364

4,6341 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christina Huggins Ramey Over a year ago

That worked! For some reason I assumed that the functions were relatively isolated from one another. Thanks so much for the solution and clarification.

gen_Eric · Accepted Answer · 2015-06-24 14:37:35Z

1

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;       
}

answered Jun 24, 2015 at 14:37

gen_Eric

228k42 gold badges304 silver badges343 bronze badges

Comments

leogent · Accepted Answer · 2015-06-24 14:43:40Z

0

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.

answered Jun 24, 2015 at 14:43

leogent

565 bronze badges

Collectives™ on Stack Overflow

Codeigniter: extra parameters being inserted into MySQL statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related