With custom queries you can make any data reading and/or manipulation you want. Instantly a world of new possibilities open up.

Why Use Custom Queries?

The basic functionalities in WordPress are fine for most simple needs, but what would you do if you want to implement some specific needs? Are you writing a plugin maybe? Then you should learn how you can use SQL queries in WordPress right now! The official references can be found in the WordPress Codex (Custom Queries and the WPDB class).

The wpdb Class

This global WordPress class is key for using queries. In fact, every function uses this class.

Using query

The query function needs a string containing the custom query. The returning value is an integer corresponding to the number of rows affected/selected, and false when there is an error.

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

get_results

This function gets multiple rows when executing a query. By default the result of the function is an array.

$query = "
	SELECT *
	FROM wp_terms wt
	INNER JOIN wp_term_taxonomy wtt ON wt.term_id = wtt.term_id
	WHERE wtt.taxonomy = 'post_tag' AND wtt.count = 0";

$wpdb->get_results($query);

get_var

This will return one variable from the database, but the complete result of the query is cached for later use. Returns NULL if no result is found.

$query = "SELECT COUNT(*) FROM users";
$wpdb->get_var($query);

get_row

A complete row will be returned as a result of the function, which can be an object, an associative array, or a numerically indexed array. NULL is the result when no matching data is found. result_type can be OBJECT, ARRAY_A or ARRAY_N (object, associative array or numbered array). Offset is an integer with a default of 0.

$query = "
SELECT * FROM wp_posts
WHERE post_type = 'post'";

$wpdb->get_row($query, ARRAY_A, 3);

get_col

For getting a column, use this function. Output will be a dimensional array. An empty array will be returned if no result is found. The second parameter is the column offset.

$query = "
SELECT * FROM wp_posts
WHERE post_type = 'post'";

$wpdb->get_col($query, 3);

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."

You can protect SQL queries against SQL injection attacks. In short data in queries must be SQL-escaped before the query is executed to prevent injection attacks. This can be easily done with the prepare method. In the following example, the values '10', 'monkey' and 'apple' will be escaped when used in this method.

// Usage: $wpdb->prepare( 'query' [, value_parameter, value_parameter ... ] );

$wpdb->query( $wpdb->prepare(
	"INSERT INTO test_table (post_id, animal, food) VALUES ( %d, %s, %s )",
	array(
		10,
		'monkey',
		'apple'
	)
));

Setting Error Messages

You can turn error messages on and off with the show_errors and hide_errors functions, but you can also print:

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

Cache Control

Deleting the cache can be made with the flush function.

$wpdb->flush();

Inserting Data

$wpdb->insert($table, $data, $format);

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

The used parameters in order are:

• the name of the table to insert data into
• the data to insert (column => value pairs) without escaping
• an array of formats to be mapped to each of the values in $data. If not present, all values will be treated as strings

Updating Data

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

The used parameters in order are:

• table name
• data
• where conditions
• format
• where_format

Column Information

You can get information about the columns of the most recent result with this function. When a function has returned an OBJECT and there are properties you don't know much about, this can be useful.

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

• Type: the information you want to retrieve, some examples are here
  • name – column name (this is the default)
  • table – name of the table the column belongs to
  • max_length – maximum length of the column
  • not_null – 1 if the column cannot be NULL
  • more can be found in the WordPress Codex WPDB reference
• Offset: specify the column from which to retrieve information (0 is the first column)

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 where 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.