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.