PHP MySQLi insert multiple rows

PHP Tutorial » PHP MySQLi insert multiple rows

To insert multiple rows data into a MySQL table, SQL statements must be executed with the mysqli_multi_query() function.

Here is a SQL syntax of INSERT INTO command to add new records to MySQL table:

Syntax:

In this chapter as well as in the following chapters we will show you three methods of working with PHP and MySQL Insert Multiple Records:

• MySQLi (object oriented)
• MySQLi (procedural)
• PDO

Before accessing data from the MySQL database, we must be able to connect to the server:

<?php

$server_name = "localhost";

$user_name = "my_username";

$password = "my_password";

$db_name = "my_DB";



// Create connection

$link = new mysqli($server_name, $user_name,

$password, $db_name);

// Check connection

if ($link->connect_error) {

die("Connection failed: " . $link->connect_error);

}



$sql = "INSERT INTO Members (name, user_name, email)

VALUES ('Johny', 'johnyboss', 'johnyboss2@agernic.com')";

$sql .= "INSERT INTO Members (name, user_name, email)

VALUES ('Johny1', 'johnyboss1', 'johnyboss21@agernic.com')"; 

$sql .= "INSERT INTO Members (name, user_name, email)

VALUES ('Johny2', 'johnyboss2', 'johnyboss22@agernic.com')"; 



if ($link->multi_query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "<br />" . $link->error;

}

$link->close();

?>

Related subjects:
MySQLi select data example PHP connect to MySQL example MySQLi insert query

Tags: php mysqli insert multiple: rows at once, php mysqli multiple inserts, insert into multiple tables, prepare statements insert multiple rows, prepare multiple insert

Attempt MySQL server connection. Assuming you are running MySQL server with default setting ("localhost", "root", "your_password", "DB_name")

<?php

  $server_name = "localhost";

  $user_name = "my_username";

  $password = "my_password";

$db_name = "my_DB";
// Create connection

  $link = mysqli_connect($server_name, $user_name,

  $password, $db_name);

  // Check connection

  if (!$link) {

  die("Connection failed: " . mysqli_connect_error());

  }
$sql = "INSERT INTO Members (name, user_name, email)

  VALUES ('Johny', 'johnyboss', 'johnyboss2@agernic.com')";

  $sql .= "INSERT INTO Members (name, user_name, email)

  VALUES ('Johny1', 'johnyboss1', 'johnyboss21@agernic.com')"; 

  $sql .= "INSERT INTO Members (name, user_name, email)

  VALUES ('Johny2', 'johnyboss2', 'johnyboss22@agernic.com')"; 
if (mysqli_multi_query($link, $sql)) {

  echo "New records created successfully";

  } else {

  echo "Error: " . $sql . "<br>" . mysqli_error($link);

  }
mysqli_close($link);

  ?>
      

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
$link = new PDO("mysql:host=$server_name;dbname=$db_name",
$user_name, $password);

$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// begin transaction
$link->beginTransaction();
// SQL statements
$link->exec("INSERT INTO Members (name, user_name, email)
VALUES ('Johny', 'johnyboss', 'johnyboss2@agernic.com')");
$link->exec("INSERT INTO Members (name, user_name, email)
VALUES ('Johny1', 'johnyboss1', 'johnyboss21@agernic.com')");
$link->exec("INSERT INTO Members (name, user_name, email)
VALUES ('Johny2', 'johnyboss2', 'johnyboss22@agernic.com')");

// commit transaction
$link->commit();
echo "New records created successfully";
} catch(PDOException $e) {
// roll back the transaction if something failed
$link->rollback();
echo "Error: " . $e->getMessage();
}

$link = null;
?>