Blog, WordPress, WordPress Development

Interact With Database in WordPress

WordPress is written using PHP as its scripting language and MySQL as its database management system. WordPress database is a deeper area in the system. While you already use many functions in WordPress to communicate with the database, there is an easy and safe way to do this directly, using the $wpdb class.

What Is wpdb?

The $wpdb class is a simple utility for interacting with the database directly. If you’ve been around WordPress code you’ve probably seen the get_posts() function or the WP_Query class. These let you retrieve posts in a flexible fashion, but sometimes you just need more.

$wpdb enables you to address queries to any table in your database, and it also helps you handle the returned data. The $wpdb class lets you add/modify/delete any data in WordPress modularly, making it a very powerful tool.

Because this functionality is built into WordPress, there is no need to open a separate database connection.

How to Interact With the WordPress Database using $wpdb

Always try and use WordPress-native functionality first. If this is not possible, or would be too inconvenient, go for it.

WordPress provides a set of functions to interact with databases using the $wpdb object. Since it’s a global object you can call this function anywhere using global declaration.

global $wpdb;

Before taking a detailed look at all the methods available, let’s take a look at a simple example, retrieving a list of post ids.

global $wpdb;
$posts = $wpdb->get_results("SELECT ID, post_title FROM $wpdb->posts WHERE post_status = 'publish' AND post_type='post' ORDER BY comment_count DESC LIMIT 0,4");
  • get_results() is a method that will not only fetch your results but put them in a convenient object.
  • the query itself is next which is standard SQL. You can write any query here which will be executed.
  • $wpdb->posts is a variable that points to the posts table.

The $results object now contains your data in the following format:

Array(
    [0] => stdClass Object(
        [ID] => 6
        [post_title] => The Male Angler Fish Gets Completely Screwed
    )
    [1] => stdClass Object(
        [ID] => 25
        [post_title] => 10 Truly Amazing Icon Sets From Germany
    )
)

Retrieving Results From The WordPress Database

When reading data from your WordPress tables you’ll want to:

  • get a single piece of data
  • a single row
  • a single column
  • multiple rows and columns

1. GET_VAR

You can use get_var to retrieve only one value from the database as a simple value. The value’s data type will be the same as its type in the database.

$email = $wpdb->get_var("SELECT user_email FROM wp_users WHERE user_login = 'honarsystems' ");
echo $email;

This example displays email of user with “honarsystems” user_login.

2. GET_ROW

When you need to find only one particular row in the database. It pulls the data into a one-dimensional object.

$posts = $wpdb->get_row("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'");
echo $posts->post_title;

Gets latest published post and echo its title.

3. GET_COL

This method gets a single column. This is helpful if you would like to retrieve the IDs of only the top 10 most commented posts.

$posts = $wpdb->get_col("SELECT ID FROM wp_posts WHERE post_status = 'publish'");
echo $posts[3]->ID;

This gets all published posts and store them in a single dimension array and echo third posts id.

4. GET_RESULTS

It is best for when you need two-dimensional data. It converts the data into an array that contains separate objects for each row.

$posts = $wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'");
echo $posts[0]->post_title;

This gets all published posts and displays first row title.

Insert Into the WordPress Database

The $wpdb class is prepared to add things to the database for you with the insert() method. The general use of this method is as follows:

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

This method takes three arguments. You’ll need to know what table you’re adding to, you’ll need to prepare an array of the data you’re adding and an array of the formats used.

Specifying the format is optional; all values are treated as strings by default, but including this in the method is a good practice. The three values you can use are %s for strings, %d for decimal numbers and %f for floats.

$wpdb->insert($wpdb->usermeta, array("user_id" => 1, "meta_key" => "awesome_factor", "meta_value" => 10), array("%d", "%s", "%d"));

Updating Data in Database

If you want to modify you database data, you should use update function of $wpdb.

$wpdb->update( $table, $data, $where, $format = null, $where_format = null );

Its use resembles what we saw above; but to handle the where clause of our update, it needs two extra parameters.

Using the $where parameter, we can specify the conditions of the update. It should be an array in the form of column-value pairs. If you specify multiple parameters, then they will be joined with AND logic.

$wpdb->update( $wpdb->posts, array("post_title" => "Modified Post Title"), array("ID" => 5), array("%s"), array("%d") );

Deleting Data From WordPress Database

To delete data from database you can use delete function.

$wpdb->delete( $table, $where, $format = null );

The parameters are like update function. It will remove all data that matches the $where condition.

$wpdb->delete( $wpdb->posts, array("ID" => 5), array("%d") );

Running Any Query

