Simple WordPress bulk insert function

README.md

Simple WordPress Bulk Insert

A very simple function to perform bulk SQL inserts, since WPDB doesn't provide one directly. The aim is for simplicity - both in the function itself, and in using it - rather than being a massive beast which covers all possible incorrect usage scenarios. Provide it with clean, consistent data, and it should hopefully be able to do what you want without issue.

Notes:

• Provide a table name and an array of associative arrays of rows to insert
• Column names are pulled from the first row of data automatically
• Make sure you provide the same fields in each row (there's no protection for this)
• Data types (for WPDB placeholders) are auto-detected for each individual value (using is_numeric())
• There is no protection for exceeding maximum query size (i.e. MySQL's max_allowed_packet); you could pre-batch into smaller "safe" chunks if you need to handle this case - or just find a better way to insert such a large amount of data
• The function returns the number of affected rows (i.e. number of rows inserted)

wpdb_bulk_insert.php

<?php

// Bulk inserts records into a table using WPDB.  All rows must contain the same keys.
// Returns number of affected (inserted) rows.
function wpdb_bulk_insert($table, $rows) {
	global $wpdb;
	
	// Extract column list from first row of data
	$columns = array_keys($rows[0]);
	asort($columns);
	$columnList = '`' . implode('`, `', $columns) . '`';

	// Start building SQL, initialise data and placeholder arrays
	$sql = "INSERT INTO `$table` ($columnList) VALUES\n";
	$placeholders = array();
	$data = array();

	// Build placeholders for each row, and add values to data array
	foreach ($rows as $row) {
		ksort($row);
		$rowPlaceholders = array();

		foreach ($row as $key => $value) {
			$data[] = $value;
			$rowPlaceholders[] = is_numeric($value) ? '%d' : '%s';
		}

		$placeholders[] = '(' . implode(', ', $rowPlaceholders) . ')';
	}

	// Stitch all rows together
	$sql .= implode(",\n", $placeholders);

	// Run the query.  Returns number of affected rows.
	return $wpdb->query($wpdb->prepare($sql, $data));
}