PHP : MySQL Insert Multiple Records

To insert multiple records into a MySQL database table using PHP, you can use prepared statements to efficiently execute a single INSERT INTO query with multiple value sets. This method helps prevent SQL injection and improves performance compared to executing individual INSERT queries. Below are examples using both mysqli and PDO to insert multiple records into a users table.

Using mysqli:

Here's an example using mysqli to insert multiple rows into the users table:

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Data to be inserted
$users = [
    ["Alice", "alice@example.com"],
    ["Bob", "bob@example.com"],
    ["Charlie", "charlie@example.com"]
];

// SQL query to insert multiple rows
$sql = "INSERT INTO users (name, email) VALUES ";
$values = array();
foreach ($users as $user) {
    $values[] = "('" . $conn->real_escape_string($user[0]) . "', '" . $conn->real_escape_string($user[1]) . "')";
}

$sql .= implode(",", $values);

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

// Close connection
$conn->close();
 

Using  PDO :

Here's the same example using PDO :

// Database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

try {
    // Create connection
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Data to be inserted
    $users = [
        ["Alice", "alice@example.com"],
        ["Bob", "bob@example.com"],
        ["Charlie", "charlie@example.com"]
    ];

    // SQL query to insert multiple rows
    $sql = "INSERT INTO users (name, email) VALUES ";
    $values = array();
    foreach ($users as $user) {
        $values[] = "(:name_" . $user[0] . ", :email_" . $user[0] . ")";
    }

    $sql .= implode(",", $values);
    $stmt = $conn->prepare($sql);

    // Bind parameters
    foreach ($users as $user) {
        $stmt->bindParam(':name_' . $user[0], $user[0]);
        $stmt->bindParam(':email_' . $user[0], $user[1]);
    }

    // Execute the query
    $stmt->execute();

    echo "New records created successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
} finally {
    // Close connection
    $conn = null;
}
 

Explanation:

• Both examples use an array of arrays ( $users ) to store the data for multiple records.
• The SQL query is constructed dynamically to include multiple value sets for insertion.
• In the PDO example, placeholders ( :name_x , :email_x ) are used for each record to prevent SQL injection.
• The bindParam method is used to bind parameters to the prepared statement in PDO.

Important Notes:

• The INSERT INTO query with multiple value sets is more efficient than individual INSERT queries.
• Always sanitize user input and use prepared statements to prevent SQL injection attacks.
• Verify that the connection is successful before executing queries.

Running these scripts will insert multiple records into the users table in your MySQL database. Each record consists of a name and an email address. Adjust the $users array with the desired data to be inserted.