Is there any way to accomplish the following in Wordpress with $wpdb->insert or
$wpdb->query($wpdb->prepare)):
INSERT into TABLE (column1, column2, column3)
VALUES
('value1', 'value2', 'value3'),
('otherval1', 'otherval2', 'otherval3'),
('anotherval1', 'anotherval2', 'anotherval3')
...etc
OK, I figured it out!
Setup arrays for Actual Values, and Placeholders
$values = array();
$place_holders = array();
the initial Query:
$query = "INSERT INTO orders (order_id, product_id, quantity) VALUES ";
Then loop through the the values you're looking to add, and insert them in the appropriate arrays:
foreach ( $_POST as $key => $value ) {
array_push( $values, $value, $order_id );
$place_holders[] = "('%d', '%d')" /* In my case, i know they will always be integers */
}
Then add these bits to the initial query:
$query .= implode( ', ', $place_holders );
$wpdb->query( $wpdb->prepare( "$query ", $values ) );
You can also use this way to build the query:
$values = array();
// We're preparing each DB item on it's own. Makes the code cleaner.
foreach ( $items as $key => $value ) {
$values[] = $wpdb->prepare( "(%d,%d)", $key, $value );
}
$query = "INSERT INTO orders (order_id, product_id, quantity) VALUES ";
$query .= implode( ",\n", $values );
I have came across with this problem and decided to build a more improved function by using accepted answer too:
/**
* A method for inserting multiple rows into the specified table
*
* Usage Example:
*
* $insert_arrays = array();
* foreach($assets as $asset) {
*
* $insert_arrays[] = array(
* 'type' => "multiple_row_insert",
* 'status' => 1,
* 'name'=>$asset,
* 'added_date' => current_time( 'mysql' ),
* 'last_update' => current_time( 'mysql' ));
*
* }
*
* wp_insert_rows($insert_arrays);
*
*
* @param array $row_arrays
* @param string $wp_table_name
* @return false|int
*
* @author Ugur Mirza ZEYREK
* @source http://stackoverflow.com/a/12374838/1194797
*/
function wp_insert_rows($row_arrays = array(), $wp_table_name) {
global $wpdb;
$wp_table_name = esc_sql($wp_table_name);
// Setup arrays for Actual Values, and Placeholders
$values = array();
$place_holders = array();
$query = "";
$query_columns = "";
$query .= "INSERT INTO {$wp_table_name} (";
foreach($row_arrays as $count => $row_array)
{
foreach($row_array as $key => $value) {
if($count == 0) {
if($query_columns) {
$query_columns .= ",".$key."";
} else {
$query_columns .= "".$key."";
}
}
$values[] = $value;
if(is_numeric($value)) {
if(isset($place_holders[$count])) {
$place_holders[$count] .= ", '%d'";
} else {
$place_holders[$count] .= "( '%d'";
}
} else {
if(isset($place_holders[$count])) {
$place_holders[$count] .= ", '%s'";
} else {
$place_holders[$count] .= "( '%s'";
}
}
}
// mind closing the GAP
$place_holders[$count] .= ")";
}
$query .= " $query_columns ) VALUES ";
$query .= implode(', ', $place_holders);
if($wpdb->query($wpdb->prepare($query, $values))){
return true;
} else {
return false;
}
}
$row_arrays argument is an associative array, because if($count == 0) assumes it is indexed with numbers starting from 0. Not very good, to rely on assumptions, that are not even documented. Would be better to use a different method to differentiate the first row.
wp_insert_rows([ 'row1' => ['prop1', 'prop2'], 'row2' => ['propA', 'propB'] ] , 'my_table') or this would fail: wp_insert_rows([ 1 => ['prop1', 'prop2'], 2 => ['propA', 'propB'] ] , 'my_table') while this would succeed: wp_insert_rows([ ['prop1', 'prop2'], ['propA', 'propB'] ] , 'my_table') Not a big issue, I'm just surprised by the $count == 0 condition, when it is not said that the input array must not be associative (i.e. must not use custom keys). I'd never do that, as it means the function relies on an assumption, which is not enforced.In addition to inserting multiple rows using $wpdb, if you ever need to update existing rows following snippet should be helpful. This is updated snippet of what @philipp provided above.
$values = array();
// We're preparing each DB item on it's own. Makes the code cleaner.
foreach ( $items as $key => $value ) {
$values[] = $wpdb->prepare( "(%d,%d)", $key, $value );
}
$values = implode( ",\n", $values );
$query = "INSERT INTO orders (order_id, product_id, quantity) VALUES {$values} ON DUPLICATE KEY UPDATE `quantity` = VALUES(quantity)";
array_map instead of the loop. (I don't know if it is MySQL specific, but note that the VALUES() syntax to refer to the would-be new values is deprecated as of MySQL 8.0.20, but as of 8.0.19 an alias can be used instead.)This is a bit late, but you could also do it like this.
global $wpdb;
$table_name = $wpdb->prefix . 'your_table';
foreach ($your_array as $key => $value) {
$result = $wpdb->insert(
$table_name,
array(
'colname_1' => $value[0],
'colname_2' => $value[1],
'colname_3' => $value[2],
)
);
}
if (!$result) {
print 'There was a error';
}
not very nice, but if you know what you are doing:
require_once('wp-load.php');
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
@mysql_select_db(DB_NAME) or die();
mysql_query("INSERT into TABLE ('column1', 'column2', 'column3') VALUES
('value1', 'value2', 'value3'),
('otherval1', 'otherval2', 'otherval3'),
('anotherval1', 'anotherval2', 'anotherval3)");
wpdbclass and didn't find any methods that insert multiple rows, so you'll have to do it using one of the two methods above.