❮ Func Date Strptime Func Mysqli Sqlstate ❯

PHP `MySQL Insert Multiple Records`

Inserting Multiple Records into MySQL Using MySQLi and PDO

The mysqli_multi_query() function can be used to execute multiple SQL statements.

The following example adds three new records to the "MyGuests" table:

Example (MySQLi - Object-Oriented)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";

if ($conn->multi_query($sql) === TRUE) {
    echo "New records inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

| | Note that each SQL statement must be separated by a semicolon. | | --- | --- |

Example (MySQLi - Procedural)

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

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', '[email protected]');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', '[email protected]')";

if (mysqli_multi_query($conn, $sql)) {
    echo "New records inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example (PDO)

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

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Begin transaction
    $conn->beginTransaction();
    // SQL statements

$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('John', 'Doe', '[email protected]')");
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('Mary', 'Moe', '[email protected]')");
$conn->exec("INSERT INTO MyGuests (firstname, lastname, email) 
    VALUES ('Julie', 'Dooley', '[email protected]')");

// Commit the transaction
$conn->commit();
echo "New records inserted successfully";
}
catch(PDOException $e)
{
    // Rollback if execution fails
    $conn->rollback();
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

Using Prepared Statements

The mysqli extension provides a second way to insert statements.

We can prepare statements and bind parameters.

The mysql extension can send statements or queries without data to the MySQL database. You can associate or "bind" variables to columns.

Example (MySQLi Using Prepared Statements)

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    $sql = "INSERT INTO MyGuests(firstname, lastname, email)  VALUES(?, ?, ?)";

    // Initialize statement object for mysqli_stmt_prepare()
    $stmt = mysqli_stmt_init($conn);

    // Prepare statement
    if (mysqli_stmt_prepare($stmt, $sql)) {
        // Bind parameters
        mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

        // Set parameters and execute
        $firstname = 'John';
        $lastname = 'Doe';
        $email = '[email protected]';
        mysqli_stmt_execute($stmt);

        $firstname = 'Mary';
        $lastname = 'Moe';
        $email = '[email protected]';
        mysqli_stmt_execute($stmt);

        $firstname = 'Julie';
        $lastname = 'Dooley';
        $email = '[email protected]';
        mysqli_stmt_execute($stmt);
    }
}
?>

We can see in the above example that modularity is used to handle issues. We can achieve simpler reading and management by creating code blocks.

Note the binding of parameters. Let's look at the code in mysqli_stmt_bind_param():

mysqli_stmt_bind_param($stmt, 'sss', $firstname, $lastname, $email);

This function binds the parameters to the query and passes them to the database. The second parameter is "sss". The following list shows the types of parameters. The s character tells MySQL that the parameter is a string.

There are four types of parameters:

i - integer
d - double
s - string
b - binary blob storing objects

Each parameter must be specified by type to ensure data security. By judging the type, the risk brought by SQL injection vulnerabilities can be reduced.