How to insert array of data into mysql using php

How to insert array of data into mysql using php

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Batch Inserting Arrays into MySQL Using PHP: A Modern Approach

Inserting a multidimensional array into a database is a common task, but the method you choose drastically impacts performance and security. The code snippet you provided attempts to handle this by iterating through each element of the array and executing an individual INSERT statement for every row. While this logic correctly maps the data fields (R_ID, email, name) to their respective columns, it relies on two deprecated practices that must be addressed immediately: superfluous nesting in the foreach loop and the use of unparameterized queries with the legacy mysql_* extension.

The primary issue in your current implementation lies within the loop structure: foreach($EMailArr as $R_ID => $email => $name). This syntax is syntactically invalid in PHP 7 and later, and it was never intended for nested arrays. Your data structure is a simple indexed list of associative arrays (e.g., [0], [1]), not an associative array with keys R_ID, email, and name at the top level. To iterate correctly, you need to loop over the top-level indices and then access the values within each sub-array:

if (is_array($EMailArr)) {
    foreach ($EMailArr as $row) {
        // $row now contains ['R_ID' => 32, 'email' => 'a@a.com', 'name' => 'Bob']
        $r_id = $row['R_ID'];
        $email = $row['email'];
        $name = $row['name'];

        // ... query logic here
    }
}

However, even with the loop structure corrected, your current approach suffers from poor scalability. Executing 4 separate queries to insert 4 rows is inefficient. Furthermore, concatenating variables directly into the SQL string—"values ('$R_ID', '$email', '$name')" —exposes your application to SQL injection vulnerabilities. An attacker could manipulate the $email or $name fields to execute arbitrary commands on your server.

To mitigate this, you must utilize prepared statements. In modern PHP development, the mysqli or PDO extensions are preferred over the deprecated mysql_* functions. Using mysqli with prepared statements ensures that data is safely escaped before being sent to the database engine:

$mysqli = new mysqli("localhost", "user", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$stmt = $mysqli->prepare("INSERT INTO email_list (R_ID, EMAIL, NAME) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $r_id, $email, $name); // 's' for string, 'i' for integer

foreach ($EMailArr as $row) {
    $r_id = $row['R_ID'];
    $email = $row['email'];
    $name = $row['name'];

    $stmt->execute();
}

$stmt->close();
$mysqli->close();

For even greater efficiency, especially when dealing with large datasets, you can execute a single batch insert using the INSERT ... VALUES clause repeated within one query. This reduces network latency and server overhead significantly:

$values = [];
foreach ($EMailArr as $row) {
    $values[] = "({$row['R_ID']}, '{$row['email']}', '{$row['name']}')";
}

$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) VALUES " . implode(", ", $values);
$stmt = $mysqli->prepare($sql); // Note: Binding is handled differently for batch inserts
// Execute logic depends on specific MySQL version and driver nuances

While batch inserts are faster, they require careful handling of data length limits and transaction management. If any single row fails validation, the entire batch might need to be rolled back unless you handle errors granularly. Always verify that your database engine supports the specific syntax used for batch operations in your target version. For complex applications where query optimization is critical, consider using ORM frameworks like Laravel, which abstract these details and provide safer, more maintainable patterns for data persistence.

Our network