Skip to content

Instantly share code, notes, and snippets.

@pauln
Created May 5, 2017 00:11
  • Star 7 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
Simple WordPress bulk insert function

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)
<?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));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment