How to insert more than one row of data into a table at one time

1 Views

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

         }
Post Views: 1

Related posts

3 comments

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

  2. 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

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

Comments are closed.