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')")
  • 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" – cale_b Mar 3 '14 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 Mar 3 '14 at 15:11
  • 1
    your use of prepare is incorrect, which would probably generate a warning. see the codex for the correct format. – Milo Mar 3 '14 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 Mar 3 '14 at 15:53
  • 1
    I have solved it by using the $wpdb->query(). – user759235 Mar 3 '14 at 16:06
5
$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.

1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.