Writing Custom Queries in WordPress

With custom queries you can make any data reading or manipulation you want. This opens up a world of new possibilities.

Why Use Custom Queries?

The basic functionality in WordPress is fine most of the time, but what would you do if you have some specific needs that aren’t already addressed by existing plugins? Are you writing your own plugin? Then you should learn how you can use SQL queries in WordPress! The official references can be found in the WordPress Codex (Custom Queries and the WPDB class).

The wpdb Class

The wpdb global WordPress class is the key for using custom queries. In fact, when it comes to executing queries, almost every WordPress API, which needs to fetch data from the database, ends up using this class in the background. To use this class, you need to use the global $wpdb variable, which is an instantiation of the wpdb class.

In this section, we’ll discuss a couple of important methods of the wpdb class. Using these methods, you can perform all types of custom queries in your WordPress project.

The query method

The query method is used to execute a query using the active database connection. The first argument of the query method is a valid SQL statement. The return value of this method is an integer corresponding to the number of rows affected/selected or false when there is an error.

Usually, you want to use this method when you want to retrieve a count of records. Let’s have a look at the following example to understand how you can use this method.

global $wpdb;
$query = "SELECT COUNT(apple) FROM $wpdb->fruits";
$result = $wpdb->query($query);

Firstly, we’ve declared the $wpdb global variable so that we can use the wpdb class. Next, we’ve prepared the SQL statement and passed it as the first argument of the query method. The query method would execute the query and returns the number of selected or affected rows.

The get_results Method

The get_results method returns the entire query result, which is an array. Each element of an array corresponds to the one row of the result.

Let’s have a look at the following example.

global $wpdb;

