To insert multiple records into a MySQL database using MySQLi in PHP, you can use either individual INSERT queries within a loop or a single query with multiple values for efficiency.

1. Insert Multiple Records Using Multiple Queries in a Loop (Procedural Approach)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// Data to insert (this could be from an array or form input)$data = [    ['John Doe', 'john@example.com'],    ['Jane Smith', 'jane@example.com'],    ['Alice Johnson', 'alice@example.com']];// Loop through the data and insert each recordforeach ($data as $row) {    $name = $row[0];    $email = $row[1];    // SQL query to insert a record    $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";    // Execute the query    if (mysqli_query($conn, $sql)) {        echo "New record inserted: $name <br>";    } else {        echo "Error: " . mysqli_error($conn) . "<br>";    }}// Close the connectionmysqli_close($conn);?>

2. Insert Multiple Records Using a Single Query (Procedural Approach)

If you have many records to insert, it's more efficient to insert them in a single query rather than running multiple individual queries. Here's how you can do it:

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = mysqli_connect($host, $username, $password, $dbname);// Check connectionif (!$conn) {    die("Connection failed: " . mysqli_connect_error());}// Data to insert (this could be from an array or form input)$data = [    ['John Doe', 'john@example.com'],    ['Jane Smith', 'jane@example.com'],    ['Alice Johnson', 'alice@example.com']];// Build the query with multiple values$values = [];foreach ($data as $row) {    $name = mysqli_real_escape_string($conn, $row[0]);    $email = mysqli_real_escape_string($conn, $row[1]);    $values[] = "('$name', '$email')";}// Create the SQL query$sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $values);// Execute the queryif (mysqli_query($conn, $sql)) {    echo "New records inserted successfully.";} else {    echo "Error: " . mysqli_error($conn);}// Close the connectionmysqli_close($conn);?>

3. Insert Multiple Records Using Prepared Statements (Object-Oriented Approach)

To safely insert multiple records using prepared statements, you can loop through the data and execute the statement for each record.

Object-Oriented Approach (Using Prepared Statements)

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Data to insert (this could be from an array or form input)$data = [    ['John Doe', 'john@example.com'],    ['Jane Smith', 'jane@example.com'],    ['Alice Johnson', 'alice@example.com']];// Prepare the SQL query$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");// Bind parameters$stmt->bind_param("ss", $name, $email);// Loop through the data and execute the prepared statementforeach ($data as $row) {    $name = $row[0];    $email = $row[1];        if ($stmt->execute()) {        echo "New record inserted: $name <br>";    } else {        echo "Error: " . $stmt->error . "<br>";    }}// Close the statement and connection$stmt->close();$conn->close();?>

4. Insert Multiple Records Using a Single Prepared Statement (Object-Oriented Approach)

Using a single prepared statement with multiple records can improve performance, especially for larger datasets.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Data to insert (this could be from an array or form input)$data = [    ['John Doe', 'john@example.com'],    ['Jane Smith', 'jane@example.com'],    ['Alice Johnson', 'alice@example.com']];// Prepare the SQL query$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");// Loop through the data and bind parameters for each recordforeach ($data as $row) {    $name = $row[0];    $email = $row[1];        // Bind the parameters and execute the query    $stmt->bind_param("ss", $name, $email);        if ($stmt->execute()) {        echo "New record inserted: $name <br>";    } else {        echo "Error: " . $stmt->error . "<br>";    }}// Close the statement and connection$stmt->close();$conn->close();?>

Summary:

1. Multiple Queries in a Loop: You can use a loop to insert records individually. This is simple but can be inefficient for large datasets.

2. Single Query with Multiple Values: You can insert multiple rows in a single query by building the VALUES part of the SQL dynamically.

3. Prepared Statements: Always use prepared statements for better security, especially when inserting user-provided data. You can either execute each prepared statement individually or use a loop to execute a single prepared statement for each record.

This should help you efficiently insert multiple records into your MySQL database. Let me know if you need further details or assistance!