Understanding WordPress Database object $wpdb

File responsible for handling WordPress database related task is wp-includes/wp-db.php

How to create your own database class or extend the wpdb?

The file: wp-includes/load.php has a function require_wp_db() that will allow you to override the $wpdb object.

Code in the above screenshot says: load the file: wp-content/db.php, if available. This helps you to override the $wpdb object by instantiating your newly created class.

// Step 1: create a file: wp-content/db.php
// example code: db.php
<?php

if( ! defined( 'ABSPATH' ) ) exit;

class NewWpdb extends wpdb {
    public function __construct( $dbuser, $dbpassword, $dbname, $dbhost ){
        parent::__construct( $dbuser, $dbpassword, $dbname, $dbhost );
    }
    public function new_method(){
        return 'hi from new method';
    }
}

// set the global variable $wpdb like:
$GLOBALS['wpdb'] = new NewWpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

// whichever way you prefer but just use one approach to create $wpdb object.
// $wpdb = new NewWpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

// Now you can call the new method in any template or function.
// example: put the following code in active (child) theme's functions.php
add_action( 'wp_footer', 'test' );
function test(){
    global $wpdb;
    $new_method = $wpdb->new_method();
    var_dump( $new_method ); // hi from new method.
}

Note: The initialization of object $wpdb at the end of your class declaration is very important. That is:

$wpdb = new NewWpdb( DB_USER, DB_PASSWORD, DB_NAME, DB_HOST );

Get table prefix:

global $wpdb;
$table_prefix = $wpdb->prefix;
// for multi site:
$wpdb->get_blog_prefix();

To list all database tables:

global $wpdb;
$db_tables = $wpdb->tables();
var_dump( $db_tables );
// output:
Array
(
    [users] => wp_users
    [usermeta] => wp_usermeta
    [posts] => wp_posts
    [comments] => wp_comments
    [links] => wp_links
    [options] => wp_options
    [postmeta] => wp_postmeta
    [terms] => wp_terms
    [term_taxonomy] => wp_term_taxonomy
    [term_relationships] => wp_term_relationships
    [termmeta] => wp_termmeta
    [commentmeta] => wp_commentmeta
)

CRUD operations with database using $wpdb

query() vs other available methods: query() can be used to perform any SQL operation but its good practice to use the appropriate methods for the particular operation (crud) whenever possible.

$operation = $wpdb->query( $wpdb->prepare( $sql, $value ) );
// the return value depends on the operation.

Note: All queries should pass through the prepare() function before being executed. The following example code should go in any function definition.

global $wpdb;

// set table name
$table_name = $wpdb->prefix . 'posts';
// write SQL query
$sql_query = "SELECT * FROM $table_name WHERE id = %d";
// use WHERE post_status = %s to get multiple records
// prepare the statement
$sql_prepare = $wpdb->prepare( $sql_query, 123 );
// if you used WHERE post_status = %s then pass 'publish'.
// at this point your SQL query is safe and ready to be executed.

$results = $wpdb->get_results( $sql_prepare, ARRAY_A );

CREATE

insert($table, $data, $format)

$wpdb->insert(
    $wpdb->prefix . 'postmeta',
    [
        'post_id' => 123,
        'meta_key' => 'meta_key',
        'meta_value' => 'Meta Value!'
    ],
    [
        '%d',
        '%s',
        '%s'
    ]
);

READ

Performing SELECT operation can be done by using one of the following methods:

get_results(): retrieve multiple rows

$results = $wpdb->get_results( $sql_prepare, ARRAY_A );

get_row(): retrieve single row

$result = $wpdb->get_row( $wpdb->prepare( $sql_query, $val ), ARRAY_A );

get_var(): retrieve single value

$table_name = $wpdb->prefix . 'comments';
$query = "SELECT COUNT(*) FROM $table_name WHERE comment_approved = %d";
$count = $wpdb->get_var( $wpdb->prepare( $query, 1 ) ); // total comment nums.

get_col()

UPDATE

update($table, $data, $where, $format, $where_format)

$wpdb->update(
    $wpdb->prefix . 'postmeta',
    [
        'meta_value' => 'Updated Meta Value!'
    ],
    [
        'post_id' => 123,
        'meta_key' => 'meta_key'
    ],
    [
        '%s'
    ],
    [
        '%d',
        '%s'
    ]
);

DELETE

delete($table, $where, $where_format)

$wpdb->delete(
    $wpdb->prefix . 'postmeta',
    [
        'post_id' => 123,
        'meta_key' => 'meta_key'
    ],
    [
        '%d',
        '%s'
    ]
);

Prepared statements

An example of prepared statement.

// create a template
$sql = "SELECT col1, col2 
        FROM table_name
        WHERE condition1 = %s OR
              condition2 = %d OR
              condition3 = %f";
// call the template and supply the values when you need to query the database.

$prepare_statement = $wpdb->prepare( $sql, 'str_val', 123, 19.50 );
//Note: the order of supplied value is similar to order of placeholder definitions i.e., %s = 'str_val', %d = 123, %f = 19.50

A prepared statement is like building a template for your SQL query.
You can define placeholders in your SQL query which will be replaced when executing the query.
- %s : will accept string values.
- %d: will accept integer values.
- %f: will accept float values.
- you don’t need to wrap placeholders in quotes (single or double).
- The order of placeholders is very crucial and you must supply the values of placeholders in the same order as their definition order.
Benefits of prepared statements:
- Reusability: define the query structure once and call it as many times as you need to.
- Fast: Database parses the SQL query each time you execute it but with prepared statements, database need to parse the query only once and later when executing the query, only placeholders are replaced.
- Prevents SQL injection attacks: Since you define what to expect in a SQL query, any malicious input is converted to equivalent datatype thus preventing the execution of SQL query in inputs. In other words the input will be treated as the datatype defined in placeholders instead of SQL queries (if any).

Reference: WordPress wpdb Class Referenc e