Sometimes you need execute a query that not executable by above functions. For example create a custom table or drop it. In this case you can use query function.

$wpdb->query("DELETE FROM wp_usermeta WHERE meta_key = 'first_login' OR meta_key = 'security_key' ");

Query function accepts SQL standard code and executes it.

WordPress Database Error Handling

Finding out why something isn’t working can be a pain when databases get involved. Are you passing the right data, did you mistype a column name, is the table name correct, and so on. There are a couple of ways to help yourself.

By calling the show_errors() or hide_errors() methods, you can turn error-reporting on or off (it’s off by default) to get some more info about what’s going on. Either way, you can also use the print_error() method to print the errors for the latest query.

$wpdb->print_error();

Protection And Validation

You have code like following

$id = $_GET['id'];
$results = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE ID =  $id" );

This code has an SQL Injection vulnerability because this query lets visitors interact with databases directly without sanitization, if the $_GET[‘id’] is an absolute number then it’s fine, but a visitor could easily add evil code here.

You can sanitize your code like this

$id = (int) $_GET['id'];
$results = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE ID =  $id" );

The above query is relatively safe but still not recommended. You don’t have to sanitize the input by your code because WordPress provides a better sanitizing using prepare() function.

$id = $_GET['id'];
$results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE ID = %d", $id ) );

If your input data is string you can use %s.

Use $wpdb or NOT to Interact With WordPress Database

It is extremely important to be able to decide when you need to use $wpdb. It is almost always redundant to use it for selecting posts as the WP_Query class is so powerful in this regard that it is almost always a better option.

Don’t forget that when using the WP_Query class, the get_posts() function, or any other function, multiple actions and filters are run. This makes your code much more modular which is what you’ll want if you’re a plugin or a theme developer.

With $wpdb you are interacting directly (or close enough) with the database so your code will become much more difficult to interact with.

Extra Functions of $wpdb

There are more functions that $wpdb offers and we talked about some in the above. In following, you can learn some other extra function.

1. INSERT_ID

Whenever you insert something into a table, you will very likely have an auto-incrementing ID in there.

$sql = $wpdb->prepare("INSERT INTO $wpdb->postmeta (post_id, meta_key, meta_value ) VALUES ( %d, %s, %d )", 3342, 'post_views', 2290);
$wpdb->query($sql);
$meta_id = $wpdb->insert_id;

Inserts data into postmeta table and displays inserted data id.

2. NUM_ROWS

If you’ve performed a query in your script, then this variable will return the number of results of your last query. This is great for post counts, comment counts, and so on.

$wpdb->get_results("SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish'");
echo $wpdb->num_rows;

3. GET_CHARSET_COLLATE

Returns charset of the database.

$wpdb->get_charset_collate();

4. PREFIX

Returns prefix of the tables of database. For default is wp_.

$wpdb->prefix

Understanding WordPress Database Tables

Each WordPress installation has 12 default tables in the database. Each database table contains data for different sections, features, and functionality of WordPress.

Note: wp_ before each table name is the database prefix you choose during the installation. It can be different if you changed it at the time of installation.

  • wp_commentmeta: This table contains meta information about comments posted on a WordPress website.
  • wp_comments: This table contains your WordPress comments. It contains comment author name, URL, email, comment, etc.
  • wp_links: To manage blogrolls create by earlier versions of WordPress or the Link Manager plugin.
  • wp_options: This table contains most of your WordPress site-wide settings such as site URL, admin email, default category, posts per page, time format, and much much more.
  • wp_postmeta: This table contains meta information about your WordPress posts, pages, and custom post types.
  • wp_posts: The name says posts but actually this table contains all post types or should we say content types.
  • wp_termmeta: This table allows developers to store custom metadata for terms under their custom taxonomies.
  • wp_terms: WordPress has a powerful taxonomy system that allows you to organize your content. Individual taxonomy items are called terms, and they are stored in this table.
  • wp_term_relationships: This table manages relationship of WordPress post types with terms in wp_terms table.
  • wp_term_taxonomy: This table defines taxonomies for terms defined in wp_terms table.
  • wp_usermeta: Contains meta information about registered users on your website.
  • wp_users: Contains user information like username, password, user email, etc.

Create a Custom Table

Sometimes you need create a custom table in WordPress database for your plugin. You can create the table with query function but there is a fancy way to do this.

In this example we are going to create my_table to store viewers IP and view date.

global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'my_table';

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (`ip` text NOT NULL,`date` text NOT NULL) $charset_collate;";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

As you see there is a dbDelta function. To call this function you have to include upgrade.php into our code.