I would like to populate a custom table, with more than one row of data at one time. If I delete the second array, it will insert one row of data. It won't add both rows at the same time though. How do I insert both rows at the same time? My code:

function mp_install_name_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . "names";

    $wpdb->insert(
        $table_name,
        array(
            'id' => '1',
            'name' => 'matt',
            'age' => '20',
            'point_one' => '0.45',
            'point_two' => '0.22'     
        ),

         array(
            'id' => '2',
            'name' =>'james',
            'age' => '6',
            'point_one' => '0.27',
            'point_two' => '0.17'  
        )
    );  

         }

I would like to populate a custom table, with more than one row of data at one time. If I delete the second array, it will insert one row of data. It won't add both rows at the same time though. How do I insert both rows at the same time? My code:

function mp_install_name_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . "names";

    $wpdb->insert(
        $table_name,
        array(
            'id' => '1',
            'name' => 'matt',
            'age' => '20',
            'point_one' => '0.45',
            'point_two' => '0.22'     
        ),

         array(
            'id' => '2',
            'name' =>'james',
            'age' => '6',
            'point_one' => '0.27',
            'point_two' => '0.17'  
        )
    );  

         }

• table

Share Improve this question asked Jan 22, 2014 at 22:10 user28566user28566

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

You could use a foreach loop -

function mp_install_name_data() {
    global $wpdb;
    $table_name = $wpdb->prefix . "names";

    $rows = array(
        array(
            'id' => '1',
            'name' => 'matt',
            'age' => '20',
            'point_one' => '0.45',
            'point_two' => '0.22'     
        ),
        array(
            'id' => '2',
            'name' =>'james',
            'age' => '6',
            'point_one' => '0.27',
            'point_two' => '0.17'  
        )
    );

    foreach( $rows as $row )
    {
        $wpdb->insert( $table_name, $row);  
    }
}

You can't insert more than one row using one call to $wpdb->insert, however you can use $wpdb to perform a raw sql query with $wpdb->query.

For MySQL syntax see docs.

Remember to use $wpdb->prepare to escape data before inserting.

$wpdb->query( $wpdb->prepare(
  "INSERT INTO $table_name (id, name, age, point_one, point_two)
  VALUES (%d, %s, %d, %s, %s), (%d, %s, %d, %s, %s);"
), 1, 'matt', 20, '0.45', '0.22', 2, 'james', 6, '0.27', '0.17', );

Tip: if you setup the id column to be AUTOINCREMENT you don't need to pass the id, it will be added automatically

If you are concerned by performance, you should consider adding the START TRANSACTION and COMMIT instructions:

mysql_query('START TRANSACTION');
$res1 = mysql_query('query1');
$res2 = mysql_query('query2');
If ( $res1 && $res2 )
    mysql_query('COMMIT');