Skip to content
Advertisement

Best way to INSERT many values in mysqli?

I’m looking for a SQL-injection-secure technique to insert a lot of rows (ca. 2000) at once with PHP and MySQLi.
I have an array with all the values that have to be include. Currently I’m doing that:

<?php
$array = array("array", "with", "about", "2000", "values");

foreach ($array as $one) 
{
    $query = "INSERT INTO table (link) VALUES ( ?)";
    $stmt = $mysqli->prepare($query);
    $stmt ->bind_param("s", $one);
    $stmt->execute();
    $stmt->close();
}
?>

I tried call_user_func_array(), but it caused a stack overflow.

What is a faster method to do this (like inserting them all at once?), but still secure against SQL injections (like a prepared statement) and stack overflows?

Advertisement

Answer

You should be able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside of your loop.

$array = array("array", "with", "about", "2000", "values");
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt ->bind_param("s", $one);

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

I tested this code with 10,000 iterations on my web server.

Without transaction: 226 seconds. With transaction: 2 seconds. Or a two order of magnitude speed increase, at least for that test.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement