PHP MySQLi Multiple attachments
I am wondering if prepared statements work the same way as normal mysql_query with multiple VALUES values.
INSERT INTO table (a,b) VALUES ('a','b'), ('c','d');
VS
$sql = $db->prepare('INSERT INTO table (a,b) VALUES (?, ?);
If I use a prepared statement in a loop, does MySQL optimize the background insert to work like the first part of the code, or is it just like running the first part of the code inside the loop with one value each time?
source to share
I went ahead and checked out a test where one query uses a prepared statement and the other runs the whole query and then runs that. I probably am not doing what I want to know is easy to understand.
Here is my test code. I thought that the ready-made statements kind of hold back until $ stmt-> close () is called to optimize it or whatever. This doesn't seem to be the case, but since a test that builds a query using real_escape_string is at least 10x faster.
<?php
$db = new mysqli('localhost', 'user', 'pass', 'test');
$start = microtime(true);
$a = 'a';
$b = 'b';
$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
$a = chr($i % 1);
$b = chr($i % 2);
$sql->execute();
}
$sql->close();
echo microtime(true) - $start;
$db->close();
?>
source to share
public function insertMulti($table, $columns = array(), $records = array(), $safe = false) {
self::$counter++;
//Make sure the arrays aren't empty
if (empty($columns) || empty($records)) {
return false;
}
// If set safe to true: set records values to html real escape safe html
if($safe === true){
$records = $this->filter($records);
}
//Count the number of fields to ensure insertion statements do not exceed the same num
$number_columns = count($columns);
//Start a counter for the rows
$added = 0;
//Start the query
$sql = "INSERT INTO " . $table;
$fields = array();
//Loop through the columns for insertion preparation
foreach ($columns as $field) {
$fields[] = '`' . $field . '`';
}
$fields = ' (' . implode(', ', $fields) . ')';
//Loop through the records to insert
$values = array();
foreach ($records as $record) {
//Only add a record if the values match the number of columns
if (count($record) == $number_columns) {
$values[] = '(\'' . implode('\', \'', array_values($record)) . '\')';
$added++;
}
}
$values = implode(', ', $values);
$sql .= $fields . ' VALUES ' . $values;
//echo $sql;
$query = $this->dbConnection->query($sql);
if ($this->dbConnection->error) {
$this->errorLog($this->dbConnection->error, $sql);
return false;
} else {
return $added;
}
}
This function will first prepare one INSERT query with multiple row values and insert it once. But this is not for bulk insertion right away.
source to share