+- 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: Active Record: Insert / update on exist (/showthread.php?tid=51514)
Pages:
1
2
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Samus[/eluser]
Basically i'm inserting thousands of rows of data at once from a .txt file.
Problem is sometimes data may already exist, but may have changed ever since the last insert, so i'm going to need to update that data with the newer values.
I need help in creating an insert/update batch method.
I'm aware this functionality isn't currently available in 2.1, but i've seen posts on where people have been able to hack/extend the core to achieve this.
Anyone able to guide me to an up to date example of how to do this please?
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Stefan Hueg[/eluser]
There are two approaches:
First:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
This is what you need. You'll have to generate an SQL statement for every update on your own
Second:
1. Build up a flat array of IDs you wish to update (lets say: $my_ids)
2. Query your database for existing keys
Code:
$result = $this->db->select('id')->from('table')->where_in($my_ids)->get();4. Use the remaining keys for insertion
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Samus[/eluser]
[quote author="Stefan Hueg" date="1336432856"]There are two approaches:
First:
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
This is what you need. You'll have to generate an SQL statement for every update on your own
Second:
1. Build up a flat array of IDs you wish to update (lets say: $my_ids)
2. Query your database for existing keys
Code:
$result = $this->db->select('id')->from('table')->where_in($my_ids)->get();4. Use the remaining keys for insertion[/quote]
Yeah that's actually similiar to what I was doing before.
It just isn't effecient at all checking then inserting/updating 30,000+ rows. PHP always times out.
I was thinking if there was a solution using something similiar to insert_batch() maybe with the 'insert on duplicate' clause
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Stefan Hueg[/eluser]
You may have a look here: http://dev.mysql.com/doc/refman/5.1/de/insert-delayed.html
But honestly: 30.000 rows isn't that much and should never produce a timeout.
Are you sure that you set proper keys and indexes on the rows you are querying?
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Samus[/eluser]
Well I wasn't too sure if the indexes affected inserting that much, but yeah I have a primary key on my ID column.
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Stefan Hueg[/eluser]
Look at DB_active_rec.php in your system/database folder.
-public function insert_batch($table = '', $set = NULL)
and system/database/mysqli_driver:
function _insert_batch($table, $keys, $values)
The way how insert_batch works is that it will stitch one single SQL statement together and fireing it onto the database.
You should do the same but use the insert ... on duplicate... statement instead.
Either you'll fire 30.000 queries which is, admittedly, slow
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Samus[/eluser]
[quote author="Stefan Hueg" date="1336435111"]Look at DB_active_rec.php in your system/database folder.
-public function insert_batch($table = '', $set = NULL)
and system/database/mysqli_driver:
function _insert_batch($table, $keys, $values)
The way how insert_batch works is that it will stitch one single SQL statement together and fireing it onto the database.
You should do the same but use the insert ... on duplicate... statement instead.
Either you'll fire 30.000 queries which is, admittedly, slow
[/quote]Indeed you're right there, that was my initial thought, but it involved 'hacking' the core and since all my CI apps share the same core, I could update someday and completely forget about that 'hack', which will probably end in me debugging it for the next 3 days.
Anyway thanks, if I don't get any more ideas, i'll just go ahead and edit that.
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Stefan Hueg[/eluser]
Don't edit, EXTEND
That's what OOP is all about You could make a MY_Model (which I always do) and put your custom query functions there.
It's a common problem but I have not seen a better solution yet.
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Samus[/eluser]
[quote author="Stefan Hueg" date="1336435660"]Don't edit, EXTEND
That's what OOP is all about You could make a MY_Model (which I always do) and put your custom query functions there.
It's a common problem but I have not seen a better solution yet.[/quote]
I guess i'll just have to copy the code from the system/db, edit and store in the model, huh
Active Record: Insert / update on exist - El Forum - 05-07-2012
[eluser]Stefan Hueg[/eluser]
I don't know if that works as I've never extended database classes, but here is a perfect solution. I just googled a bit:
http://dev.mysql.com/doc/refman/5.1/en/replace.html
With this you can just build your own replace_batch function and make us all happy