How to add On Duplicate Key Insert into Query Builder in CodeIgniter 3?

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};