Writing Custom Queries in WordPress

With custom WordPress queries, you can make any data reading or manipulation you want. This opens up a world of new possibilities. Let's get started with this WordPress custom query tutorial!

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 in WordPress and the WPDB class).

The wpdb Class

Prepared Queries

Setting Error Messages

Referencing WordPress Tables

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 any type of custom query in WordPress projects.

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.

1	global $wpdb;
2	$query = "SELECT COUNT(apple) FROM $wpdb->fruits";
3	$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 will execute the query and return 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 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 prepared statements in more detail 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 retrieve 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.

1	global $wpdb;
2
3	$query = $wpdb->prepare( "SELECT * FROM wp_posts WHERE post_type = %s", 'post' );
4	$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 will return the specific column as specified in the second argument.

1	global $wpdb;
2
3	$query = $wpdb->prepare( "SELECT * FROM wp_posts WHERE post_type = %s", 'post' );
4	$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 they're 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.

1	global $wpdb;
2	$wpdb->show_errors();
3	$wpdb->hide_errors();

Cache Control

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

1	global $wpdb;
2	$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.

1	$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 from 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.

Writing Custom Queries in WordPress

Why Use Custom Queries?

The wpdb Class

The query method

The get_results Method

The get_var Method

The get_row Method

The get_col Method

Prepared Queries

Setting Error Messages

Cache Control

Inserting Data

Updating Data

Column Information

Referencing WordPress Tables

More WordPress Tutorials

The `wpdb` Class

The `query` method

The `get_results` Method

The `get_var` Method

The `get_row` Method

The `get_col` Method