This hack in CodeIgniter 3 does the following:
If a primary or unique key in the database is the same as one of the insert values then it updates if not then it inserts.
Add this function into mysqli_driver.php in CI\system\database\drivers\mysqli.
/**
* ON DUPLICATE UPDATE statement
*
* Generates a platform-specific on duplicate key update string from the supplied data
*
* @author Jeric T <[email protected]> based off (Chris Miller <[email protected]>)
* @since 3.0.0
* @access public
* @param string the table name
* @param array the update/insert data
* @return string
*/
function _duplicate_insert($table, $values)
{
$updatestr = array();
$keystr = array();
$valstr = array();
foreach($values as $key => $val)
{
$updatestr[] = $key." = ".$val;
$keystr[] = $key;
$valstr[] = $val;
}
$sql = "INSERT INTO ".$this->_escape_str($table)." (".implode(', ',$keystr).") ";
$sql .= "VALUES (".implode(', ',$valstr).") ";
$sql .= "ON DUPLICATE KEY UPDATE ".implode(', ',$updatestr);
return $sql;
}
// --------------------------------------------------------------------
Then add this function into CI\system\database\DB_query_builder.php
/**
* On Duplicate Key Update
*
* Compiles an on duplicate key update string and runs the query
*
* @author Jeric T <[email protected]> based off (Chris Miller <[email protected]>)
* @since 3.0.0
* @access public
* @param string the table to retrieve the results from
* @param array an associative array of update value
* @return object
*/
function on_duplicate($table = '', $set = NULL )
{
if ( ! is_null($set))
{
$this->set($set);
}
if (count($this->qb_set) == 0)
{
if ($this->db_debug)
{
return $this->display_error('db_must_use_set');
}
return FALSE;
}
if ($table == '')
{
if ( ! isset($this->qb_from[0]))
{
if ($this->db_debug)
{
return $this->display_error('db_must_set_table');
}
return FALSE;
}
$table = $this->qb_from[0];
}
$sql = $this->_duplicate_insert($this->protect_identifiers($table), $this->qb_set );
$this->_reset_write();
return $this->query($sql);
}
// --------------------------------------------------------------------
Example usage:
$data = array(
'id' => 10,
'title' => $title,
'name' => $name,
'date' => $date
);
$this->db->on_duplicate('mytable', $data);
// INSERT INTO mytable (id, title, name, date)
// VALUES ({$id}, {$title}, {$name}, {$date})
// ON DUPLICATE KEY UPDATE id={$id}, title={$title}, name={$name}, date={$date};