PHP Insert Multiple Data In MySQL

Submitted by: 
Visitors have accessed this post 3425 times.

If you are looking for a tutorial on How To Insert Multiple Data In MySQL then you are at the right place. In my previous tutorial, we already have a PHP Inserting Data To MySQL. We are going to use this source code and add some codes to have PHP Insert Multiple Data In MySQL.

This is our table that we are going to insert multiple data.

  1. CREATE TABLE `tbl_registration` (
  2. `tbl_registration_id` INT(11) NOT NULL,
  3. `first_name` VARCHAR(100) NOT NULL,
  4. `middle_name` VARCHAR(100) NOT NULL,
  5. `last_name` VARCHAR(100) NOT NULL,
  6. `email` VARCHAR(100) NOT NULL,
  7. `contact_number` VARCHAR(100) NOT NULL
  8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inserting Multiple Data Using MySQLi and PDO

Creating Our Connection

For MySQLi (Object-Oriented). Save it as "mysqli_o_o_connection.php".

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_multiple_data";
  6.  
  7. // Create connection
  8. $conn = new mysqli($servername, $username, $password, $dbname);
  9. // Check connection
  10. if ($conn->connect_error) {
  11. die("Connection failed: " . $conn->connect_error);
  12. }
  13. ?>

For MySQLi (Procedural) Save it as "mysqli_procedural_connection.php".

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_multiple_data";
  6.  
  7. // Create connection
  8. $conn = mysqli_connect($servername, $username, $password, $dbname);
  9. // Check connection
  10. if (!$conn) {
  11. die("Connection failed: " . mysqli_connect_error());
  12. }
  13. ?>

For PDO (PHP Data Objects) Save it as "pdo_connection.php".

  1. <?php
  2. $servername = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $dbname = "add_multiple_data";
  6.  
  7. $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
  8. ?>

PHP Query To Insert Multiple Data

Using MySQLi (Object-Oriented)

  1. <?php
  2. include ('mysqli_o_o_connection.php');
  3.  
  4. $sql = "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  5. VALUES ('John', 'Meyer', 'Doe', '<a href="mailto:johnDoe@gmail.com" rel="nofollow">[email protected]</a>', '345-2346');";
  6.  
  7. $sql .= "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  8. VALUES ('Mark', 'Meyer', 'Doe', '<a href="mailto:markDoe@gmail.com" rel="nofollow">[email protected]</a>', '235-5682');";
  9.  
  10. $sql .= "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  11. VALUES ('Jane', 'Meyer', 'Doe', '<a href="mailto:janeDoe@gmail.com" rel="nofollow">[email protected]</a>', '869-2356');";
  12.  
  13. if ($conn->multi_query($sql) === TRUE) {
  14. echo "Multiple Data Successfully Created!!!";
  15. } else {
  16. echo "Error: " . $sql . "<br>" . $conn->error;
  17. }
  18.  
  19. $conn->close();
  20. ?>

Using MySQLi (Procedural)

  1. <?php
  2. include ('mysqli_procedural_connection.php');
  3.  
  4. $sql .= "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  5. VALUES ('Mark', 'Meyer', 'Doe', '<a href="mailto:markDoe@gmail.com" rel="nofollow">[email protected]</a>', '235-5682');";
  6.  
  7. $sql = "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  8. VALUES ('John', 'Meyer', 'Doe', '<a href="mailto:johnDoe@gmail.com" rel="nofollow">[email protected]</a>', '345-2346');";
  9.  
  10. $sql .= "INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  11. VALUES ('Jane', 'Meyer', 'Doe', '<a href="mailto:janeDoe@gmail.com" rel="nofollow">[email protected]</a>', '869-2356');";
  12.  
  13. if (mysqli_multi_query($conn, $sql)) {
  14. echo "Multiple Data Successfully Created!!!";
  15. } else {
  16. echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  17. }
  18.  
  19. mysqli_close($conn);
  20. ?>

Using PDO (PHP Data Objects)

  1. <?php
  2. include ('pdo_connection.php');
  3.  
  4. try {
  5. // set the PDO error mode to exception
  6. $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  7.  
  8. // begin the transaction
  9. $conn->beginTransaction();
  10.  
  11. // our SQL statements
  12. $conn->exec("INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  13. VALUES ('Jane', 'Meyer', 'Doe', '<a href="mailto:janeDoe@gmail.com" rel="nofollow">[email protected]</a>', '869-2356');");
  14.  
  15. $conn->exec("INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  16. VALUES ('Mark', 'Meyer', 'Doe', '<a href="mailto:markDoe@gmail.com" rel="nofollow">[email protected]</a>', '235-5682');");
  17.  
  18. $conn->exec("INSERT INTO tbl_registration (first_name, middle_name, last_name, email, contact_number)
  19. VALUES ('John', 'Meyer', 'Doe', '<a href="mailto:johnDoe@gmail.com" rel="nofollow">[email protected]</a>', '345-2346');");
  20.  
  21. // commit the transaction
  22. $conn->commit();
  23. echo "Multiple Data Successfully Created!!!";
  24. }
  25. catch(PDOException $e)
  26. {
  27. // roll back the transaction if something failed
  28. $conn->rollback();
  29. echo "Error: " . $e->getMessage();
  30. }
  31.  
  32. $conn = null;
  33. ?>

So what can you say about this work? Share your thoughts in the comment section below or email me at [email protected]. Practice Coding. Thank you very much.


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.