ITSourceCode.com

We Exist to Provide 100% Free Source Code and Tutorials

Custom Search

11-MySQL Multiple Insert

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

Example using MySQLi(Object-Oriented):

 

  1. <?php
  2. $server = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "mysqltutorial";
  6.  
  7. // Create connection
  8. $conn = new mysqli($server, $username, $password, $database);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13.  
  14. // sql to Insert record table
  15. $sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
  16. VALUES ('Villanueva', 'Joken', 'Kabankalan City');";
  17. $sql .= "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
  18. VALUES ('Batuto', 'Erick Jason', 'Cebu City');";
  19. $sql .= "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
  20. VALUES ('Drapite', 'Bryan', 'Kabankalan City');";
  21.  
  22. if ($conn->multi_query($sql) === TRUE) {
  23.     echo "New records created successfully";
  24. } else {
  25.     echo "Error: " . $sql . "<br>" . $conn->error;
  26. }
  27.  
  28. $conn->close();
  29. ?>

Example using MySQLi(Procedural):

 

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

Example using PDO:

 

  1. <?php
  2.  
  3. $server = "localhost";
  4. $username = "root";
  5. $password = "";
  6. $database = "mysqltutorial";
  7.  
  8. try {
  9.     $conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
  10.     // set the PDO error mode to exception
  11.     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  12.  
  13.     //Should start the transaction
  14.     $conn->beginTransaction();
  15.    
  16.     // sql to Insert record table
  17.    
  18.     $conn->exec("INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
  19.     VALUES ('Villanueva', 'Joken', 'Kabankalan City');");
  20.     $conn->exec ("INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
  21.     VALUES ('Batuto', 'Erick Jason', 'Cebu City');");
  22.     $conn->exec ("INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
  23.     VALUES ('Drapite', 'Bryan', 'Kabankalan City');");
  24.    
  25.     // commit the changes for transaction
  26.     $conn->commit();   
  27.     echo "New record created successfully!";
  28.     }
  29. catch(PDOException $e)
  30.     {
  31.     // do roll back if something went wrong
  32.     $conn->rollback();
  33.     echo $sql . "<br>" . $e->getMessage();
  34.     }
  35.  
  36. $conn = null;
  37. ?>
Facebook Comments

Check Your Domain Ranking

Leave a Reply

Your email address will not be published. Required fields are marked *

ITSourceCode.com © 2016 Frontier Theme

Subscribe For Latest Updates

Signup for our newsletter and get notified when we publish new articles for free!