How to “reset” CodeIgniter active record for consecutive queries?

18
votes

I'm using CodeIgniter and have a case where two tables (projects and tasks) need to be updated with a value right after one another (active column needs to be set to "n"). The code I am using is:

function update($url, $id)
{
    $this->db->where('url', $url);
    $this->db->update('projects', array('active' => 'n'));
    $this->db->where('eventid', $id);
    $this->db->update('tasks', array('active' => 'n'));
}

With this code, the projects table gets updated but the tasks table does not. If I comment out $this->db->update('projects', array('active' => 'n')); then the tasks table gets updated.

I reckon this has something to do with caching but I have tried flush_cache before the tasks db->update call but that didn't have any effect.

Can someone explain how consecutive update queries can be executed using CodeIgniter?

6
phpcodeigniter
This should be working correctly by default because you aren't caching the WHEREs, it is strange that you're having issues. Try echoing $this->db->last_query() after each update and exiting to make sure the queries are actually correct. – Wesley Murch

6 Answers

19
votes

Use

$this->db->start_cache();

Before starting query building and

$this->db->stop_cache();

After ending query building. Also, use

$this->db->flush_cache();

After stop cache.

11
votes

This works:

$this->db->flush_cache();

If you don't perform a get() or similar CI does not always clear the cache. The final code looks like this:

$this->db->from('table');
$this->db->where('field', $field);
$count = $this->db->count_all_results();
$this->db->flush_cache();
10
votes

Try calling $this->db->reset(); after the first update call.

EDIT: meh, try $this->db->_reset_write(); to flush all traces of the query.

7
votes

For version 3 of Codeigniter the correct way is:

$this->db->reset_query()

As found here: http://www.codeigniter.com/userguide3/database/query_builder.html#resetting-query-builder

0
votes

In the second update call, do you need the url conditional? if so, after you call the first update that data is no longer available for the second one. You will need to set again:

function update($url, $id)
{
    $this->db->where('url', $url);
    $this->db->update('projects', array('active' => 'n'));
    $this->db->where('url', $url);
    $this->db->where('eventid', $id);
    $this->db->update('tasks', array('active' => 'n'));
}

// Alternatively
function update($url, $id)
{
    $where_bit = array(
        'url' => $url,
    );
    $this->db->update('projects', array('active' => 'n'), $where_bit);
    $where_bit['event_id'] = $id;
    $this->db->update('tasks', array('active' => 'n'), $where_bit);
}

CI active record update supports the where condition to be passed in as an array of key => value as the 3rd parameter (also as a string but id recommend using the array instead)

0
votes

try 

$this->db->reconnect();

after your query.

Good day!