It’s Festive Season! Enjoy 30% Off

Use code FESTIVE30 at checkout

Code has been added to clipboard!

Make MySQL Insert Multiple Rows at Once: Save Time and Code Lines

Reading time 2 min
Published Aug 8, 2017
Updated Oct 15, 2019

As you have learned to update your database tables by inserting and deleting rows, those processes seem easy. However, when you have a lot of data, it's handy to know how to make MySQL insert multiple rows to your data tables at once.

You can do that by using proper statements and following syntax requirements. Of course, first you have to establish a connection to your database and have at least one table created.

In this tutorial, we will explain how to insert multiple rows MySQL in the table you made before. It does not matter whether you use MySQLi or POD: they both allow to add more than one record.

MySQL Insert Multiple Rows: Main Tips

  • By using PHP MySQL insertion statements, you can insert multiple records at the same time.
  • This method might prove useful if you want to efficiently send multiple records with a single query instead of multiple queries. This will help your website run faster.
  • You have to make sure you use correct MySQL insert syntax.

Three Ways of Inserting Multiple Rows

MySQL insert examples below show how to insert three records into the table called users while using different types of connections. Let's start with the way to insert multiple rows with the MySQLi object-oriented connection:

Example
<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // Create connection
  $conn = new mysqli($host, $user, $pass, $dbname);
  // Check connection
  if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
  }
  $sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', '[email protected]');";
  $sql .= "INSERT INTO users (username, password, email) VALUES ('Margaret', 'Johnson', '[email protected]');";
  $sql .= "INSERT INTO users (username, password, email) VALUES ('Juliete', 'Doodley', '[email protected]');";
  if ($conn->multi_query($sql) === TRUE) {
    echo "New records created successfully";
  } else { 
    echo "Error: " . $sql . "<br>" . $conn->error;
  }
  $conn->close();  
?>

Note: Every SQL statement has to be separated by a semicolon (;).

Now, let's take a look at how to MySQL insert multiple rows using MySQLi procedural. In this case, you will use a function called mysqli_multi_query():

Example
<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // Create connection  
  $conn = mysqli_connect($host, $user, $pass, $db);  	
  // Check connection  	
  if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
  }
  $sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', '[email protected]');";  	
  $sql .= "INSERT INTO users (username, password, email) VALUES ('Margaret', 'Johnson', '[email protected]');"	
  $sql .= "INSERT INTO users (username, password, email) VALUES ('Juliete', 'Doodley', '[email protected]');";  	
  if (mysqli_multi_query($conn, $sql)) {    
    echo "New records created successfully";
  } else {    
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }
  mysqli_close($conn);  
?>

If you want to make MySQL insert multiple rows using PDO, slightly different MySQL insert syntax rules apply. You won't need to use mysqli_multi_query() for a PHP MySQL insert in this case. Take a look:

Example
<?php
  $server = 'server';
  $user = 'user';  	
  $pass = 'pass';
  $db = 'db';
  try {    
    $con = new PDO("mysql:host=$server;db=$db", $user, $pass);
    // PDO error mode is set to exception
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // the transaction begins
    $con->beginTransaction();
    // our SQL statements
    $con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Ben', 'Jefferson', '[email protected]')");
    $con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Johnny', 'Eriksen', '[email protected]')");
    $con->exec("INSERT INTO users (name, surname, e_mail) VALUES ('Mary', 'Julie', '[email protected]')");
    // commit the transaction
    $con->commit();
    echo "Created new records.";    
  } catch(PDOException $err) {
    // roll the transaction back if something fails
    $con->rollback();
    echo "Error message: " . $err->getMessage();
  }
  $con = null;  
?>

MySQL Insert Multiple Rows: Summary

  • MySQL statements allow you to insert multiple rows with one query. That will make your website faster.
  • There are different approaches to this insertion process that depend on your type of connection to MySQL. Whichever you choose, it's crucial to use proper MySQL insert syntax.