Advertisement
Cheat Sheets

The WordPress Coding Standards: Database Queries and Formatting SQL Queries

by

Aside from the summary that we're going to be providing as the last article in this series, this is the last explication of the WordPress Coding Standards that we're going to be covering in this series.

We're going to be covering the nuances of database queries and how to format SQL within the context of your code.

Of course, this wouldn't be without its own set of caveats: Generally speaking, there are APIs that are already available that can prevent us from needing to write SQL on our own; however, these APIs don't catch every single case that we actually need.

After all, how can developers implementing APIs know exactly what and how we're going to build something?

To that end, we're going to take a look at the APIs that are available for executing database queries, how to use them, and then how to define our own queries when the APIs fall short.


Database Queries in WordPress

As mentioned in the introduction of this article, there are a number of APIs that make it possible for us to craft our own queries without needing to write SQL.

The reason that it's important to become familiar and to learn these APIs is so that the code that you write will be written on top of the level of abstraction provided by WordPress to make sure the queries are as optimized as possible (given the current version).

Additionally, it increases the likelihood that any code that you write today will be compatible with future versions of WordPress primarily because, again, it's written against the level of abstraction provided by WordPress.

If the table schema changes, parameters map to different clauses in the SQL, or so on, you don't have to worry about it because the API will take care of it for you.


What Are the Query APIs?

So what are the query APIs that WordPress defines?

  • WP_Query is intended to be used to query information about any post type and its related author, category, taxonomies, type, status, and so on attributes.
  • WP_User_Query is intended to be used when we need to retrieve information from the user table and the usermeta table. It also allows us to work with roles, custom fields, and more.

There are also other WordPress API methods that make it really easy to grab stuff from various database tables some of which don't even require a significant number of arguments:

  • get_post_meta retrieves meta data associated with a given post ID. It can retrieve all of the meta data or the value for a specific key.
  • get_comment_meta retrieves meta data associated with a given comment ID. It can retrieve all of the meta data or the value for a specific key.
  • get_user_meta retrieves meta data associated with a given user ID (are you starting to see a theme here?). It can retrieve all of the meta data or the value for a specific key.

Note that the purpose of this article isn't to take a deep dive into each of these APIs (and there are more of them) - only to make them known to those of you who haven't previously used them, and to given a short definition as to when they can be used.

Ultimately, these are the APIs that you should examine first before writing your own SQL. For what it's worth, I'm speaking from experience here: There have been times where I've written code (or even blog posts) that have been busted because they weren't using the best practices for querying the database.

But, as previously mentioned, these APIs can't predict all cases in which we need to write our database queries. In those situations, WordPress provides an object that allows us to directly interact with the database.


All About $wpdb

That brings us to $wpdb. Essentially, $wpdb is an object that's available in WordPress that allows us to interface directly with the database. This means that we're able to write raw SQL and have it execute against the underlying database.

On top of that, we can select how we want the data returned: arrays, objects, sometimes single values, and so on. In fact, the object offers the ability to perform the following functions:

  • SELECT variables, rows, columns, and generic results
  • INSERT rows
  • UPDATE existing rows

Perhaps the largest concern when introducing raw SQL into your project is that you're opening your project up to potential malicious behavior. Though you could make this case for any database logic, the truth is that APIs should do a good job of protecting us from anything like that.

Generally speaking, they do.

But $wpdb isn't exempt from that either. There are specific ways to interface with the database so that you can protect your queries against SQL injection.

To reiterate a point from the the Coding Standards:

If you must touch the database, get in touch with some developers by posting a message to the wp-hackers mailing list. They may want to consider creating a function for the next WordPress version to cover the functionality you wanted.

So, in short, if the API falls short of what you need, then $wpdb may be your best option, but I recommend only using it if you've exhausted the rest of your options.


Conclusion

At this point, we've examined the Coding Standards in a level of detail that I hope equips you with information that you did not previously have when starting this series.

To conclude this particular post, the biggest take away that I want everyone to have is this:

  1. Check the API before writing inline SQL. Even though writing direct SQL queries may be easiest, especially for those who are accomplished with database systems, resist the urge! Check the API documentation first.
  2. If you can't find an API function to do your work, then write SQL. If you must write SQL, make sure that you take all things necessary into account - how you want your data returned, you've properly prepared your queries, and you're properly handling the data when it's returned.

If you do that, then you should have your bases covered.

In the final article in this series, we'll have a quick guide summarizing everything that we've discussed throughout the series.

Related Posts
  • Code
    Creative Coding
    Using WordPress for Web Application Development: A ReviewApplication foundation 400
    Over the past few months, we've been taking a look at all of the features and aspects that make WordPress a potential foundation for application development. In fact, we've spent roughly 15 articles talking about all that WordPress offers. And though we'll be reviewing each of the points in this email, perhaps the biggest thing to take away that building web applications using WordPress is different than using many of the popular frameworks that are currently available namely because WordPress isn't a framework.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Custom Database QueriesApplication foundation 400
    Throughout this series, we've been looking at the various facilities that make it possible to treat WordPress as a foundation for web application development. Thus far, we've covered a lot of ground: We've talked about how WordPress is more of a foundation rather than a framework. We've discussed the nature of the the Event-Driven Design Pattern. There's been a discussion of Email, User Management, Saving Data, Retrieving Data ...and more. In the most recent articles, we've been talking a look at how to handle queries against the WordPress database through the use of WP_Query and WP_User_Query.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: WP_User_QueryApplication foundation 400
    In this series, we've been taking a look at how WordPress can be used to development web applications much like a number of different frameworks and other tools that are available. Starting in the last article, we began looking at the different options that we have as it relates to querying the WordPress data. First, we reviewed WP_Query.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Features: Custom Queries with WP_QueryApplication foundation 400
    We've been looking at how WordPress can be used as a foundation for application development, but one of the things that we've yet to cover that most modern frameworks offer is how to query the database to retrieve results for any given view. Specifically, we haven't talked about how to get information out of the database and insert it into our pages.Read More…
  • Code
    Creative Coding
    Using WordPress for Web Application Development: Features: Saving DataApplication foundation 400
    When it comes to the web, almost any application that runs within your browser is backed by a data store of some type. Usually, these data stores are in the form of a type of database. Until recently, most applications were built on top of some type of SQL database, but with the rise of document-based databases such as CouchDB, other applications have begun to use other backends as well.Read More…
  • Code
    Plugins
    Migrating Your WordPress Database: A Database PrimerMigrating your wordpress database 400
    When it comes to working with WordPress-based projects, arguably one of the most frustrating or tedious aspects of deployment is actually getting the databases across your environments in-sync with one another. Sure, there's something to be said for using test data in development, user data in staging, and actual data in production, but there's no such thing as a silver bullet, right? That means that sometimes test data is going to work; other times, it won't. For example, let's say that you inherit a project for which you have to pull down a database and then begin working with existing data. Or let's say that you have to migrate an entire site or application from one server to another. In cases like that, test data doesn't help a whole lot. Instead, you need a tool for it. And sure, the WordPress Importer is a fair tool for basic migrations, and running SQL exports and imports is okay if you're comfortable with database front-ends and working with SQL itself. But what about those who are somewhere in between?Read More…