Mysqli multiple row insert, simple multi insert query [duplicate]

by

The mysqli class provides a number of different ways of accomplishing your inserts, each with its own benefits. Certainly, one of them should fit your needs.

The following examples assume that your unspecified “extracted data” is stored in an array of arrays: $bigArray[0…datasetsize][0…2].

The mysqli database is assumed to be $db.

Method 1 – As Simple As Possible

If you want to use a prepared statement and parameter binding, a first effort might look like the following. While not optimal, the statement is only prepared once. However, the variables are bound for each insert, which is wasteful (but simple). Since inserts are not bundled, the example loops over 10.

$statement = $db->prepare("INSERT INTO testTable (fieldA, fieldB, fieldC) VALUES (?,?,?)");
for ($i = 0; $i < 10; ++$i)
{
    $statement->bind_param("iii",$bigArray[$i][0],$bigArray[$i][1],$bigArray[$i][2]);
    $statement->execute();
}

Method 2 – Optimized

Prepared statements and multiple inserts combined enable performance which is nearly identical to the raw insert queries of Method 1. Actual results will vary depending on your setup, but a quick test on my system with both a local and a remote database showed performance a few percentage points faster with the optimized method, increasing a few points more if data in Method 1 needs to be escaped.

The following uses call_user_func_array, but you could avoid that if you know how many inserts you want to bundle each time and build call bind_param directly. That would further increase performance slightly.

$sql = "INSERT INTO testTable (fieldA,fieldB,fieldC) VALUES (?,?,?)".str_repeat(",(?,?,?)",count($bigArray)-1);
$statement = $db->prepare($sql);

// This is the type string used by statement::bind_param. 
// s will be good for any type.
$types = str_repeat("s", count($bigArray) * count($bigArray[0]));

$values = array_merge(...$bigArray);
$statement->bind_param($types, ...$values);
$statement->execute();

More Related Contents:

  1. Mysql No connection could be made because the target machine actively refused it
  2. Fatal error: Call to undefined function mysqli_result()
  3. mysqli – fetch_Array error call to a member function fetch_array() on a non-object mysqli [duplicate]
  4. which is better every user have a table or just a row [closed]
  5. PHP – Change value y,n in database [closed]
  6. Commands out of sync; you can’t run this command now
  7. PDOException SQLSTATE[HY000] [2002] No such file or directory
  8. Many-to-many relationships examples
  9. SELECT COUNT(*) AS count – How to use this count
  10. Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  11. How to upload images into MySQL database using PHP code
  12. mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL server through socket ‘MySQL’ (2)
  13. What is the difference between MySQL, MySQLi and PDO? [closed]
  14. MySQL check if a table exists without throwing an exception
  15. Maximum execution time in phpMyadmin
  16. Call to a member function execute() on boolean in [duplicate]
  17. Replacing mysql_* functions with PDO and prepared statements
  18. How to connect to MySQL database in PHP using mysqli extension?
  19. phpmyadmin automatic logout time
  20. MySQL skipping first row
  21. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  22. Laravel nested relationships
  23. Using PHP to execute multiple MYSQL Queries
  24. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  25. Why I am getting an error when using w3school tutorial? [duplicate]
  26. How to run multiple insert query in SQL using PHP in one go?
  27. Using Prepared Statement, how I return the id of the inserted row?
  28. PHP prepared statements and transactions in a loop [duplicate]
  29. When should I close a database connection in PHP?
  30. MySqli Commands out of sync; you can’t run this command now [duplicate]

Leave a Comment