PDO Prepared Inserts multiple rows in single query

Question

I am currently using this type of SQL on MySQL to insert multiple rows of values in one single query   INSERT INTO  tbl    key1   key2   VALUES   r1v1   r1v2     r2v1   r2v2         On the readings on PDO  the use prepared statements should give me a better security than static queries   I would therefore like to know whether it is possible to generate  inserting multiple rows of values by the use of one query  using prepared statements    If yes  may I know how can I implement it

User · Answer

Here s a class I wrote do multiple inserts with purge option    lt  php          pdo- gt beginTransaction        pmi   new PDOMultiLineInserter  pdo   foo   array  a   b   c   e    10       pmi- gt insertRow  data               pmi- gt insertRow  data       pmi- gt purgeRemainingInserts        pdo- gt commit           class PDOMultiLineInserter       private   purgeAtCount      private   bigInsertQuery    singleInsertQuery      private   currentlyInsertingRows    array        private   currentlyInsertingCount   0      private   numberOfFields      private   error      private   insertCount   0       function   construct  PDO  pdo   tableName   fieldsAsArray   bigInsertCount   100             this- gt  numberOfFields   count  fieldsAsArray            insertIntoPortion    INSERT INTO   tableName      implode         fieldsAsArray      VALUES            questionMarks          str repeat        this- gt  numberOfFields - 1                 this- gt  purgeAtCount    bigInsertCount           this- gt  bigInsertQuery       pdo- gt prepare  insertIntoPortion  questionMarks str repeat       questionMarks   bigInsertCount - 1             this- gt  singleInsertQuery    pdo- gt prepare  insertIntoPortion  questionMarks              function insertRow  rowData                todo Compare speed             this- gt  currentlyInsertingRows   array merge  this- gt  currentlyInsertingRows   rowData           foreach  rowData as  v  array push  this- gt  currentlyInsertingRows   v                      if     this- gt  currentlyInsertingCount     this- gt  purgeAtCount                if   this- gt  bigInsertQuery- gt execute  this- gt  currentlyInsertingRows      FALSE                     this- gt  error    Failed to perform a multi-insert  after   this- gt  insertCount  inserts   the following errors occurred   implode   lt br  gt     this- gt  bigInsertQuery- gt errorInfo                     return false                             this- gt  insertCount                  this- gt  currentlyInsertingCount   0               this- gt  currentlyInsertingRows   array                      return true             function purgeRemainingInserts             while   this- gt  currentlyInsertingCount  gt  0                 singleInsertData   array                    todo Compare speed - http   www evardsson com blog 2010 02 05 comparing-php-array shift-to-array pop                 for   i   0   i  lt   this- gt  numberOfFields   i     singleInsertData     array pop  this- gt  currentlyInsertingRows   array reverse  singleInsertData               for   i   0   i  lt   this- gt  numberOfFields   i    array unshift  singleInsertData  array pop  this- gt  currentlyInsertingRows                 if   this- gt  singleInsertQuery- gt execute  singleInsertData      FALSE                     this- gt  error    Failed to perform a small-insert  whilst purging the remaining rows  the following errors occurred   implode   lt br  gt     this- gt  singleInsertQuery- gt errorInfo                     return false                             this- gt  currentlyInsertingCount--                       public function getError             return  this- gt  error

User · Answer

My real world example to insert all german postcodes into an empty table  to add town names later       obtain column template  stmt    db- gt prepare  SHOW COLUMNS FROM towns     stmt- gt execute     columns   array fill keys array values  stmt- gt fetchAll PDO  FETCH COLUMN    null      multiple INSERT  postcode    01000     smallest german postcode while   postcode  lt   99999      highest german postcode      values   array        while   postcode  lt   99999               reset row          row    columns             now fill our row with data          row  postcode     sprintf   05d    postcode              build INSERT array         foreach   row as  value                 values      value                     postcode               avoid memory kill         if     postcode   10000                 break                         build query      count columns   count  columns        placeholder          substr str repeat        count columns   1                        placeholder group   substr str repeat  placeholder  count  values     count columns   1                            into columns   implode      array keys  columns     col1 col2 col3        this part is optional       on duplicate   array        foreach   columns as  column   gt   row             on duplicate      column           on duplicate      column             on duplicate     ON DUPLICATE KEY UPDATE    vsprintf substr str repeat     s   VALUES  s     count columns   1    on duplicate          execute query      stmt    db- gt prepare  INSERT INTO towns       into columns      VALUES     placeholder group    on duplicate    INSERT INTO towns  col1 col2 col3  VALUES                    ON DUPLICATE          stmt- gt execute  values       As you can see its fully flexible  You don t need to check the amount of columns or check on which position your column is  You only need to set the insert data        row  postcode     sprintf   05d    postcode     I m proud of some of the query string constructors as they work without heavy array-functions like array merge  Especially vsprintf   was a good find   Finally I needed to add 2x while   to avoid exceeding the memory limit  This depends on your memory limit but at all its a good general solution to avoid problems  and having 10 queries is still much better than 10 000

User · Answer

Two possible approaches    stmt    pdo- gt prepare  INSERT INTO foo VALUES  v1 1   v1 2   v1 3         v2 1   v2 2   v2 3         v2 1   v2 2   v2 3      stmt- gt bindValue   v1 1    data 0  0     stmt- gt bindValue   v1 2    data 0  1     stmt- gt bindValue   v1 3    data 0  2       etc     stmt- gt execute      Or    stmt    pdo- gt prepare  INSERT INTO foo VALUES  a   b   c     foreach  data as  item         stmt- gt bindValue   a    item 0         stmt- gt bindValue   b    item 1         stmt- gt bindValue   c    item 2         stmt- gt execute        If the data for all the rows are in a single array  I would use the second solution

User · Answer

That s simply not the way you use prepared statements    It is perfectly okay to insert one row per query because you can execute one prepared statement multiple times with different parameters  In fact that is one of the greatest advantages as it allows you to insert you a great number of rows in an efficient  secure and comfortable manner   So it maybe possible to implement the scheme you proposing  at least for a fixed number of rows  but it is almost guaranteed that this is not really what you want

User · Answer

Multiple Values Insert with PDO Prepared Statements Inserting multiple values in one execute statement  Why because according to this page it is faster than regular inserts   datafields   array  fielda    fieldb            data     array  fielda    gt   value    fieldb    gt   value          data     array  fielda    gt   value    fieldb    gt   value          more data values or you probably have a loop that populates data  With prepared inserts you need to know the fields you re inserting to  and the number of fields to create the   placeholders to bind your parameters  insert into table  fielda  fieldb        values                         That is basically how we want the insert statement to look like  Now  the code  function placeholders  text   count 0   separator  quot   quot         result   array        if  count  gt  0           for  x 0   x lt  count   x                  result      text                       return implode  separator   result       pdo- gt beginTransaction       also helps speed up your inserts   insert values   array    foreach  data as  d        question marks            placeholders      sizeof  d               insert values   array merge  insert values  array values  d        sql    quot INSERT INTO table   quot    implode  quot   quot    datafields      quot   VALUES  quot           implode       question marks     stmt    pdo- gt prepare   sql    stmt- gt execute  insert values    pdo- gt commit     Although in my test  there was only a 1 sec difference when using multiple inserts and regular prepared inserts with single value

User · Answer

This is how I did it   First define the column names you ll use  or leave it blank and pdo will assume you want to use all the columns on the table - in which case you ll need to inform the row values in the exact order they appear on the table    cols    name    middleName    eMail    table    people     Now  suppose you have a two dimensional array already prepared  Iterate it  and construct a string with your row values  as such   foreach    people as  person     if    rowVals      rows            name              middleName                        eMail            else    rowVals             name              middleName              eMail              Now  what you just did was check if  rows was already defined  and if not  create it and store row values and the necessary SQL syntax so it will be a valid statement  Note that strings should go inside double quotes and single quotes  so they will be promptly recognized as such   All it s left to do is prepare the statement and execute  as such    stmt    db- gt prepare    INSERT INTO  table  cols VALUES  rowVals      stmt- gt execute       Tested with up to 2000 rows so far  and the execution time is dismal  Will run some more tests and will get back here in case I have something further to contribute   Regards

User · Answer

what about something like this           if count  types of values  gt 0             uid   1            x   0            sql                 values   array              foreach  types of values as  k  gt  v                sql       id  k  kind of val  k                   values   id  k      uid               values   kind of val  k      v                        sql   substr  sql 0 -2             query    INSERT INTO table  id value type  VALUES  sql             res    this- gt db- gt prepare  query             res- gt execute  values                           The idea behind this is to cycle through your array values  adding  id numbers  to each loop for your prepared statement placeholders while at the same time  you add the values to your array for the binding parameters  If you don t like using the  key  index from the array  you could add  i 0  and  i   inside the loop  Either works in this example  even if you have associative arrays with named keys  it would still work providing the keys were unique  With a little work it would be fine for nested arrays too      Note that substr strips the  sql variables last space and comma  if you don t have a space you d need to change this to -1 rather than -2

User · Answer

Most of the solutions given here to create the prepared query are more complex that they need to be  Using PHP s built in functions you can easily creare the SQL statement without significant overhead    Given  records  an array of records where each record is itself an indexed array  in the form of field   gt  value   the following function will insert the records into the given table  table  on a PDO connection  connection  using only a single prepared statement  Note that this is a PHP 5 6  solution because of the use of argument unpacking in the call to array push   private function import PDO  connection   table  array  records         fields   array keys  records 0         placeHolders   substr str repeat       count  fields    1        values           foreach   records as  record            array push  values     array values  record                query    INSERT INTO      table              query    implode       fields        query       VALUES          query    implode        array fill 0  count  records    placeHolders         query               statement    connection- gt prepare  query        statement- gt execute  values

User · Answer

For what it is worth  I have seen a lot of users recommend iterating through INSERT statements instead of building out as a single string query as the selected answer did  I decided to run a simple test with just two fields and a very basic insert statement    lt  php require  conn php      fname    J    lname    M     time start   microtime true    stmt    db- gt prepare  INSERT INTO table  FirstName  LastName  VALUES   fname   lname      for  i   1   i  lt   10   i             stmt- gt bindParam   fname    fname        stmt- gt bindParam   lname    lname        stmt- gt execute          fname     O        lname     A        time end   microtime true    time    time end -  time start   echo  Completed in     time    seconds  lt hr gt      fname2    J    lname2    M     time start2   microtime true    qry    INSERT INTO table  FirstName  LastName  VALUES     qry                qry                qry                qry                qry                qry                qry                qry                qry                qry               stmt2    db- gt prepare  qry    values   array     for  j   1   j lt  10   j           values2   array  fname2   lname2        values   array merge  values  values2         fname2     O        lname2     A       stmt2- gt execute  values     time end2   microtime true    time2    time end2 -  time start2   echo  Completed in     time2    seconds  lt hr gt      gt    While the overall query itself took milliseconds or less  the latter  single string  query was consistently 8 times faster or more  If this was built out to say reflect an import of thousands of rows on many more columns  the difference could be enormous

User · Answer

The Accepted Answer by Herbert Balagtas works well when the  data array is small  With larger  data arrays the array merge function becomes prohibitively slow  My test file to create the  data array has 28 cols and is about 80 000 lines  The final script took 41s to complete  Using array push   to create  insert values instead of array merge   resulted in a 100X speed up with execution time of 0 41s  The problematic array merge     insert values   array     foreach  data as  d     question marks            placeholders      sizeof  d            insert values   array merge  insert values  array values  d       To eliminate the need for array merge    you can build the following two arrays instead    Note that these fields are empty  but the field count should match the fields in  datafields   data     array                 n        getting rid of array merge   array push  insert values   value1   value2   value3     n      These arrays can then be used as follows  function placeholders  text   count 0   separator  quot   quot         result   array        if  count  gt  0           for  x 0   x lt  count   x                  result      text                       return implode  separator   result       pdo- gt beginTransaction     foreach  data as  d     question marks            placeholders      sizeof  d              sql    quot INSERT INTO table   quot    implode  quot   quot   array keys  datafield       quot   VALUES  quot    implode       question marks     stmt    pdo- gt prepare  sql    stmt- gt execute  insert values    pdo- gt commit

User · Answer

Based on my experiments I found out that mysql insert statement with multiple value rows in single transaction is the fastest one   However  if the data is too much then mysql s max allowed packet setting might restrict the single transaction insert with multiple value rows  Hence  following functions will fail when there is data greater than mysql s max allowed packet size    singleTransactionInsertWithRollback singleTransactionInsertWithPlaceholders singleTransactionInsert   The most successful one in insert huge data scenario is transactionSpeed method  but it consumes time more the above mentioned methods  So  to handle this problem you can either split your data into smaller chunks and call single transaction insert multiple times or give up speed of execution by using transactionSpeed method   Here s my research   lt  php  class SpeedTestClass       private  data       private  pdo       public function   construct                  this- gt data                this- gt pdo   new  PDO  mysql dbname test data    admin    admin            if    this- gt pdo                die  Failed to connect to database                         public function createData                  prefix    test            postfix    unicourt com            salutations     Mr     Ms     Dr     Mrs               csv       Salutation    First Name    Last Name    Email Address            for   i   0   i  lt  100000     i                 csv                        salutations  i    count  salutations                     prefix  i                   prefix  i                   prefix  i      postfix                                     this- gt data    csv             public function truncateTable                  this- gt pdo- gt query  TRUNCATE TABLE  name                public function transactionSpeed                  timer1   microtime true            this- gt pdo- gt beginTransaction             sql    INSERT INTO  name    first name    last name   VALUES   first name   last name             sth    this- gt pdo- gt prepare  sql            foreach   array slice  this- gt data  1  as  values                 sth- gt execute                     first name    gt   values 1                     last name    gt   values 2                                          timer2   microtime true              echo  Prepare Time      timer2 -  timer1  PHP EOL              timer3   microtime true            if    this- gt pdo- gt commit                  echo  Commit failed n                      timer4   microtime true              echo  Commit Time      timer4 -  timer3  PHP EOL           return  timer4 -  timer1             public function autoCommitSpeed                  timer1   microtime true            sql    INSERT INTO  name    first name    last name   VALUES   first name   last name             sth    this- gt pdo- gt prepare  sql           foreach   array slice  this- gt data  1  as  values                 sth- gt execute                     first name    gt   values 1                     last name    gt   values 2                                      timer2   microtime true            return  timer2 -  timer1             public function noBindAutoCommitSpeed                  timer1   microtime true            foreach   array slice  this- gt data  1  as  values                 sth    this- gt pdo- gt prepare  INSERT INTO  name    first name    last name   VALUES     values 1        values 2                     sth- gt execute                       timer2   microtime true            return  timer2 -  timer1             public function singleTransactionInsert                  timer1   microtime true           foreach   array slice  this- gt data  1  as  values                 arr          values 1        values 2                          sth    this- gt pdo- gt prepare  INSERT INTO  name    first name    last name   VALUES   implode        arr             sth- gt execute             timer2   microtime true            return  timer2 -  timer1             public function singleTransactionInsertWithPlaceholders                  placeholders                timer1   microtime true            sql    INSERT INTO  name    first name    last name   VALUES            foreach   array slice  this- gt data  1  as  values                 placeholders                            arr      values 1                arr      values 2                      sql    implode        placeholders            sth    this- gt pdo- gt prepare  sql            sth- gt execute  arr            timer2   microtime true            return  timer2 -  timer1             public function singleTransactionInsertWithRollback                  placeholders                timer1   microtime true            sql    INSERT INTO  name    first name    last name   VALUES            foreach   array slice  this- gt data  1  as  values                 placeholders                            arr      values 1                arr      values 2                      sql    implode        placeholders            this- gt pdo- gt beginTransaction             sth    this- gt pdo- gt prepare  sql            sth- gt execute  arr            this- gt pdo- gt commit             timer2   microtime true            return  timer2 -  timer1            s   new SpeedTestClass     s- gt createData     s- gt truncateTable    echo  Time Spent for singleTransactionInsertWithRollback    s- gt singleTransactionInsertWithRollback     PHP EOL   s- gt truncateTable    echo  Time Spent for single Transaction Insert    s- gt singleTransactionInsert     PHP EOL   s- gt truncateTable    echo  Time Spent for single Transaction Insert With Placeholders    s- gt singleTransactionInsertWithPlaceholders     PHP EOL   s- gt truncateTable    echo  Time Spent for transaction    s- gt transactionSpeed     PHP EOL   s- gt truncateTable    echo  Time Spent for AutoCommit    s- gt noBindAutoCommitSpeed     PHP EOL   s- gt truncateTable    echo  Time Spent for autocommit with bind    s- gt autoCommitSpeed     PHP EOL   s- gt truncateTable      The results for 100 000 entries for a table containing only two columns is as below    php data php Time Spent for singleTransactionInsertWithRollback  0 75147604942322 Time Spent for single Transaction Insert  0 67445182800293 Time Spent for single Transaction Insert With Placeholders  0 71131205558777 Time Spent for transaction  8 0056409835815 Time Spent for AutoCommit  35 4979159832 Time Spent for autocommit with bind  33 303519010544

