database - wpdb->insert multiple record at once

So after I have created the table 'settings-table' i want to add a couple of records to it.

This is how I want to do it, not the best way, so I need a better way to do this because I will add more records to the table.

EXAMPLE 1

 $wpdb->insert('settings-table', array('option_name'   => 'name-1', 
                                       'option_value'  => 'val-1', 
                                       'option_created'=> current_time('mysql'),
                                       'option_edit'   => current_time('mysql'),
                                       'option_user'   => 'user-1' 
                                       ));    
 $wpdb->insert('settings-table', array('option_name'   => 'name-2', 
                                       'option_value'  => 'val-2', 
                                       'option_created'=> current_time('mysql'),
                                       'option_edit'   => current_time('mysql'),
                                       'option_user'   => 'user-2' 
                                       ));
 $wpdb->insert('settings-table', array('option_name'   => 'name-1', 
                                       'option_value'  => 'val-3', 
                                       'option_created'=> current_time('mysql'),
                                       'option_edit'   => current_time('mysql'),
                                       'option_user'   => 'user-3' 

UPDATE

this works(any other better solutions are welcome)

$wpdb->query("INSERT INTO settings-table
            (`option_name`, `option_value`, `option_created`, `option_edit`, `option_user`)
            VALUES
            ('name-1', 'val-1', current_time('mysql'), current_time('mysql'), 'user-1'),
            ('name-2', 'val-2', current_time('mysql'), current_time('mysql'), 'user-2'),
            ('name-3', 'val-3',  current_time('mysql'), current_time('mysql'), 'user-3')")

So after I have created the table 'settings-table' i want to add a couple of records to it.

This is how I want to do it, not the best way, so I need a better way to do this because I will add more records to the table.

EXAMPLE 1

 $wpdb->insert('settings-table', array('option_name'   => 'name-1', 
                                       'option_value'  => 'val-1', 
                                       'option_created'=> current_time('mysql'),
                                       'option_edit'   => current_time('mysql'),
                                       'option_user'   => 'user-1' 
                                       ));    
 $wpdb->insert('settings-table', array('option_name'   => 'name-2', 
                                       'option_value'  => 'val-2', 
                                       'option_created'=> current_time('mysql'),
                                       'option_edit'   => current_time('mysql'),
                                       'option_user'   => 'user-2' 
                                       ));
 $wpdb->insert('settings-table', array('option_name'   => 'name-1', 
                                       'option_value'  => 'val-3', 
                                       'option_created'=> current_time('mysql'),
                                       'option_edit'   => current_time('mysql'),
                                       'option_user'   => 'user-3' 

UPDATE

this works(any other better solutions are welcome)

$wpdb->query("INSERT INTO settings-table
            (`option_name`, `option_value`, `option_created`, `option_edit`, `option_user`)
            VALUES
            ('name-1', 'val-1', current_time('mysql'), current_time('mysql'), 'user-1'),
            ('name-2', 'val-2', current_time('mysql'), current_time('mysql'), 'user-2'),
            ('name-3', 'val-3',  current_time('mysql'), current_time('mysql'), 'user-3')")

• database
• mysql
• wpdb

Share Improve this question edited Mar 3, 2014 at 16:09 user759235 asked Mar 3, 2014 at 14:15 user759235user759235 42633 gold badges77 silver badges1919 bronze badges 6

• 1 What are these settings for? You should consider using the options table (with all of the WP functionality that exists for it), or postmeta or usermeta (again, with all the WP functionality that already exists around them). Finally: if you want to use your own table, a loop would probably give you what you want, but we'd to have more context, understand what it is you're trying to accomplish "big picture" – random_user_name Commented Mar 3, 2014 at 15:04
• 1 The example above is just an example, I dont want to use the wp-options table. I cant give anymore info as there is none, it very basic, I just need to add multiple records inside the table. – user759235 Commented Mar 3, 2014 at 15:11
• 1 your use of prepare is incorrect, which would probably generate a warning. see the codex for the correct format. – Milo Commented Mar 3, 2014 at 15:44
• Yes I have seen it, I have changed it, but I still need a way to add multiple records to the table – user759235 Commented Mar 3, 2014 at 15:53
• 1 I have solved it by using the $wpdb->query(). – user759235 Commented Mar 3, 2014 at 16:06

 |  Show 1 more comment

4 Answers 4

Sorted by: Reset to default 10

$wpdb->query("INSERT INTO settings-table
            (option_name, option_value, option_created, option_edit, option_user)
            VALUES
            ('name-1', 'val-1', current_time('mysql'), current_time('mysql'), 'user-1'),
            ('name-2', 'val-2', current_time('mysql'), current_time('mysql'), 'user-2'),
            ('name-3', 'val-3',  current_time('mysql'), current_time('mysql'), 'user-3')")

In the query which you had posted, the column names shouldn't be in string.

$wpdb->insert function doesn't support multiple records to be inserted into single function call. You have to loop over your data and prepare the data to be inserted and then use $wpdb->insert into a loop and insert records one by one.

Instead, we can prepare insert query as follow and run a query only once.

So, if we want to insert 100 records, we need to run 100 insert queries if we want to use $wpdb->insert. And if we use following code, we need to run only 1 insert query.

Hope that helps.

function do_insert($place_holders, $values) {

    global $wpdb;

    $query           = "INSERT INTO settings-table (`option_name`, `option_value`, `option_created`, `option_edit`, `option_user`) VALUES ";
    $query           .= implode( ', ', $place_holders );
    $sql             = $wpdb->prepare( "$query ", $values );

    if ( $wpdb->query( $sql ) ) {
        return true;
    } else {
        return false;
    }

}

$data_to_be_inserted = array( array(
    'option_name'   => 'name-1', 
    'option_value'  => 'val-1', 
    'option_created'=> current_time('mysql'),
    'option_edit'   => current_time('mysql'),
    'option_user'   => 'user-1' 
),

array(
    'option_name'   => 'name-2', 
    'option_value'  => 'val-2', 
    'option_created'=> current_time('mysql'),
    'option_edit'   => current_time('mysql'),
    'option_user'   => 'user-2' 
),
array(
    'option_name'   => 'name-1', 
    'option_value'  => 'val-3', 
    'option_created'=> current_time('mysql'),
    'option_edit'   => current_time('mysql'),
    'option_user'   => 'user-3'
));

$values = $place_holders = array();

if(count($data_to_be_inserted) > 0) {
    foreach($data_to_be_inserted as $data) {
        array_push( $values, $data['option_name'], $data['option_value'], $data['option_created'], $data['option_edit'], $data['option_user']);
        $place_holders[] = "( %s, %s, %s, %s, %s)";
    }

    do_insert( $place_holders, $values );
}

I came up with this solution that extends the wpdb class so that it uses it's internal data processing functions:

<?php
class wpdbx extends wpdb {
  public function __construct() {
    parent::__construct(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
  }

  public function insert_multiple($table, $data, $format = null) {
    $this->insert_id = 0;

    $formats = array();
    $values = array();

    foreach ($data as $index => $row) {
      $row = $this->process_fields($table, $row, $format);
      $row_formats = array();

      if ($row === false || array_keys($data[$index]) !== array_keys($data[0])) {
        continue;
      }

      foreach($row as $col => $value) {
        if (is_null($value['value'])) {
          $row_formats[] = 'NULL';
        } else {
          $row_formats[] = $value['format'];
        }

        $values[] = $value['value'];
      }

      $formats[] = '(' . implode(', ', $row_formats) . ')';
    }

    $fields  = '`' . implode('`, `', array_keys($data[0])) . '`';
    $formats = implode(', ', $formats);
    $sql = "INSERT INTO `$table` ($fields) VALUES $formats";

    $this->check_current_query = false;
    return $this->query($this->prepare($sql, $values));
  }
}

global $wpdbx;
$wpdbx = new wpdbx();
?>

You can then use it like this:

<?php
global $wpdbx;
$results = $wpdbx->insert_multiple(
  'settings-table',
  array(
    array(
      'option_name'   => 'name-1', 
      'option_value'  => 'val-1', 
      'option_created'=> current_time('mysql'),
      'option_edit'   => current_time('mysql'),
      'option_user'   => 'user-1' 
    ),
    array(
      'option_name'   => 'name-2', 
      'option_value'  => 'val-2', 
      'option_created'=> current_time('mysql'),
      'option_edit'   => current_time('mysql'),
      'option_user'   => 'user-2' 
    ),
    array(
      'option_name'   => 'name-1', 
      'option_value'  => 'val-3', 
      'option_created'=> current_time('mysql'),
      'option_edit'   => current_time('mysql'),
      'option_user'   => 'user-3'
    )
  )
);
?>

This Will Also Work

        $nameArray = array("name-1", "name-2", "name-3");
        $valueArray = array("val-1", "val-2" , "val-3");
        $userArray = array("user-1" , "user-2", "user-3");
        $maxRow= count($nameArray); // can use count of any array
        $setting_values = array();
        $time = current_time('mysql');

        for($x=0;$x<$maxRow; $x++){
            $SettingContext = [
                'option_name' => "'" . $nameArray[$x] . "'",
                'option_value' => "'" . $valueArray[$x] . "'",
                'option_created' => "'" . $time . "'",
                'option_edit' => "'" . $time . "'",
                'option_user' => "'" . $userArray[$x] . "'"
            ];
            $setting_values[] = "(" . implode(',', $SettingContext) . ")";
        }
        $settingSubmit = implode(',', $setting_values);
        $setting_save = $wpdb->query("
                INSERT INTO setting
                (option_name, option_value, option_created,option_edit, option_user)
                VALUES " . $settingSubmit
        );