Database

Published 3 years ago.
Last updated

Using the WPDB Class

The wpdb class can be used to execute SQL query statements which return PHP objects
In order to work with the class you have to declare it as a global variable.

global $wpdb;

For OOP I’ve found it best to declare the wpdb object in a custom database class constructor and assign the class to a class property for ease of reuse.

public $wpdb;

public function __construct()
{
    global $wpdb;
    $this->wpdb = $wpdb;
}

Table Names

It is possible to assign prefixes to WordPress database table names. This is done when the WP app is installed. In order to properly work with the WPDB, make sure to append the prefix property from the wpdb object to all table names.

$table_name = $wpdb->prefix . 'posts';
$sql_query = "SELECT * FROM $table_name LIMIT 10";

WP also has built-in table names for working with default tables: $wpdb->posts, $wpdb->postmeta, and $wpdb->users.

Custom Queries

// Use the WP `wpdb` class to access the database.
global $wpdb;

// Query all the attachement posts with no parent post.
$query = "
    SELECT *
      FROM $wpdb->posts
     WHERE $wpdb->posts.post_type = 'attachment'
       AND $wpdb->posts.post_parent = '0'
";

// Get the query results.
$query_results = $wpdb->get_results($query);

// If rows exist, loop through them.
if (count($query_results) > 0) {
    foreach ($query_results as $index => $result_row) {
        echo $result_row . "\n";
    }
}

Query Helper Methods

WP has built-in helper methods for working with basic database queries: $wpdb->insert(), $wpdb->update(), and $wpdb->get_row().

Using an External Database

To connect to an external database, create a new instance of the wpdb class.

// Connect to an external database.
$extdb = new \wpdb($username, $password, $db_name, $db_host);

// test the new DB object.
if (is_object($extdb) && !isset($extdb->error)) {
    // DB object is good.
}
 

More WP DB Resources