What is the best way to insert multiple rows in PHP PDO MYSQL?

by

You have at least these two options:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values (?,?,?)";

$stmt = $db->prepare($sql);

foreach($rows as $row)
{
    $stmt->execute($row);
}

OR:

$rows = [(1,2,3), (4,5,6), (7,8,9) ... ];

$sql = "insert into `table_name` (col1, col2, col3) values ";

$paramArray = array();

$sqlArray = array();

foreach($rows as $row)
{
    $sqlArray[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';

    foreach($row as $element)
    {
        $paramArray[] = $element;
    }
}

// $sqlArray will look like: ["(?,?,?)", "(?,?,?)", ... ]

// Your $paramArray will basically be a flattened version of $rows.

$sql .= implode(',', $sqlArray);

$stmt = $db->prepare($sql);

$stmt->execute($paramArray);

As you can see the first version features a lot simpler code; however the second version does execute a batch insert. The batch insert should be faster, but I agree with @BillKarwin that the performance difference will not be noticed in the vast majority of implementations.

More Related Contents:

  1. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  2. Insert form in MySQL with PDO [duplicate]
  3. Undefined index $_POST
  4. Can I mix MySQL APIs in PHP?
  5. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  6. How can I do ‘insert if not exists’ in MySQL?
  7. Getting raw SQL query string from PDO prepared statements
  8. How do I set ORDER BY params using prepared PDO statement?
  9. “Invalid parameter number: parameter was not defined” Inserting data
  10. Having issue with matching rows in the database using PDO
  11. How can I properly use a PDO object for a parameterized SELECT query
  12. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  13. PDO with INSERT INTO through prepared statements [closed]
  14. PDO::fetchAll vs. PDO::fetch in a loop
  15. PHP Connection failed: SQLSTATE[HY000] [2002] Connection refused
  16. Real escape string and PDO [duplicate]
  17. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  18. PDO Connection Test
  19. PHP PDO vs normal mysql_connect
  20. Can I create a database using PDO in PHP?
  21. PHP PDO and MySQLi [duplicate]
  22. PDO fetch one column from table into 1-dimensional array
  23. Getting a PHP PDO connection from a mysql_connect()?
  24. PDO error: SQLSTATE[HY000]: General error: 2031
  25. Having issue with matching rows in the database
  26. MySQL and PHP – insert NULL rather than empty string
  27. Checking for an empty result (PHP, PDO, and MySQL) [duplicate]
  28. PHP/SQL Insert Error when using Named Placeholders
  29. PDO bindParam into one statement?
  30. Is a BLOB converted using the current/default charset in MySQL?

Leave a Comment