How to Customize the wpdb Class and Add New Methods

The WordPress database abstraction layer, more commonly know as wpdb is a class based on ezSQL responsible for interacting with the database. The wpdb class is also a drop-in – a piece of core WP code that can easily be replaced by another piece of code with similar functionality (an up-to-date list of drop-ins is available in WP Code Reference).

This is where a big misconception comes into play. People perceive “replaced” as in “write my own, complete database class”. Fortunately, you don’t have to write the whole database class. You can take the existing class and add, remove or modify only the functions you need. And that’s what we’re going to do.

Why modify the wpdb class?

Surely, wpdb has everything you need!? The whole WP is built on top of it. Technically that’s correct. By using the $wpdb->query() method you can execute any SQL command. But with that logic applied we would never have or need methods like $wpdb->insert() or $wpdb->get_var(). But we do, because they save time, reduce bugs and keep bad queries away. So, what functions are missing? That depends on the project you’re working on. We needed support for these two queries:

INSERT IGNORE INTO tbl_name ...
INSERT INTO tbl_name ... ON DUPLICATE KEY UPDATE ...

Step #1 – Create the drop-in

The process of creating a DB drop-in is simple, and the end-result blends seamlessly into WP.
Create a file named db.php and place it in the /wp-content/ folder. Give the file a standard plugin header and create a class that extends wpdb. Something like this:

<?php
/*
  Plugin Name: Extended wpdb
  Description: A few extra functions for wpdb
  Version: 1.0
  Author: WebFactory Ltd
*/

class wpdb_extended extends wpdb {

  public function __construct(){
    parent::__construct( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );
  }

  public function test() {
    echo 'Extended wpdb is running.';
  }
}


global $wpdb;
$wpdb = new wpdb_extended();

The header is pretty self-explanatory. The construct method makes sure we create a new DB connection once an object is constructed and it uses the original wpdb constructor for that. Those DB constants are defined in wp-config.php. As we said in the beginning – we’re just adding some functions and reusing everything else. The second method test() is purely to see if everything works correctly.

The last two lines are crucial. Since this is a custom class, WP will not create the $wpdb object for us automatically. We have to do it ourselves. Remember to use the global keyword because otherwise, you’ll create a local variable.

Step #2 – Test before digging deeper

Open your WP Dashboard and head over to Plugins. Besides the usual All, Active and Inactive (plugins) tabs you’ll see a Drop-ins tab and in it, by some magic, our custom database class.

Since the admin is working properly, we know we did a good job. Our new, custom, extended class is in, and nothing changed. Great! But, just for good measure put these three lines of code into theme’s functions.php (or somewhere inside a plugin where it’ll get run immediately):

global $wpdb;
$wpdb->test();
die;

Run any admin or front-end page and what do you see? “Extended wpdb is running.” Perfect. Now for our real-world functions.

Step #3 – Create, modify or replace functions

The INSERT IGNORE query seems pretty easy to do. We already have $wpdb->insert() so we’re just missing one single word. Unfortunately, due to the lack of filters in the right places, it’s not going to be two lines of code.

If you open wp-db.php and head down to the insert() method on line #2100, you’ll see that it uses the _insert_replace_helper() method from line #2151. And that function doesn’t have a single filter or action. We can’t wrap the function either because it immediately returns the query result and not the query itself which we could modify. Bummer. We’ll have to override both insert() and _insert_replace_helper() methods. We’ll add an extra parameter – $ignore. A boolean with the default value of false.

public function insert( $table, $data, $format = null, $ignore = false ) {
  return $this->_insert_replace_helper( $table, $data, $format, 'INSERT', $ignore = false );
}

In any other circumstance, a function that has five parameters would be a good candidate to turn them into an associative array. But we want backward compatibility, so we’re not doing that. Changes to the other function are minor too:

function _insert_replace_helper( $table, $data, $format = null, $type = 'INSERT', $ignore = false ) {
  $this->insert_id = 0;

  if ( ! in_array( strtoupper( $type ), array( 'REPLACE', 'INSERT' ) ) ) {
    return false;
  }

  $data = $this->process_fields( $table, $data, $format );
  if ( false === $data ) {
    return false;
  }

  $formats = $values = array();
  foreach ( $data as $value ) {
    if ( is_null( $value['value'] ) ) {
      $formats[] = 'NULL';
      continue;
    }

    $formats[] = $value['format'];
    $values[]  = $value['value'];
  }

  $fields  = '`' . implode( '`, `', array_keys( $data ) ) . '`';
  $formats = implode( ', ', $formats );
  
  // modification for IGNORE keyword
  if (true == $ignore && 'INSERT' == $type) {
    $type = 'INSERT IGNORE';
  }

  $sql = "$type INTO `$table` ($fields) VALUES ($formats)";

  $this->check_current_query = false;
  return $this->query( $this->prepare( $sql, $values ) );
}

Why don’t developers do this more often?

If you need a custom query only a few times you can easily use $wpdb->query(). There’s no need to replace the default class. You can also write a custom class that extends the default one but doesn’t override the wpdb object. In that case you’d use $mycustomclass->method() but that’s not the end of the world.

Besides the obvious coolness factor 🙂 extending the default wpdb class is something that’s meant to be done in WP and is not hackish, so feel free to play with it. Do keep two things in mind:

• when you’re sending your project to someone, don’t forget to include the db.php file; it’s not going to be in any theme or plugin folder
• keep an eye on changes in wp-db.php as they might affect your custom class (although that’s highly unlikely)