New Post has been published on Mocco
New Post has been published on http://mocco.sk/the-wordpress-coding-standards-database-queries-and-formatting-sql-queries/
The WordPress Coding Standards: Database Queries and Formatting SQL Queries
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.
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.
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
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.
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:
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.
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.
Other parts in this series:
Check out the original source here.
