1

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

         }
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);  
    }
}
  • I was also using DECIMAL in the sql query, & changed it to float . Would this have made a difference for point_one & point_two ? Thank-you. – mattnewbie Jan 22 '14 at 22:41
  • Sorry, i am not sure what your are trying to say. But if you mean your point_one & point_two value is different than what you have used for the insert statement, then the problem could be from the field type set for those two fields. – Shazzad Jan 22 '14 at 22:50
  • @shazzard. Sorry, let me clarify. In the sql statement that creates the custom table. For the point_one & point_two columns, should I use DECIMAL or FLOAT in the sql statement? Does it matter which one I use? Thank-you – mattnewbie Jan 23 '14 at 13:11
  • check this reference stackoverflow.com/q/5150274/712612 – Shazzad Jan 23 '14 at 14:53
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

  • Thank-you. It is possible to use $wpdb->insert , see below if you like – mattnewbie Jan 22 '14 at 22:36
  • @mattnewbie I say that you can't using one call, the answer you accept call $wpdb->insert two times with a foreach loop. But in that way you make 2 database queries, using my method you run only one query. If you do this task a lot of times or for a lot of rows, performance improvement can be very high. – gmazzap Jan 22 '14 at 22:47
  • Thanks, after re-reading it, I understand the difference! – mattnewbie Jan 23 '14 at 13:12
0

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

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.