-
-
Save nateperry/9533083 to your computer and use it in GitHub Desktop.
A Codigniter mysql driver that adds the ability to have "INSERT ... ON DUPLICATE UPDATE" statements. This is a forked version which has been changed to appear more inline with the built in Codeigniter functions.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php if (! defined('BASEPATH')) exit('No direct script access allowed'); | |
class MY_DB_mysql_driver extends CI_DB_mysql_driver { | |
final public function __construct($params) { | |
parent::__construct($params); | |
} | |
/** | |
* Insert_On_Duplicate_Update | |
* | |
* Compiles insert strings and runs the queries | |
* MODIFIED to do a MySQL 'ON DUPLICATE KEY UPDATE' | |
* | |
* @access public | |
* @param string the table to retrieve the results from | |
* @param array an associative array of insert values | |
* @param bool whether or not to replace or add on update | |
* @return object | |
*/ | |
function insert_on_duplicate_update($table = '', $set = NULL, $add = false) | |
{ | |
if ( ! is_null($set)) | |
{ | |
$this->set($set); | |
} | |
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]; | |
} | |
$sql = $this->_insert_on_duplicate_update($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set), $add); | |
$this->_reset_write(); | |
return $this->query($sql); | |
} | |
/** | |
* Insert_On_Duplicate_Update_Batch | |
* | |
* Compiles batch insert strings and runs the queries | |
* MODIFIED to do a MySQL 'ON DUPLICATE KEY UPDATE' | |
* | |
* @access public | |
* @param string the table to retrieve the results from | |
* @param array an associative array of insert values | |
* @param bool whether or not to replace or add on update | |
* @return object | |
*/ | |
function insert_on_duplicate_update_batch($table = '', $set = NULL, $add = false) | |
{ | |
if ( ! is_null($set)) | |
{ | |
$this->set($set); | |
} | |
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]; | |
} | |
//get all column names from first index only | |
foreach ($this->ar_set["`0`"] as $key => $val) { | |
$keys[] = $key; | |
} | |
//strip out the values from each item | |
foreach ($this->ar_set as $item) { | |
$itemVal = []; | |
foreach ($item as $key => $val) { | |
$itemVal[] = $val; | |
} | |
$values[] = $itemVal; | |
} | |
$sql = $this->_insert_on_duplicate_update_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $keys, $values, $add); | |
$this->_reset_write(); | |
return $this->query($sql); | |
} | |
/** | |
* Insert_on_duplicate_update statement | |
* | |
* Generates a platform-specific insert string from the supplied data | |
* MODIFIED to include ON DUPLICATE UPDATE | |
* | |
* @access private | |
* @param string the table name | |
* @param array the insert keys | |
* @param array the insert values | |
* @param bool changes how values are updated | |
* @return string | |
*/ | |
private function _insert_on_duplicate_update($table, $keys, $values, $add) | |
{ | |
if ($add) { | |
foreach($keys as $num => $key) { | |
if (strpos($key, 'id') === false) | |
$update_fields[] = $key .'='.$key.'+VALUES('. $key.')'; | |
} | |
} else { | |
foreach($keys as $num => $key) { | |
$update_fields[] = $key .'='. $values[$num]; | |
} | |
} | |
$sql = "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).") ON DUPLICATE KEY UPDATE ".implode(', ', $update_fields); | |
return $sql; | |
} | |
/** | |
* Insert_on_duplicate_update_batch statement | |
* | |
* Generates a platform-specific insert batch string from the supplied data | |
* MODIFIED to include ON DUPLICATE UPDATE | |
* | |
* @access private | |
* @param string the table name | |
* @param array the insert keys | |
* @param array the insert values | |
* @param bool changes how values are updated | |
* @return string | |
*/ | |
private function _insert_on_duplicate_update_batch($table, $keys, $allValues, $add) | |
{ | |
//create list of values within parenteses | |
foreach ($allValues as $values) { | |
$valueStr = '('; | |
$valueStr .= implode(', ', $values); | |
$valueStr .= ')'; | |
$theValues[] = $valueStr; | |
} | |
if ($add) { | |
foreach($keys as $num => $key) { | |
if (strpos($key, 'id') === false) | |
$update_fields[] = $key .'='.$key.'+VALUES('. $key.')'; | |
} | |
} else { | |
foreach($keys as $num => $key) { | |
if (strpos($key, 'id') === false) | |
$update_fields[] = $key .'='.$key.'VALUES('. $key.')'; | |
} | |
} | |
$sql = "INSERT INTO ".$table." (".implode(', ', $keys).") "; | |
$sql .= "VALUES ".implode(', ', $theValues)." ON DUPLICATE KEY UPDATE ".implode(', ', $update_fields); | |
return $sql; | |
} | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This extends the mysql driver which is deprecated and recommended to be using mysqli. There's a mysqli version on Github