$query = $wpdb->prepare( "SELECT * 
    FROM $wpdb->wp_terms wt
    INNER JOIN 
    $wpdb->wp_term_taxonomy wtt 
    ON wt.term_id = wtt.term_id
    WHERE wtt.taxonomy =  %s AND wtt.count = %d", array( 'post_tag', 0 ) );

$results = $wpdb->get_results($query);

if (is_array($results) && count($results)) {
    foreach ($results as $row) {
        echo $row['name'];
    }
}

Firstly, we’ve used the prepare method to prepare the SQL query for safe execution. We’ll discuss more about prepared statements later in this article. Next, we’ve passed the prepared query in the first argument of the get_results method. Finally, the get_results method executes the query and returns the result as an array.

The get_var Method

The get_var method is used to return one variable from the database, and the complete result of the query is cached for later use. It returns NULL if there’s no result.

Let’s have a close look at the following example to understand how it works.

global $wpdb;

$query = $wpdb->prepare( "SELECT wt.term_id, wt.name
    FROM $wpdb->wp_terms wt
    INNER JOIN 
    $wpdb->wp_term_taxonomy wtt 
    ON wt.term_id = wtt.term_id
    WHERE wtt.taxonomy =  %s AND wtt.count = %d
    ORDER BY wtt.count DESC", array( 'post_tag', 0 ) );

$results = $wpdb->get_var($query, 1, 0);

The get_var method takes three arguments.

• query: the SQL query which you want to execute.
• column: the column name to retreive from the results
• row: the number of the specific row you want to retrieve from the result set.

In the above example, we tried to retrieve the value of the name column in the first row.

The get_row Method

The get_row method is used to retrieve a single row from the database. It returns NULL, when there’s no result.

global $wpdb;

$query = $wpdb->prepare( "SELECT * FROM wp_posts WHERE post_type = %s", 'post' );
$row = $wpdb->get_row($query, ARRAY_A, 3);

The get_row method takes three arguments.

• query: the SQL query which you want to execute.
• return type: one of OBJECT, ARRAY_A, or ARRAY_N, which correspond to an stdClass object, an associative array, or a numeric array.
• row: the number of the specific row you want to retrieve from the result set

In the above example, we’ve tried to retrieve the fourth row from the result set.

The get_col Method

The get_col method is used to retrieve the specific column from the result set. If the result set contains only one column, it will be returned. On the other hand, if the result set contains more than one column, it would return the specific column as specified in the second argument.

global $wpdb;

$query = $wpdb->prepare( "SELECT * FROM wp_posts WHERE post_type = %s", 'post' );
$col = $wpdb->get_col($query, 3);

In the above example, we’ve tried to retrieve the fourth column from the result set.

Prepared Queries

According to the php.net manual:

“They [prepared queries] can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters.”

A prepared statement is a pre-compiled SQL statement which can be executed multiple times by sending just the data to the server. It has the added advantage of automatically making the data used in the placeholders safe from SQL injection attacks. You can use a prepared statement by including placeholders in your SQL.

In short, a query must be SQL-escaped before it is executed to prevent injection attacks. The wpdb class provides the prepare method which allows you to prepare the SQL query for safe execution. In the examples we’ve discussed so far, we’ve already used the prepare method before we execute SQL queries.

Let’s have a quick look at the following example in which the values 10, monkey and apple will be escaped when it will be actually used in the query.

// Usage: $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );
global $wpdb;
$wpdb->query( $wpdb->prepare(
    "INSERT INTO $wpdb->test_table (post_id, animal, food) VALUES ( %d, %s, %s )",
    array(
        10,
        'monkey',
        'apple'
    )
));

Setting Error Messages

You can turn database error messages on and off with the show_errors and hide_errors methods.

global $wpdb;
$wpdb->show_errors();
$wpdb->hide_errors();

Cache Control

Flushing the query results cache can be done with the flush method.

global $wpdb;
$wpdb->flush();

Inserting Data

You can use the insert method to insert a row into the MySQL table.

global $wpdb;
$wpdb->insert(
    $wpdb->foods,
    array(
        'fruit' => 'apple',
        'year' => 2012
    ),
    array(
        '%s',
        '%d'
    )
);

The insert method takes three arguments.

• table: the name of the table
• data: the data to insert (column => value pairs) without escaping
• format: an array of formats to be mapped to each of the values in $data. If you don’t pass anything, all values will be treated as strings

Updating Data

The update method is used to update a row into the MySQL table.

global $wpdb;
$wpdb->update(
    $wpdb->foods,
    array(
        'fruit' => 'apple',  // string
        'year' =>  'value2'  // integer (number)
    ),
    array( 'ID' => 1 ),
    array(
        '%s',   // value1
        '%d'    // value2
    ),
    array( '%d' )
);

The update method takes five arguments.

• table: the name of the table.
• data: the data to update (column-value pairs) without escaping.
• where: where conditions in the form of key-value pair array.
• format: an array of formats to be mapped to each of the values in $data. If you don’t pass anything, all values will be treated as strings.
• format where: an array of formats to be mapped to each of the values in $where. If you don’t pass anything, all values will be treated as strings.

Column Information

The get_col_info method is used to retrieve column metadata from the last query.

$wpdb->get_col_info('type', 1);

Let’s have a look at the parameters.

• info_type: the type of metadata which you want to retrieve. You can pass any one of these: 'name', 'table', 'def', 'max_length', 'not_null', 'primary_key', 'multiple_key', 'unique_key', 'numeric', 'blob', 'type', 'unsigned', or 'zerofill'.
• col_offset: Specify the column from which to retrieve information.

Referencing WordPress Tables

WordPress database tables can be referenced in the wpdb class. This is very convenient as table names can be different than the default ones. Here’s a list of WordPress database table references:

• $wpdb->posts;
• $wpdb->postmeta;
• $wpdb->comments;
• $wpdb->commentmeta;
• $wpdb->terms;
• $wpdb->term_taxonomy;
• $wpdb->term_relationships;
• $wpdb->users;
• $wpdb->usermeta;
• $wpdb->links;
• $wpdb->options;

Note that we don’t need to include the prefix, that’s the benefit here since the wpdb class takes care of that for us.

There we have it! A reference for custom queries in WordPress, all in one place for you.

This post has been updated with contributions from Sajal Soni. Sajal belongs to India and he loves to spend time creating websites based on open source frameworks.