CODEWITHSUNDEEP
phpmysqlcodeigniter
Christina Huggins Ramey
Christina Huggins Ramey

Reputation: 603

Codeigniter: extra parameters being inserted into MySQL statement

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?

Upvotes: 1

Views: 52

Answers (3)

leogent
leogent

Reputation: 56

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.

Upvotes: 0

gen_Eric
gen_Eric

Reputation: 227270

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

Upvotes: 1

user20232359723568423357842364
user20232359723568423357842364

Reputation: 4634

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

Upvotes: 2

Related Questions