PHP MySQLi Multiple Insert - Inserting Multiple Records

Inserting Multiple Records at Once

Sometimes we want to insert multiple pieces of data into the database with a single script execution. Instead of calling mysqli_query() for each row, we use the mysqli_multi_query() function. We combine all SQL commands into a single string and separate them with a semicolon (;).

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqli_database";

$connection = mysqli_connect($server, $username, $password, $database);

if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
}

// Combine multiple INSERT commands into one string, separated by semicolons
$sql = "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Banana', 'Cabbage');";
$sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Orange', 'Carrot');";
$sql .= "INSERT INTO mysqli_table (fruit, vegetable) VALUES ('Plum', 'Chicory')";

if (mysqli_multi_query($connection, $sql)) {
    echo "All new records were added successfully.";
} else {
    echo "Error: " . mysqli_error($connection);
}

// Reading and outputting data
$sql = "SELECT table_id, fruit, vegetable FROM mysqli_table";
$result = mysqli_query($connection, $sql);

mysqli_close($connection);
?>

RESULT

The result will be displayed here...

How do you know all records are in the database? In the simulator, you can see the table with all three rows. In your local environment at home, use phpMyAdmin:

When you run the script, a success confirmation should be displayed.

Check the contents of the table in the phpMyAdmin tool.

Make sure that Banana, Pomaranča, and Sliva are in the table.