wpdb->insert multiple record at once

4 comments

1. $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.

2. $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 );
   }
   

3. 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'
       )
     )
   );
   ?>
   

4. 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
           );
   

Comments are closed.