+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Affected Rows won't work when using batch insert/update (/showthread.php?tid=40524)
Affected Rows won't work when using batch insert/update - El Forum - 04-11-2011
[eluser]axelitus[/eluser]
Hi, as the post title says the function to get the affected rows doesn't work when using the batch insert or update functions.
I'm using CodeIgniter 2.0.2 Reactor
Let's say we have the following array:
Code:
$data = array(
array(
'username' => 'axelitus',
'name' => 'Axel'
),
array(
'username' => 'test_user',
'name' => 'Test'
)
)And now we try to insert it with the insert_batch() function from the DB class:
Code:
$this->db->insert_batch ('users', $data );The function works as expected inserting the data (if the primary key for one record already exists this functions returns an error as expected). But when I try to get the affected rows count all I get is a zero although the records were inserted correctly.
Code:
$affected_rows = $this->db->affected_rows ();
var_dump($affected_rows);Output:
Code:
int(0)This doesn't seem right at all and it also happens with the update_batch() function.
If anyone knows what is happening... or if this is a bug/error to post it in the bug tracker...
Affected Rows won't work when using batch insert/update - El Forum - 04-11-2011
[eluser]WanWizard[/eluser]
That's odd.
affected_rows() just calls the selected drivers' PHP function. As insert_batch() inserts the data with 100 rows at the same time, the figure could be incorrect (as you'll only see the last insert operation), but you see something else than zero.
Are you using transactions? If so, after a commit, the counter is reset to zero.
Affected Rows won't work when using batch insert/update - El Forum - 04-11-2011
[eluser]axelitus[/eluser]
You are right. I had some code that filtered what rows needed to be updated and what records needed to be updated. So after I had inserted all records (512 in total) the code determined that the records needed to be updated, but as you state it is done 100 at a time, so I changed the first records, but as I called the $this->db->affected_rows() function the last records needed not be updated.
I deleted all records and tried again my code, so now it says that there were 12 records affected (as the first 500 where done in batch). So if using $this->db->insert_batch() or $this->db->update_batch() we can't rely on having the accurate amount of records affected, just the one's that were affected in the last batch.
I'll post this as a feature request then.
Thanks a lot for your help pointing me in the right direction!
BTW I'm not using transactions...
Affected Rows won't work when using batch insert/update - El Forum - 04-11-2011
[eluser]WanWizard[/eluser]
It will be difficult to solve this using affected_rows(). Maybe it's better if the method returned the number instead of TRUE.
As for transactions, if an insert or update is embedded in a transaction, the data is gone after a commit. Hence the question (was possible since you got zero returned).
Affected Rows won't work when using batch insert/update - El Forum - 04-11-2011
[eluser]axelitus[/eluser]
I've already posted a new enhancement in bitbucket: https://bitbucket.org/ellislab/codeigniter-reactor/issue/215/affected-rows-for-insert-update-batch
Thanks!
Affected Rows won't work when using batch insert/update - El Forum - 10-19-2012
[eluser]Unknown[/eluser]
[quote author="axelitus" date="1302560564"]I've already posted a new enhancement in bitbucket: ...
Thanks![/quote]
That link is not working anymore.
Here is the patch for CI's Active Record Class http://webaurum.blogspot.com/2012/10/codeigniter-affected-rows-fix-on-db.html