Last active
January 28, 2017 19:53
-
-
Save awhitesong/304e9a90fe4ffc8392ce4ad223d58c5b to your computer and use it in GitHub Desktop.
BULK INSERT mysql PHP
This file contains hidden or 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
// START OF CLASSES | |
// This class creates the connection and passes parameters to prepared statement class. | |
class db extends mysqli { | |
public function m_prepare($partialQuery, $quoteCount) { | |
if ($quoteCount < 1) { | |
return false; | |
} | |
return new stmt($this, $partialQuery, $quoteCount); | |
} | |
public function close() { | |
return parent::close(); | |
} | |
} | |
// This class creates the Prepared Statement for Bulk Insert. | |
class stmt extends mysqli_stmt { | |
public function __construct($link, $partialQuery, $quoteCount) { | |
parent::__construct($link); | |
$this->partialQuery = $partialQuery; | |
$this->quoteString = " (" . str_repeat("?,", $quoteCount-1) . "?)"; | |
$this->mbind_reset(); | |
} | |
public function mbind_reset() { | |
unset($this->mbind_params); | |
unset($this->mbind_types); | |
$this->mbind_params = array(); | |
$this->mbind_types = array(); | |
$this->mbind_types[0] = ""; | |
$this->query = $this->partialQuery; | |
if ($this->prepareFlag == 1) { | |
$this->reset(); | |
$this->prepareFlag = 0; | |
} | |
$this->bindFlag = 0; | |
} | |
//use this one to bind params by reference | |
public function mbind_param($type, ...$param) { | |
$this->mbind_types[0].= $type; | |
$this->mbind_params = array_merge($this->mbind_params, $param); | |
if ($this->bindFlag == 1) { | |
$this->query .= "," . $this->quoteString; | |
} else { | |
$this->query .= $this->quoteString; | |
$this->bindFlag = 1; | |
} | |
} | |
public function mbind_param_do() { | |
$params = array_merge($this->mbind_types, $this->mbind_params); | |
return call_user_func_array(array($this, 'bind_param'), $this->makeValuesReferenced($params)); | |
} | |
private function makeValuesReferenced($arr){ | |
$refs = array(); | |
foreach($arr as $key => $value) | |
$refs[$key] = &$arr[$key]; | |
return $refs; | |
} | |
public function execute() { | |
$executeResult = false; | |
if(count($this->mbind_params)) { | |
$this->prepare($this->query); | |
$this->prepareFlag = 1; | |
$this->mbind_param_do(); | |
$executeResult = parent::execute(); | |
$this->mbind_reset(); | |
} else { | |
$this->error = "No binded parameters to execute"; | |
} | |
return $executeResult; | |
} | |
public function close() { | |
return parent::close(); | |
} | |
private $mbind_types = array(); | |
private $mbind_params = array(); | |
private $quoteString = ""; | |
private $query = ""; | |
private $partialQuery = ""; | |
private $bindFlag = 0; | |
private $prepareFlag = 0; | |
} | |
// END OF CLASSES | |
$paramCount = 4 // number of quotes/columns in the query i.e (?,?,?,?) | |
$_db = new db($dbhost, $dbuser, $dbpass, $dbname) or die('Could not connect'); | |
$query = "INSERT INTO `table` (`STRINGcol1`, `INTcol2`, `DECIMALcol3`, `STRINGcol4`) VALUES "; | |
$stmt = $_db->m_prepare($query, $paramCount) or die('Cannot insert or wrong function parameters'); | |
foreach ($arr as $key => $value) { | |
.. get your values | |
$stmt->mbind_param('sids', val1, val2, val3, val4); | |
} | |
$isExecute = $stmt->execute(); | |
$stmt->close(); | |
$_db->close(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment