Insert multiple rows with one query MySQL

Question

I want to write script  with function to multiple insert query  let me explain this better   I have one quantity input in html form   and I have MySQL query to insertregistered user into the table  so i want my function to insert this query for  quantity  times    mysql query  INSERT INTO  pxlot   realname email address phone status regtime ip    VALUES    realname    email    address    phone   0    dateTime    ip      or die  mysql error        Inserts the user    To insert this for example 3 times  Any suggestions

User · Answer

If you would like to insert multiple values lets say from multiple inputs that have different post values but the same table to insert into then simply use   mysql query  INSERT INTO  table   a b c d e f g  VALUES     a    b    c    d    e    f    g       a    b    c    d    e    f    g       a    b    c    d    e    f    g     or die  mysql error        Inserts 3 times in 3 different rows

User · Answer

servername    quot localhost quot     username    quot username quot     password    quot password quot     dbname    quot myDB quot        Create connection   conn   new mysqli  servername   username   password   dbname       Check connection  if   conn- gt connect error     die  quot Connection failed   quot     conn- gt connect error       sql    quot INSERT INTO MyGuests  firstname  lastname  email  VALUES   John    Doe    john example com    quot     sql     quot INSERT INTO MyGuests  firstname  lastname  email  VALUES   Mary    Moe    mary example com    quot     sql     quot INSERT INTO MyGuests  firstname  lastname  email  VALUES   Julie    Dooley    julie example com   quot      if   conn- gt multi query  sql      TRUE       echo  quot New records created successfully quot       else       echo  quot Error   quot     sql    quot  lt br gt  quot     conn- gt error       conn- gt close     Source W3schools

User · Answer

While inserting multiple rows with a single INSERT statement is generally faster  it leads to a more complicated and often unsafe code  Below I present the best practices when it comes to inserting multiple records in one go using PHP  To insert multiple new rows into the database at the same time  one needs to follow the following 3 steps   Start transaction  disable autocommit mode  Prepare INSERT statement Execute it multiple times  Using database transactions ensures that the data is saved in one piece and significantly improves performance  How to properly insert multiple rows using PDO PDO is the most common choice of database extension in PHP and inserting multiple records with PDO is quite simple   pdo   new  PDO  quot mysql host localhost dbname test charset utf8mb4 quot    user    password          PDO  ATTR ERRMODE   gt   PDO  ERRMODE EXCEPTION       PDO  ATTR EMULATE PREPARES   gt  false         Start transaction  pdo- gt beginTransaction        Prepare statement  stmt    pdo- gt prepare  INSERT      INTO  pxlot   realname email address phone status regtime ip       VALUES                        Perform execute   inside a loop    Sample data coming from a fictitious data set  but the data can come from anywhere foreach   dataSet as  data           All seven parameters are passed into the execute   in a form of an array      stmt- gt execute   data  name     data  email     data  address    getPhoneNo  data  name      0    data  regtime     data  ip            Commit the data into the database  pdo- gt commit     How to properly insert multiple rows using mysqli The mysqli extension is a little bit more cumbersome to use but operates on very similar principles  The function names are different and take slightly different parameters  mysqli report MYSQLI REPORT ERROR   MYSQLI REPORT STRICT    mysqli   new  mysqli  localhost    user    password    database     mysqli- gt set charset  utf8mb4        Start transaction  mysqli- gt begin transaction        Prepare statement  stmt    mysqli- gt prepare  INSERT      INTO  pxlot   realname email address phone status regtime ip       VALUES                        Perform execute   inside a loop    Sample data coming from a fictitious data set  but the data can come from anywhere foreach   dataSet as  data           mysqli doesn t accept bind in execute yet  so we have to bind the data first        The first argument is a list of letters denoting types of parameters  It s best to use  s  for all unless you need a specific type        bind param doesn t accept an array so we need to unpack it first using            stmt- gt bind param  sssssss        data  name     data  email     data  address    getPhoneNo  data  name      0    data  regtime     data  ip           stmt- gt execute          Commit the data into the database  mysqli- gt commit     Performance Both extensions offer the ability to use transactions  Executing prepared statement with transactions greatly improves performance  but it s still not as good as a single SQL query  However  the difference is so negligible that for the sake of conciseness and clean code it is perfectly acceptable to execute prepared statements multiple times  If you need a faster option to insert many records into the database at once  then chances are that PHP is not the right tool

User · Answer

In most cases inserting multiple records with one Insert statement is much faster in MySQL than inserting records with for foreach loop in PHP   Let s assume  column1 and  column2 are arrays with same size posted by html form   You can create your query like this    lt  php      query    INSERT INTO TABLE   column1    column2   VALUES         query parts   array        for  x 0   x lt count  column1    x              query parts             column1  x              column2  x                    echo  query    implode       query parts     gt    If data is posted for two records the query will become      INSERT INTO TABLE  column1  column2  VALUES   data    data      data    data

User · Answer

Here are a few ways to do it  INSERT INTO pxlot  realname email address phone status regtime ip   select   realname    email    address    phone   0    dateTime    ip   from SOMETABLEWITHTONSOFROWS LIMIT 3    or   INSERT INTO pxlot  realname email address phone status regtime ip   select   realname    email    address    phone   0    dateTime    ip  union all select   realname    email    address    phone   0    dateTime    ip  union all select   realname    email    address    phone   0    dateTime    ip    or  INSERT INTO pxlot  realname email address phone status regtime ip   values    realname    email    address    phone   0    dateTime    ip       realname    email    address    phone   0    dateTime    ip       realname    email    address    phone   0    dateTime    ip

User · Answer

INSERT INTO table  a b  VALUES  1 2    2 3    3 4     http   dev mysql com doc refman 5 5 en insert html

[php] Insert multiple rows with one query MySQL

Examples related to php

Examples related to mysql