How to run 1000s insert queries with mysqli?

1. Comments

The key to have your multiple inserts fast and reliable is to use transactions and a prepared statement.

A transaction will ensure that all operations either complete in their entirety or have no effect whatsoever. Another important advantage of using a transaction is that it makes multiple inserts dramatically faster, eliminating all possible delays that could be caused by separate query execution.

A prepared statement will add some little boost to the speed and of course ensure that there will be no errors or injections.

First of all, make sure you have a proper mysqli connection, which, among other things, tells mysqli to throw an exception in case of error. Then just prepare your query, start a transaction, execute the queries and commit the transaction

include 'mysqli.php';

// a sample array of data
$data = [
    ['col1' => 'foo1', 'col2' => 'bar1'],
    ['col1' => 'foo2', 'col2' => 'bar2'],
];
// prepare the SQL query once
$stmt = $mysqli->prepare("INSERT INTO table SET col1 = ?, col2 = ?");

$mysqli->begin_transaction();
// loop over the data array
foreach ($data as $row) {
    $stmt->bind_param("ss", $row['col1'], $row['col2']);
    $stmt->execute();
}
$mysqli->commit();

note that it could be not just a single query but multiple different queries but the principle remains the same: just wrap all queries in a transaction and use prepared statements.