How to "reset" CodeIgniter active record for consecutive queries?

Question

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?

This *should* be working correctly by default because you aren't caching the `WHERE`s, it is strange that you're having issues. Try `echo`ing `$this->db->last_query()` after each update and `exit`ing to make sure the queries are actually correct. — Wesley Murch, Jun 06 '11 at 01:12

score 20 · Answer 1 · edited Dec 21 '11 at 08:45

20

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.

edited Dec 21 '11 at 08:45

eeerahul

1,629
4
27
38

answered Dec 21 '11 at 08:04

Prasanna Shetye

201
2
3

score 12 · Answer 2 · answered Aug 02 '13 at 20:54

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();

Antony · Answer 3 · 2022-01-12T12:18:35.407

12

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

And 2022 update for version 4:

$this->db->resetQuery();

As found here: https://codeigniter.com/user_guide/database/query_builder.html#resetting-query-builder

edited Jan 12 '22 at 12:18

answered Nov 16 '16 at 22:06

Antony

3,875
30
32

This should be the correct answer. Worked perfectly. Thank you! – viMaL Nov 28 '19 at 05:34
Correct answer! Thanks – Ahsan Khan Oct 07 '20 at 08:20

score 10 · Answer 4 · answered Jun 05 '11 at 21:42

10

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

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

answered Jun 05 '11 at 21:42

Femi

64,273
8
118
148

3

I would really recommend not using any functions that start with _* on CI. They are meant to be private functions. And they may not be available in future versions. [Sources](http://www.tig12.net/downloads/apidocs/codeigniter/database/CI_DB_active_record.class.html#det_methods__reset_write) Except for the only 2 exceptions I make which are [these](http://stackoverflow.com/questions/734138/best-practices-for-processing-errors-from-database-in-codeigniter) – Katsuke Jun 05 '11 at 21:49
1

And you are right, it is to be avoided: depending on his specific issue it may be the only way to solve it, so it is there for him. Having access to the **private by convention** methods have been quite handy from time to time, and strictly speaking they COULD have been marked as private (at least in 5.x and higher) if you were NEVER supposed to touch them. So as will all things powerful, handle with care. – Femi Jun 05 '11 at 21:54
1

Agreed, maybe add a tiny warning to you answer in case anyone uses it and is unaware of this fact? (That's sort of why I made the comment in the first place ^^) – Katsuke Jun 05 '11 at 22:18
Didn't work. I tried both variations after the first update but only the first update will get committed, the second update calls gets ignored. Again, if I comment out the first update call, the second update runs just fine. Perplexed :O – zee Jun 05 '11 at 22:35
Quite odd: if you do `echo $this->db->last_query();` after the first and second calls to `update`, what gets printed out? – Femi Jun 06 '11 at 03:38

score 0 · Answer 5 · answered Apr 29 '13 at 20:24

0

try

$this->db->reconnect();

after your query.

Good day!

answered Apr 29 '13 at 20:24

mariofertc

383
2
7

2

This would reconnect the database, which would effectively reset the query but this is overkill for what the OP is trying to do. – brenjt Apr 29 '15 at 20:33

score 0 · Answer 6 · answered Jun 05 '11 at 21:44

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)

How to "reset" CodeIgniter active record for consecutive queries?

6 Answers6

Linked