Welcome Guest, Not a member yet? Register   Sign In
Performant Insert/Update ?
#1

[eluser]hykoh[/eluser]
Hi there,

what is the easiest way to create a performant insert or update in loops with the CI 2.0 DB class ?

for example:

Code:
<?php

for ($i = 1; $i <= 100; $i++) {
    $this->db->insert('table', array( 'id' => $i, 'content' => 'foo' ));
}

?&gt;

if my code looks like this, he always fires the query on demand .. means 100 connections to the db with 100 insert querys .. is there any easy way to get it in one query ???
#2

[eluser]tonanbarbarian[/eluser]
i have built something before that did what you want
cannot find the code however

what i did was used the insert_string method of the database class to generate all of the queries
then i took the value statement from all but the first query and appeneded them onto the first query
you can then run it as a single statement

code would be something like this
Code:
$insert_sql = $this->db->insert_string('table', array('id'=>1, 'content'=>'foo'));
for ($i=2; $i <= 100; $i++) {
  $new_sql = $this->db->insert_string('table', array('id'=>$i, 'content'=>'foo'));
  $insert_sql .= ','.substr($new_sql, strpos($new_sql, ' VALUES ')+8);
}
$this->db->simple_query($insert_sql);
#3

[eluser]WanWizard[/eluser]
CI 2.0 has new AR methods insert_batch() and update_batch(), which do inserts or updates 100 records at the time.
#4

[eluser]hykoh[/eluser]
thanks for the tip WanWizard, but it's still not included in the UserGuide
#5

[eluser]WanWizard[/eluser]
I always like to know the in's and out's of the stuff I work with.

It's been discussed as a missing feature some time ago, the changelog indicates it's been added on 2010-03-03. Why it's not documented? I don't know. They probably have their reasons...




Theme © iAndrew 2016 - Forum software by © MyBB