1

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?

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;

}

2 Answers 2

2

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.

1
  • Yeah, I was fearing that would be the only way. I'm a little surprised WP don't have a built in method for doing it, as it can be a real time saver. Thanks.
    – David Gard
    Jul 31, 2013 at 15:24
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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