Make one query for adding entries to database

I have a situation where I need several entries to be added to a database table (sometime a few hundred), but using $wpdb->insert() (and $wpdb->update() where edits are necessary) is running these inserts one at a time, which is very inefficient.

Is there a $wpdb method available that will allow me to create the query, by looping through my query, and then execute it once I’m out of that loop?

Read More

Here is the code I am currently using to add new data. Any suggestions for improvements would be appreciated –

function do_add_invitees_in_database(){

    /** Make sure there are invitees to add */
    if(empty($this->invitees['new'])) :
        return $_POST['add_successfull'] = true;
    endif;

    global $wpdb;

    /** Get the creator ID */
    $creator_id = get_current_user_id();

    /** Make the invitee creation date */
    $date_added = date('Y-m-d H:i:s', time());  

    /** Insert the new invitees in to the 'event_invitees' database table */
    foreach($this->invitees['new'] as $invitee) :

        $row = array(
            'invitee_created_by'        => $creator_id,
            'invitee_created_date'      => $date_added,
            'invitee_last_edited_by'    => '0',
            'invitee_last_edited_date'  => '0000-00-00 00:00:00',
            'event_id'                  => $_POST['event_id'],
            'email'                     => $invitee['email'],
            'first_name'                => $invitee['first_name'],
            'surname'                   => $invitee['surname'],
            'custom'                    => $invitee['custom'],
            'invited'                   => 0
        );
        $wpdb->insert($wpdb->event_invitees, $row);

        /** Debug the query run by $wpdb->insert() */
        $this->debug($row);

    endforeach;

    /** If there were no errors, mark the invitee additions as successfull */
    return $_POST['add_successfull'] = (!$this->error_found) ? true : false;

}

Related posts

2 comments

  1. Is there a $wpdb method available that will allow me to create the
    query, by looping through my query, and then execute it once I’m out
    of that loop?

    If you and your loop can construct the proper SQL, then use $wpdb->query. You can run any query you want with that, so whatever version of INSERT INTO that you construct should work.

  2. After some research, and reluctant acceptance that I’d have to code this myself, I have come up with the code available in this Pastebin.

    I’ve done some benchmarking, and compared to the original method used in my question, this is very fast. One 100 inserts, here are the results –

    Old job complete in : 0.045570850372314 seconds
    New job complete in : 0.0089929103851318 seconds

    As you can see, it’s just over 5 times quicker for 100 new rows. My testing also showed that the overall multiplier of time taken increased exponentially.

    Hopefully WP will build a similar method in to $wpdb soon!

Comments are closed.