+- 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: Bulk inserts/updates to db. Possible with CI? (/showthread.php?tid=35846)
Bulk inserts/updates to db. Possible with CI? - El Forum - 11-12-2010
[eluser]nuwanda[/eluser]
I'm trying to cut down on my db writes. I have several methods where I'm looping through arrays and inserting the new data one record at a time.
In raw mysql you can do this to insert multiple records in one query:
Code:
INSERT
INTO
table
(column_a, column_b, column_c)
VALUES
(1, 2, 3),
(4, 5, 6),
(7, 8, 9)
;Now, to accomplish this in CI, I could construct my own query string (like above) by looping, but that seems cumbersome.
I tried using multiple AR sets:
Code:
start loop{
$this->db->set($new_record_data);
}
$this->db->insert($table);But that only inserts the last set.
I also tried passing a multidimensional associative array to the insert function but that threw a mysql error. Obviously the insert can only handle associative arrays one at a time.
Then I considered transactions. If I start a trans, fire my queries, then end the trans, does that bulk write my data or does it just loop through them one at a time, which is what I'm trying to avoid.
Anyone got any ideas about this?
Bulk inserts/updates to db. Possible with CI? - El Forum - 11-12-2010
[eluser]WanWizard[/eluser]
CI 2.0 includes active record methods for batch inserts and updates.
If you can't or won't upgrade, you can always have a look at the code on bitbucket, to see if you can resume something.
Bulk inserts/updates to db. Possible with CI? - El Forum - 11-12-2010
[eluser]nuwanda[/eluser]
Excellent news about CI2. But I won't be upgrading until it's officially released.
Anyone got any thoughts on my transactions question? I'm not quite sure how I'd test to see if that resulted in one insert query or not.
Bulk inserts/updates to db. Possible with CI? - El Forum - 11-12-2010
[eluser]WanWizard[/eluser]
Transations don't change the queries you produce. Transaction commands are normal SQL statements as well. If you generate inserts in a loop, they will stay separate inserts.
That's why I suggested to look at the CI2 code. It should be compatible with the 1.7.2 active record library.