Skip to content

Instantly share code, notes, and snippets.

@awhitesong
Last active January 28, 2017 19:53
Show Gist options
  • Save awhitesong/304e9a90fe4ffc8392ce4ad223d58c5b to your computer and use it in GitHub Desktop.
Save awhitesong/304e9a90fe4ffc8392ce4ad223d58c5b to your computer and use it in GitHub Desktop.
BULK INSERT mysql PHP
// 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