11- MySQL Multiple Insert

0
114

MySQL Multiple Insert Performs a multiple queries in MySQL, you need to use the function called “mysqli_multi_query”. This function allows you to perform one or more queries against the database, you only have to separate the query using the semicolon.

Example using MySQLi(Object-Oriented):

  1. die("Connection failed: " . $conn->connect_error);
  2. }
  3.  
  4. // sql to Insert record table
  5. $sql = "INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  6. VALUES ('Villanueva', 'Joken', 'Kabankalan City');";
  7. $sql .= "INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  8. VALUES ('Batuto', 'Erick Jason', 'Cebu City');";
  9. $sql .= "INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  10. VALUES ('Drapite', 'Bryan', 'Kabankalan City');";
  11.  
  12. if ($conn-&gt;multi_query($sql) === TRUE) {
  13. echo "New records created successfully";
  14. } else {
  15. echo "Error: " . $sql . "
  16. " . $conn-&gt;error;
  17. }
  18.  
  19. $conn-&gt;close();
  20. ?&gt;

Example using MySQLi(Procedural):

  1. $username = "root";
  2. $password = "";
  3. $database = "mysqltutorial";
  4.  
  5. // Create connection
  6. $conn = mysqli_connect($server, $username, $password, $database);
  7. // Check connection
  8. if (!$conn) {
  9. die("Connection failed: " . mysqli_connect_error());
  10. }
  11.  
  12. // sql to Insert record table
  13. $sql = "INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  14. VALUES ('Villanueva', 'Joken', 'Kabankalan City');";
  15. $sql .= "INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  16. VALUES ('Batuto', 'Erick Jason', 'Cebu City');";
  17. $sql .= "INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  18. VALUES ('Drapite', 'Bryan', 'Kabankalan City');";
  19.  
  20. if (mysqli_multi_query($conn, $sql)) {
  21.  
  22. echo "New record created successfully!";
  23. } else {
  24. echo "Error: " . $sql . "
  25. " . mysqli_error($conn);
  26. }
  27.  
  28. mysqli_close($conn);
  29. ?&gt;

Example using PDO:

  1. $conn-&gt;beginTransaction();
  2.  
  3. // sql to Insert record table
  4.  
  5. $conn-&gt;exec("INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  6. VALUES ('Villanueva', 'Joken', 'Kabankalan City');");
  7. $conn-&gt;exec ("INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  8. VALUES ('Batuto', 'Erick Jason', 'Cebu City');");
  9. $conn-&gt;exec ("INSERT INTO <code>tblpeople</code> (<code>LNAME</code>, <code>FNAME</code>, <code>ADDRESS</code>)
  10. VALUES ('Drapite', 'Bryan', 'Kabankalan City');");
  11.  
  12. // commit the changes for transaction
  13. $conn-&gt;commit();
  14. echo "New record created successfully!";
  15. }
  16. catch(PDOException $e)
  17. {
  18. // do roll back if something went wrong
  19. $conn-&gt;rollback();
  20. echo $sql . "
  21. " . $e-&gt;getMessage();
  22. }
  23.  
  24. $conn = null;
  25. ?&gt;

Our Previous Topic Called: 10- MySQL Last Inserted

Facebook Comments

LEAVE A REPLY

Please enter your comment!
Please enter your name here