☰

insert_batch()

public function insert_batch(array $records, string $table): int

Description

Inserts multiple records into a specified database table using a single batch INSERT statement for improved performance. This method is optimized for inserting large datasets efficiently. It validates table existence, constructs a parameterized batch query, and returns the number of successfully inserted records.

Parameters

Parameter Type Description Default Required
records array An array containing associative arrays, where each inner array represents a record to be inserted. All records must have identical column structures. - Yes
table string The name of the database table to insert records into. - Yes

Return Value

Type Description
int The number of records successfully inserted. Returns 0 if the records array is empty.

Exceptions

  • RuntimeException - If the specified table does not exist in the database.
  • Exception - If an error occurs during the database operation.
  • PDOException - If there's a database constraint violation or connection error.

Security Warning: This method should only be used in controlled environments and not exposed to untrusted users or website visitors to prevent potential security vulnerabilities.

Example #1

The code sample below demonstrates how to insert multiple user records into the 'users' table in a single batch operation.

PHP
$users = [
    [
        'name' => 'John Doe',
        'email' => '[email protected]',
        'status' => 'active'
    ],
    [
        'name' => 'Jane Smith',
        'email' => '[email protected]',
        'status' => 'active'
    ],
    [
        'name' => 'Mike Johnson',
        'email' => '[email protected]',
        'status' => 'pending'
    ]
];

$inserted_count = $this->db->insert_batch($users, 'users');
echo "Successfully inserted " . $inserted_count . " user records.";

Example #2

The code sample below demonstrates how to insert product inventory records into the 'inventory_log' table.

PHP
$inventory_updates = [
    ['product_id' => 101, 'quantity' => 50, 'action' => 'restock', 'date' => '2024-01-15'],
    ['product_id' => 102, 'quantity' => 25, 'action' => 'restock', 'date' => '2024-01-15'],
    ['product_id' => 103, 'quantity' => -10, 'action' => 'sale', 'date' => '2024-01-15'],
    ['product_id' => 104, 'quantity' => 30, 'action' => 'restock', 'date' => '2024-01-15']
];

$count = $this->db->insert_batch($inventory_updates, 'inventory_log');
echo "Logged " . $count . " inventory transactions.";

Example #3

The code sample below demonstrates handling an empty records array and the importance of consistent record structure.

PHP
// Empty array returns 0 immediately
$empty_result = $this->db->insert_batch([], 'logs');
echo "Inserted: " . $empty_result . " records"; // Output: Inserted: 0 records

// All records MUST have the same column structure
$valid_records = [
    ['title' => 'Post 1', 'content' => 'Content 1', 'author_id' => 1],
    ['title' => 'Post 2', 'content' => 'Content 2', 'author_id' => 1]
    // Missing or extra columns in any record will cause an error
];

Important Notes

  • Parameter order is insert_batch(array $records, string $table) - records first, then table.
  • Returns 0 immediately if the $records array is empty.
  • All records in the array must have identical column structures (same keys in each associative array).
  • Column names are extracted from the first record in the array.
  • Uses positional parameters (?) instead of named parameters for batch efficiency.
  • Constructs a single SQL statement with multiple VALUE clauses for optimal performance.
  • The method validates that the table exists before executing the query.
  • Not suitable for user-generated input - use only with validated, controlled data.
  • If debug mode is enabled, the generated batch SQL query will be displayed.