During last phase of the development on another CodeIgniter based project, I did import/export functionality, and used Active Record’s insert/update batch functions.
I figured out, that CodeIgniter’s core with CI_VERSION=2.1.2 doesn’t allow you to calculate the number of affected rows on batch functions. That’s the function to get the affected rows doesn’t work when using the batch insert or update functions. More details posted in the issue https://github.com/EllisLab/CodeIgniter/issues/126, it was opened a year ago, though in the open state still.
I decided to fix that myself, patched the native CI’s Active Record Class. From now the batch functions could return the number of affected rows instead of only TRUE. If everything worked but no records were affected the function would return 0. When there's an error in the parameters passed it will return FALSE, so no confusion here.
Here are the methods I patched, so you could freely use them or just send that to CI’s devs to fix that issue in the newer versions of the framework:
/**
* Insert_Batch
*
* Compiles batch insert strings and runs the queries
* (affected rows fix by WebAurum (http://webaurum.blogspot.com),
* for more information see issue https://github.com/EllisLab/CodeIgniter/issues/126 )
*
* @param string the table to retrieve the results from
* @param array an associative array of insert values
* @return object
*/
public function insert_batch($table = '', $set = NULL)
{
if ( ! is_null($set))
{
$this->set_insert_batch($set);
}
if (count($this->ar_set) == 0)
{
if ($this->db_debug)
{
//No valid data array. Folds in cases where keys and values did not match up
return $this->display_error('db_must_use_set');
}
return FALSE;
}
if ($table == '')
{
if ( ! isset($this->ar_from[0]))
{
if ($this->db_debug)
{
return $this->display_error('db_must_set_table');
}
return FALSE;
}
$table = $this->ar_from[0];
}
$affected_rows = 0;
// Batch this baby
for ($i = 0, $total = count($this->ar_set); $i < $total; $i = $i + 100)
{
$sql = $this->_insert_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_keys, array_slice($this->ar_set, $i, 100));
//echo $sql;
$this->query($sql);
$affected_rows += $this->affected_rows();
}
$this->_reset_write();
return $affected_rows;
}
/**
* Update_Batch
*
* Compiles an update string and runs the query
* (affected rows fix by WebAurum (http://webaurum.blogspot.com),
* for more information see issue https://github.com/EllisLab/CodeIgniter/issues/126 )
*
* @param string the table to retrieve the results from
* @param array an associative array of update values
* @param string the where key
* @return object
*/
public function update_batch($table = '', $set = NULL, $index = NULL)
{
// Combine any cached components with the current statements
$this->_merge_cache();
if (is_null($index))
{
if ($this->db_debug)
{
return $this->display_error('db_must_use_index');
}
return FALSE;
}
if ( ! is_null($set))
{
$this->set_update_batch($set, $index);
}
if (count($this->ar_set) == 0)
{
if ($this->db_debug)
{
return $this->display_error('db_must_use_set');
}
return FALSE;
}
if ($table == '')
{
if ( ! isset($this->ar_from[0]))
{
if ($this->db_debug)
{
return $this->display_error('db_must_set_table');
}
return FALSE;
}
$table = $this->ar_from[0];
}
$affected_rows = 0;
// Batch this baby
for ($i = 0, $total = count($this->ar_set); $i < $total; $i = $i + 100)
{
$sql = $this->_update_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->ar_set, $i, 100), $this->_protect_identifiers($index), $this->ar_where);
$this->query($sql);
$affected_rows += $this->affected_rows();
}
$this->_reset_write();
return $affected_rows;
}
