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).
This global WordPress class is key for using queries. In fact, every function uses this class.
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);
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);
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);
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
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);
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);
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
hide_errors functions, but you can also print:
Deleting the cache can be made with the
$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
$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
- where conditions
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.
- 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:
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.