Advanced WordPress Queries, Part 3

Throughout this series, we’ve taken a look at the WordPress Database, executed some simple queries against it, and looked at a variety of ways to read data from the database. As useful as this is, it will only take you so far… especially when it comes to creating your own themes, plugins, or other extended WordPress-based functionality. In this final post, we’ll take a look at how we can update existing values in the database and even introduce values of our own!

Security Measures

Up until this point, we haven’t worried too much about writing defensive queries to protect against things such as SQL injection. Since we’re going to be writing data directly to the database, now is a good time to begin doing so. Luckily, the WordPress API makes this relatively easy.

Generally speaking, queries must be wrapped in a prepare statement and all values that will be inserted into the database will be passed within the context of an array. For example:

<?php
$query = "INSERT INTO $wpdb->posts (post_id) VALUES (%d)";
$wpdb->query($wpdb->prepare($query, 10));
?>

Strings are represented using the ‘%s’ token and integers are represented using the ‘%d’ values. So, assuming that you’ll be passing these values into the database, you would write a query like this:

<?php
$query = "INSERT INTO $wpdb->posts (post_id, post_title) VALUES (%d, %s)";
$wpdb->query($wpdb->prepare($query, 10, 'Post Title Example'));
?>

Though this works for the majority of the work that you will be doing, you can go a step further using WordPress’ data validation functionality.

Inserting New Values

Inserting new values into the database is especially useful when you have a collection of values that you want to write to the database. Specifically, inserting values is most useful when you’re going to be adding an entire new row to the database.

To do this, the only thing that you really need to know is the schema of the table(s) that you’ll be updating. In the following example, we’ll take a look at how we can insert a new user into the database using a custom role.

Assume that you’re going to be introducing the ‘Code Snippet’ term to the WordPress database. The term table schema consists of the following columns the first three of which are required:

• term_id
• name
• slug
• term_group

We also have to create a relationship between the term and the taxonomy table. The taxonomy table is simple and will require the following values:

• term_taxonomy_id
• term_id
• taxonomy

We’ll need to specify values for each of the required columns prior to writing it to the database before writing each query. I’ve named the variables the same as the columns that they’ll modify so the code should be easy to follow:

<?php

global $wpdb;

$term_id = 100; // note that this may need to be adjusted based on the ID's in your table
$name = 'Code Snippet';
$slug = 'code-snippet';

// add the new category
$query = "INSERT INTO $wpdb->terms (term_id, name, slug) VALUES (%d, %s, %s)";
$wpdb->query($wpdb->prepare($query, $term_id, $name, $slug));

// create the relationship
$term_taxonomy_id = 100;
$taxonomy = 'category';
$query = "INSERT INTO $wpdb->term_taxonomy (term_taxonomy_id, term_id, taxonomy) VALUES (%d, %d, %s)";
$wpdb->query($wpdb->prepare($query, $term_taxonomy_id, $term_id, $taxonomy));

?>

At this point, you can load up your database front end and you should be able to see the new record in the database. Alternatively, you should be able to view the new category in your Categories screen in the WordPress administration area.

Updating Existing Values

Updating existing values in the database is similar to inserting records but can be used just to change a single (or a subset) or values. Updating records typically follows two specific scenarios:

For example, let’s assume that you want to change the title of the very first post in the database:

<?php
global $wpdb;
// Custom Keywords and Link Option Test
$wpdb->update("$wpdb->posts", array('post_title' => 'First Post!'), array('ID' => 645));
?>

Another example would be updating multiple rows within a single query. Case in point, let’s say that you want to convert all of your post tags to categories. You’ll need to locate all of the term_taxonomy_id’s that have the taxonomy of ‘post_tag’ and update their taxonomy to category.

Note that I recommend running this only in development because it will modify your existing tag structure:

<?php
global $wpdb;
$wpdb->update("$wpdb->term_taxonomy", array('taxonomy' => 'category_demo'), array('taxonomy' => 'post_tag'));
?>

Simple (but powerful), right?

As a general rule of thumb, if I can update a value from within the WordPress administration options then I will do so there. I only run update queries within the context of a theme or plugin when I need to modify some data that’s not easily accessible via the user interface.

Moving Forward

At this point, we’ve covered the WordPress database, the ways in which we can read a value (or values), updating existing records, and inserting new records all by writing a few lines of code.

From here, you should be able to take your theme and plugin development to the next level.