In this article, I will show you how to get started with the wpdb
class, how to update/retrieve data from WordPress original tables and how to interact with your custom tables.
WordPress defines a class calledwpdb
, which contains a set of functions used to interact with a database. Because this function is built into WordPress, there is no need to open a separate database connection.
Because methods in the wpdb
class should not be called directly. We have to use the global $wpdb
object instead! WordPress database has 12 (11 before vesion 4.4) tables by default. To interact with one of them, you can use this syntax: $wpdb->table_name
, here are the name of tables:
- posts.
- comments.
- links.
- options.
- postmeta.
- terms.
- term_taxonomy.
- term_relationships.
- termmeta.
- commentmeta.
- users.
- usermeta.
To interact with custom tables. You can use this syntax:
1 2 |
$wpdb->prefix . 'table_name' $wpdb->base_prefix . 'table_name' // if you are using WordPress multisite and want to use prefix that was defined in the wp-config.php |
Ok, so now, Let’s get into wpdb
methods:
SELECT a Variable
The get_var
function returns a single variable from the database. It returns NULL
if no result is found
1 |
$wpdb->get_var( $query ); |
For example: Retrieve the number of posts.
1 2 3 4 |
$post_count = $wpdb->get_var( " SELECT COUNT(*) FROM $wpdb->posts" ); |
SELECT a Row
To retrieve an entire row from a query, use get_row
. The function can return the row as an object, an associative array, or as a numerically indexed array.
1 |
$wpdb->get_row( $query, $output_type, $row_offset); |
$query
: (string) The query you wish to run.$output_type
: One of three pre-defined constants. Defaults to OBJECT.- OBJECT - result will be output as an object.
- ARRAY_A - result will be output as an associative array.
- ARRAY_N - result will be output as a numerically indexed array.
$row_offset
: (integer) The desired row (0 being the first). Defaults to 0.
For example: Get all publish posts.
1 2 3 4 5 |
$list_posts = $wpdb->get_row( " SELECT * FROM $wpdb->posts WHERE post_status = 'publish'" ); |
SELECT a Column
To SELECT a column, use get_col
. This function outputs a one dimensional array.
1 |
$wpdb->get_col( $query ); |
For example: Get all user login names.
1 2 3 4 |
$list_posts = $wpdb->get_col( " SELECT user_login FROM $wpdb->users" ); |
SELECT Generic Results
Generic, multiple row results can be pulled from the database with get_results
. The function returns the entire query result as an array.
1 |
$wpdb->get_results( $query, $output_type ); |
$query
: (string) The query you wish to run.$output_type
: Defaults to OBJECT. See SELECT a Row for more information.
For example: Get ID, post title, post excerpt from posts.
1 2 3 4 |
$list_posts = $wpdb->get_results( " SELECT ID, post_title, post_excerpt FROM $wpdb->posts" ); |
Running General Queries
The query
function allows you to execute any SQL query on the WordPress database.
1 |
$wpdb->query( $query ); |
For example: I have a custom table named wp_test
, this table has two columns (ID and value), I want to delete a row that has ID equal 3.
1 2 3 4 |
$wpdb->query( " DELETE FROM {$wpdb->prefix}test WHERE ID = 3" ); |
Protect Queries Against SQL Injection Attacks
All data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks. To do that, use prepare
funcion.
1 |
$sql = $wpdb->prepare( 'query' , value_parameter[, value_parameter ... ] ); |
$query
: (string) The SQL query you wish to execute, with placeholders.$value_parameter
: (int|string|array) The value to substitute into the placeholder.
For example:
1 2 3 4 5 6 7 8 9 |
$wpdb->query( $wpdb->prepare( " INSERT INTO {$wpdb->prefix}test ( ID, value ) VALUES ( %d, %s )", 2, $value, ) ); |
INSERT row
1 |
$wpdb->insert( $table, $data, $format ); |
$table
: (string) The name of the table to insert data into.$data
: (array) Data to insert (in column => value pairs).$format
: (array|string) (optional) An array of formats to be mapped to each of the values in$data
.
For example:
1 2 3 4 5 6 7 8 9 10 11 |
$wpdb->insert( $wpdb->prefix . 'test', array( 'ID' => 5, 'value' => 'this is it', ), array( '%d', // value1 '%s', // value2 ), ); |
UPDATE rows
1 |
$wpdb->update( $table, $data, $where, $format = null, $where_format = null ); |
$table
: (string) The name of the table to update.$data
: (array) Data to update (in column => value pairs).$where
: (array) A named array of WHERE clauses (in column => value pairs). Multiple clauses will be joined with ANDs.$format
: (array|string) (optional) An array of formats to be mapped to each of the values in $data. If string, that format will be used for all of the values in $data.$where_format
: (array|string) (optional) An array of formats to be mapped to each of the values in$where
. If string, that format will be used for all of the items in$where
.
For example:
1 2 3 4 5 6 7 8 9 |
$wpdb->update( $wpdb->prefix . 'test', array( 'value' => 'new value', ), array( 'ID' => 5, ), ); |
DELETE Rows
1 |
$wpdb->delete( $table, $where, $where_format = null ); |
$table
: (string) (required) Table name.$where
: (array) (required) A named array of WHERE clauses (in column -> value pairs). Multiple clauses will be joined with ANDs.$where_format
: (string/array) (optional) An array of formats to be mapped to each of the values in$where
. If a string, that format will be used for all of the items in$where
.
For example:
1 2 3 4 5 6 7 8 9 |
$wpdb->delete( $wpdb->prefix . 'test', array( 'ID' => 5, ), array( '%d', // value1 ), ); |
Very simple but important to know, thanks for your reading!