User · Answer

Same answer as Mr  Balagtas  slightly clearer    Recent versions MySQL and PHP PDO do support multi-row INSERT statements  SQL Overview The SQL will look something like this  assuming a 3-column table you d like to INSERT to  INSERT INTO tbl name              colA  colB  colC       VALUES                                         ON DUPLICATE KEY UPDATE works as expected even with a multi-row INSERT  append this  ON DUPLICATE KEY UPDATE colA   VALUES colA   colB   VALUES colB   colC   VALUES colC   PHP Overview Your PHP code will follow the usual  pdo- gt prepare  qry  and  stmt- gt execute  params  PDO calls   params will be a 1-dimensional array of all the values to pass to the INSERT  In the above example  it should contain 9 elements  PDO will use every set of 3 as a single row of values   Inserting 3 rows of 3 columns each   9 element array   Implementation Below code is written for clarity  not efficiency  Work with the PHP array     functions for better ways to map or walk through your data if you d like  Whether you can use transactions obviously depends on your MySQL table type  Assuming    tblName  - the string name of the table to INSERT to  colNames - 1-dimensional array of the column names of the table These column names must be valid MySQL column identifiers  escape them with backticks      if they are not  dataVals - mutli-dimensional array  where each element is a 1-d array of a row of values to INSERT  Sample Code    setup data values for PDO    memory warning  this is creating a copy all of  dataVals  dataToInsert   array     foreach   dataVals as  row   gt   data        foreach  data as  val             dataToInsert      val               optional  setup the ON DUPLICATE column names  updateCols   array     foreach   colNames as  curCol         updateCols      curCol    quot    VALUES  curCol  quot       onDup   implode        updateCols       setup the placeholders - a fancy way to make the long  quot              quot  string  rowPlaces         implode       array fill 0  count  colNames                 allPlaces   implode       array fill 0  count  dataVals    rowPlaces      sql    quot INSERT INTO  tblName   quot    implode        colNames          quot   VALUES  quot     allPlaces    quot  ON DUPLICATE KEY UPDATE  onDup quot       and then the PHP PDO boilerplate  stmt    pdo- gt prepare   sql     stmt- gt execute  dataToInsert     pdo- gt commit

User · Answer

Here is my simple approach        values   array        foreach  workouts id as  value           value        value      plan id            array push  values   value              values    implode      values         sql    INSERT INTO plan days id name  VALUES     values           stmt    this- gt conn- gt prepare  sql        stmt- gt execute

User · Answer

You can insert multiple rows in a single query with this function   function insertMultiple  query  rows        if  count  rows  gt 0             args   array fill 0  count  rows 0                    params   array            foreach  rows as  row                         values         implode       args                   foreach  row as  value                                 params      value                                    query    query   VALUES   implode       values            stmt    PDO- gt prepare  query            stmt- gt execute  params              row is an array of arrays of values  In your case you would call the function with  insertMultiple  INSERT INTO tbl   key1   key2    array array  r1v1   r1v2   array  r2v1   r2v2        This has the benefit that you use prepared statements  while inserting multiple rows with a single query  Security

User · Answer

Here is another  slim  solution for this issue   At first you need to count the data of the source array  here   aData  with count    Then you use array fill   and generate a new array wich as many entries as the source array has  each with the value          the number of placeholders depends on the fields you use  here  2   Then the generated array needs to be imploded and as glue a comma is used  Within the foreach loop  you need to generate another index regarding on the number of placeholders you use  number of placeholders   current array index   1   You need to add 1 to the generated index after each binded value    do    db- gt prepare  INSERT INTO table  id  name  VALUES   implode      array fill 0  count  aData                foreach  aData as  iIndex   gt   aValues     iRealIndex   2    iIndex   1    do- gt bindValue  iRealIndex   aValues  id    PDO  PARAM INT     iRealIndex    iRealIndex   1    do- gt bindValue  iRealIndex   aValues  name    PDO  PARAM STR       do- gt execute

User · Answer

Although an old question all the contributions helped me a lot so here s my solution  which works within my own DbContext class  The  rows parameter is simply an array of associative arrays representing rows or models  field name   gt  insert value   If you use a pattern that uses models this fits in nicely when passed model data as an array  say from a ToRowArray method within the model class      Note  It should go without saying but never allow the arguments passed   to this method to be exposed to the user or reliant on any user input  other than the insert values  which have been validated and sanitised  The  tableName argument and the column names should be defined by the calling logic  for instance a User model could be mapped to the user table  which has its column list mapped to the model s member fields    public function InsertRange  tableName   rows           Get column list      columnList   array keys  rows 0         numColumns   count  columnList        columnListString   implode       columnList           Generate pdo param placeholders      placeHolders   array         foreach  rows as  row                 temp   array             for  i   0   i  lt  count  row    i                 temp                    placeHolders           implode       temp                     placeHolders   implode       placeHolders           Construct the query      sql    insert into  tableName   columnListString  values  placeHolders        stmt    this- gt pdo- gt prepare  sql         j   1      foreach  rows as  row                for  i   0   i  lt   numColumns   i                           stmt- gt bindParam  j   row  columnList  i                  j                          stmt- gt execute

User · Answer

I had the same problem and this is how i accomplish for myself  and i made a function for myself for it   and you can use it if that helps you    Example   INSERT INTO countries  country  city  VALUES  Germany  Berlin    France  Paris     arr1   Array  Germany    Berlin     arr2   Array  France    France     insertMultipleData  countries   Array  arr1   arr2         Inserting multiple data to the Database  public function insertMultipleData  table   multi params       try           db    this- gt connect              beforeParams                paramsStr                valuesStr                for   i 0   i  lt  count  multi params    i                    foreach   multi params  i  as  j   gt   value                                        if   i    0                         beforeParams            j                                            paramsStr             j          i                                                                          paramsStr   substr replace  paramsStr      -1                valuesStr            paramsStr                       paramsStr                            beforeParams   substr replace  beforeParams      -1            valuesStr   substr replace  valuesStr      -1              sql    INSERT INTO      table           beforeParams      VALUES      valuesStr                  stmt    db- gt prepare  sql             for   i 0   i  lt  count  multi params    i                   foreach   multi params  i  as  j   gt   amp  value                     stmt- gt bindParam        j          i   value                                                                           this- gt close  db            stmt- gt execute                                    return true        catch PDOException  e                       return false             return false        Making connection to the Database      public function connect             host   Constants  DB HOST           dbname   Constants  DB NAME           user   Constants  DB USER           pass   Constants  DB PASS            mysql connect str    mysql host     host     dbname     dbname            dbConnection   new PDO  mysql connect str   user   pass            dbConnection- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION            return  dbConnection                Closing the connection     public function close  db            db   null          If insertMultipleData  table   multi params  returns TRUE  your data has been inserted to your database

User · Answer

A shorter answer  flatten the array of data ordered by columns then     array   array   1   2   3   4   5    1   2   3   4   5     arCount   count  array    rCount     arCount     arCount - 1   0    criteria   sprintf              s   str repeat                  rCount     sql    INSERT INTO table c1 c2 c3 c4 c5  VALUES criteria     When inserting a 1 000 or so records you don t want to have to loop through every record to insert them when all you need is a count of the values

User · Answer

This worked for me     sql    INSERT INTO table pk pk1 pk pk2 date pk 3  VALUES      qPart   array fill 0  count  array          UTC TIMESTAMP          sql    implode       qPart    stmt      DB  prepare  base    sql    i   1  foreach   array as  value        stmt- gt bindValue  i     value      stmt- gt bindValue  i     pk pk1      stmt- gt bindValue  i     pk pk2       stmt- gt bindValue  i     pk pk3        stmt- gt execute

User · Answer

Since it has not been suggested yet  I m pretty sure LOAD DATA INFILE is still the fastest way to load data as it disables indexing  inserts all data  and then re-enables the indexes - all in a single request   Saving the data as a csv should be fairly trivial keeping in mind fputcsv  MyISAM is fastest  but you still get big performance in InnoDB  There are other disadvantages  though so I would go this route if you are inserting a lot of data  and not bother with under 100 rows

User · Answer

Here is my solution  https   github com sasha-ch Aura Sql based on auraphp Aura Sql library   Usage example    q    insert into t2 id name  values            on duplicate key update name name     bind values       1  str1    2  str2        pdo- gt perform  q   bind values     Bugreports are welcome

[php] PDO Prepared Inserts multiple rows in single query

Examples related to php

Examples related to pdo

Examples related to insert

Examples related to prepared-statement