 Hi everybody, welcome to Database Interactions with WP Query and WPDB. This is a pretty big set, this is a pretty big topic because it's actually two different topics, a little ambitious with the plan here. So the subtitle for this might be a little more accurate as to what I'm going to actually go over. And introduction to WordPress database objects for developers. My name is Jonathan Daggerhart and I develop in WordPress and Drupal. I work for a company Educational Partners International where we do J1 visa application processing. I use WordPress both personally and professionally for a long time and I develop and maintain plugins. The slides I'm going to be going through today have a lot of code on them. So if you're at the back you can visit the Daggerhart.com blog and get the slides on your local computer. Yes, yes. We'll be going through three main sections. The first one is a real broad overview of databases, very broad. After each section I'll pause and ask you for questions. So let's get started. What is a database? I told you it was going to be broad. Just in case some people accidentally ended up in this room, I thought I'd cover the very basics. So if you know what a spreadsheet is, you're really close to knowing what a database is. The whole file is a database itself, you have columns, you have rows, and depending only if you're in Google or Microsoft, the sheet or the workbook is like a table. And if you never really thought about what a database is, this simple analogy can go a really long way. So jumping right in, we are going to design our own database table real quick, just theoretically at the moment, and it is going to be for the purpose of contact form submissions. So in this database we are going to record the contacts name, their email address, a short message to us, and for our own sanity we're going to keep a unique ID for each submission. All right. In an Excel, or in a spreadsheet, that database table might look a lot like this. All we've done is named our columns, ID, name, email message. And in PHP MyAdmin, which is a UI for managing an actual MySQL database, that same table would look a lot like this. Same columns, rows, everything should be pretty familiar. So the way that we get that we as developers from over here get data from the database over here is we ask it a question in a language that it recognizes. The structured query language is kind of a generic syntax for different databases. And they'll be slightly different depending on what type of database you're using. But in general, these concepts you'll find that they apply over and over again. What we're going to ask the database is to select for us a data cell from a column. And if we want a very specific row or subset of rows, then we'll say select the database cells from this table. Oh, wait. Man, I did it already. So this should be table. All right, select data cells from a table where a column is equal to something. This is where we get into very specific rows. All right, so if I were to execute this same SQL query in PHP MyAdmin, it would return to me the name and the email for the submission row where the name is equal to John. Very simple. Questions. Any questions about what a database is or how it's organized? Because it's about to get fast. All right, here we go. What is WPDB? WPDB is a globally instantiated WordPress object that's available in PHP. And it includes a bunch of methods and properties that are extremely helpful when executing database queries in WordPress. Real simple example of that. We would access the object using the global keyword, and getResults is a very common method where we can execute almost any type of SQL query. Now before we go too much further, let's talk about when you would use this object. So in WordPress, everything that comes with core should have a right way to access that data. So if we are going to make custom interactions with the database, it really comes down to data that's not a part of core. You could certainly use this object to get core data, but that's not, in most cases, that's not going to be the right way to do it. So if we're managing our own custom database tables, say we wrote a plug-in where we're going to record contact form submissions, that's a use case. If we need to interact with the data of another plug-in or, for goodness sakes, if a theme provided a database table, then we may need to get the data that way. If you're working with a second database, not all websites are created equal. Some of them use data from many different places. Or if you need a really small subset of WordPress core data, or an aggregate of WordPress core data, that's not easily accessible through a function already. So I've been trying to think of an example for the past week. What is that subset of data? I thought it might be count the number of posts of a different post status, but there's a function for that. Does anybody have an example of this use case where we might need a subset of WordPress data that's not available through a function? If not, then we might just want to mark that one off the list. All right? It's going to come up. Here's how it's going to go. You're going to say, man, I really need just the most recent post ID, maybe something like that. And so you'll just write a simple SQL query, and you'll get that. And then five months later you'll find, oh, there was a function for that. That's the story of your WordPress life. OK, so now let's get into exactly how we're going to use this object and its methods to get data from the table. The main select methods, select being the keyword for getting data from the database. The main select methods of the object are get var, which is actually get single cell of data. Everything's color coded up here. Get row for a single row. Get a column for only a column of data, and get results for roughly whatever your query asks for. Here's a simple example of get var. I'm going to say, show me the name from form submissions, where the ID is equal to 5, and I get John back. The result of this, what this returns back to an imaginary variable, or if I printed it out, is John. So let's just keep going through these. Get row, a single row from the database, returned as a standard PHP object. So I will have every single column will be represented by property of this object. And it's not going to do anything fancy for me. If I serialize data within the database, I'll have to un-serialize it myself. But that's a very simple single row result. Get a single column. So I want all the names from everyone from my contact form. That will return an array, numerically indexed array, of exactly the names in my table. Get results is where you start to see much more flexibility in what's going to be returned to you. Get results is the generic sort of select method in which you can select anything you want from get results. And it's going to come back to you in an array of objects by default. So this specific query, I say, give me every single column from the form submissions database table. There's no where statement in this SQL query. So it is going to return all results. And what I get from that is an array where numerically indexed, and each item in that array is an object, and that object has a property for every column in the database table. OK. So this is not a lesson on writing SQL queries. It's not a lesson on designing good database tables. This is mainly going to focus on writing the PHP involved in the correct way. So the most important method, in my opinion, of the WordPress database object that you're going to need to become real familiar with is the prepare method. Prepare is how you sanitize dynamic or untrusted data that came from, likely, a user or maybe an API before you issue it as part of the command that you send to the database. The way it works is a lot like Sprint F, and it can additionally work like V Sprint F. I don't know if any of you are familiar with that, but essentially it's a string with tokens that get replaced by the arguments that you pass into the function. So let's take a little look. Do you want to get hacked? Because this is how you get hacked. We have untrusted, unsanitized data directly placed into my SQL statement. So if you have code like this on your website, maybe take a break at lunch and review it. Because the user can submit any data it wants here. And that data could be another SQL query that drops your entire database table. So we don't want to do this. We never do this. Instead, we always prepare our statements. So here is the same request. We're going to prepare select everything from form submissions where id is equal to an integer. This percent d is the token to replace a value as an integer. Additionally, we have tokens for a string or for a float. Decimal related stuff. So what we've done is we've told the prepare method to take this data, sanitize it, and then put it into the SQL query once it's safe. Then the results of the prepare method return to us as a string, the SQL variable is a string, with that sanitized query. And that is what we put in to get results. Now we have safely taken untrusted dynamic data. One more prepare for the road. So this example just shows multiple different parameters for the prepare method. It's essentially the same. We are using the percent s token because the name and the email are strings. So we need to tell the database to treat them as such. And well, let's see. Other methods, which are much more simple, you don't really have to know SQL to use these methods. These are the insert, update, delete. This is the cud of crud, if you will. So all we need to know is the table name and an array of data in which the keys of this array match our column names. And in some cases, we need an array of conditions in which the keys of this array match our column names. Now all of these, both getRow, getResults, like all of these take additional parameters. What I'm trying to emphasize here is how easy it could be. So if maybe you haven't started writing your own database interactions yet, it'll feel a little more comfortable. Ultimately, this is pretty straightforward stuff. So if I were to take these values right here and pass them into update, well, I wouldn't get much of a difference. What it would say is update table form submissions set name equal to John, where id equals 5. So that's how you would kind of read it out loud. A few properties that you're going to want to be familiar with with the WordPress database subject, especially if you're handling your own table of data. Number of rows, so after we run a query, the number of rows is equal to the total number of rows returned by the query. So that's valuable for a select. It has counted the results for you. Rows affected is the number of rows that were changed by your query. This is what you're going to use if you're going to update or delete something. If I were to say delete all rows where name equals John, rows affected would be 1 if I only had one John in my database table. Insert id. If I am going to, if I've created a system that will automatically add new rows to the table, then after each execution of an insert, the property insert id will be the value of an auto increment column. Not going into auto increment, but just know that it exists. The prefix is the prefix on all your WordPress database tables as defined by the user during installation. So you've all probably, most of you have installed WordPress before and ask you, what is your database prefix? And by default, it's wp underscore. So if you change that, then the wpdb object remembers what you, it's always available to you. It remembers what you put in during installation. And additionally, these are somewhat, I didn't want to list them all, but every WordPress core table is also represented by its own property and it's already prefix for you. I'll show a little bit of how we'll use those later. No way, I'll show now. So let's do using the prefix property. In this example, I want to select the hello world post that comes with every WordPress install. So I'm going to select everything from the prefix as defined by the user next to the post table where id equals 1. Since I did a get row, the result is a standard object with a property for every column in the table. Whereas I could also just use the core table name itself without the prefix is available as part of the wpdb object and it has already prefixed the table name for me. So these two statements are exactly the same in their result. This wpdb post, if I were to print that to a screen, it would be wp underscore post. All right, that is wpdb, brief overview. Any questions? Yes? Absolutely. The question is, if I create a select query that is limited to a number of five, what will the value for the number rows be? It will be five. It will be the actual rows that you request them. So even though there may be 100 available, it will respect your limits. And so if you ask for 10 and there's only eight, it'll be eight. So it's not about what you asked for, it's about what is actually returned. A number of total results? Well, I'm not exactly clear on how you can get 100 and only five results from a query. And what am I? Oh, OK, you're talking about just counting all the rows. OK, that would be, it's a different type of query. So you'll say, yeah, select count and the wild card for all columns or whatever it's star. And your database table, and you'll use, for that type of query, you'll use get var. And that will return just the number of rows found. I don't know if it is. I'd love to know, because I didn't find that while digging into this. Thank you. Any other questions? So there is one little catch that you might run into when you're writing all of your SQL statements, especially if you're familiar with the like, where you're searching for rows that are like a value, commonly used in search results, I guess, a very simple form of doing search. There is a function called escape like that you need to use instead of or along with the prepare method. All right. So wpquery and the global wpquery object. So this is, we're going to go into how WordPress works, how the main loop operates, and what works the way it does, and what little gotchas you might run into. So the wpquery class does double duty at the very least. It might even do more, where it handles both the querying of post and the execution of a loop that you might see in your theme templates. So the global wpquery object is an instance of the wpquery class that was created early during WordPress execution that's dictated by the rewrite rule or the route or the context of what you're seeing on the page. So why would you use wpquery instead of just writing your own queries? Well, if you want to deal with post, this is the way to do it. If you have a really complex query, this will make it easy. There's a lot, most everything in WordPress is a post. So there is a lot you can accomplish without writing your own SQL queries. So if you're just getting into theme or plug-in development and you need to access post, you start here and then you work your way towards wpdb. I did the presentation backwards because they'll build up. We'll see. Bonus, when you're using the wpquery as a loop, your template tags will work. If anybody's a themeer, you know that you live and die by template tags. So the only way to make a custom query and have your template tags work, well, it's not the only way, but a very easy way is to use a wpquery loop. Additionally, the objects you'll be dealing with within the loop will be wppost objects, which are a little better than standard objects with properties. They have some methods that are helpful. So now we're going to dig into the main loop, how it really works, what it does, and implications of that. If you've ever opened the template file in a WordPress theme, you've seen these loops, this type of loop. We check to see if a query has half post, and then we do the post. So these are really familiar, but what do they actually do? I'm not sure if you've dug into it, but I recommend it. In fact, we'll do it right now. So this is a copy of the function half post and the post. And the takeaway from this is that these functions are just wrappers for the global wpquery object. So what we can say is that these two loops are equivalent to each other. And if you can become comfortable with this, then you can become comfortable with executing your own wp queries. Does anybody have a question about this before stepping forward? All right. So this is our first attempt at our own custom wpquery, but it's incomplete, and we'll find out why. So we instantiate a new wpquery, and then we run a loop on it, similar to what we just saw. We check if it has post, and while it still has post, during each iteration of the loop, we run the post that will set up the next post available within the query, within the loop. OK. So what do these methods actually do? We saw that the other functions are just wrappers for these methods. So let's find out what's happening. Half post is relatively harmless. It keeps track of the loop state, and at the very end of the loop, it'll reset the loop back to the beginning. And it's very useful, but not quite as much of a problem as the post can be. The post can be very destructive, and we're going to see exactly why. So wpquery the post. Take a look at this for just a moment, and I've written all the hints you need in the side here. So what we want to understand here is that, first of all, there is a global post object. Be aware. And second of all, we just lost it. It just got replaced by the next post in the loop. So what does this mean? Well, the implications of replacing the global post object can be very big, catastrophic to some degree. So the original page context, as determined by the rewrite or the route or whatever you want to call it, has been significantly changed. If we've executed a new wpquery and we've run the post within that query, then we've lost the original wpquery context, as well as the original global post context. If we don't fix it, the rest of the page will have the wrong context for execution. So say you have a widget in the footer that relies on knowing what the current post being viewed is. Well, that widget won't have post information anymore, because we've blown away the global post object and replaced it with something from our loop. But on the bright side, template tags will work. So it's actually the replacing of that global post object that makes all your templates tags work. If you dig into a template tag, you're going to find that somewhere within any of those functions they'll run getPost. And that just accesses the global post object, and then it goes and gets the title or the content from that. So enter wpreset query, very important. This function restores the global wpquery back to its original state, as well as the global post object. So this is just that function exactly. We'll see that it is setting the global wpquery equal to the global wpthequery, which simplified as it's the original state. It's a backup of the query. That way we can do these things. And then it runs another function that does the same thing for the post object, reset the post data. So now we actually have a complete wpquery loop that is ready to go. We can use this code. We can use this template to write our own loops all day long, and we're not going to mess up anything in WordPress. Well, there's an asterisk there. The admin side is a little different. All right. So the one thing we haven't looked at here is the arguments we can pass into wpquery. And there's a whole lot of them. And some of them are redundant. But I'm not going to go into every single one. There are some resources available on the blog post, and I can point you in the right direction. When in doubt, check the codex. There's a lot of information there. So some that I use all the time, very simple ones, involving strings or numbers, post per page, the order, the post type, order by. So I wrote it in this order so that we could actually get a sense about this query. Show me the 10 most recent posts sorted by date. Those last four don't relate to that sentence. But when you're making your queries for the first time, either in SQL or in wpquery, I can't stress enough how helpful it is to write a sentence out, write it on a piece of paper. What do I want from the database? And once you have that sentence written down, you're really close to understanding exactly what you need to tell the database. So other very useful things, if you want to get a page by a slug, page name, if you want to get a post by a slug, name, category, tag, these are really useful. You can use these every day. You have my permission. These are some other helpful arguments. You can handle the post status based on the post parent. Ignore sticky post is important, especially as plugin and theme developers. And I'm drawing attention to it because this has gotten me. And if you don't pay attention, it will get you. So what's going to happen is by default, ignore sticky post is set to false, meaning sticky post will always be the first result in your query. So when you're building a website, no problem. You probably don't have any sticky post that you made as an example. But then four months later, your client's going to make a sticky post. And it's going to show up in every widget, on every wrong page throughout your entire site. So just I recommend getting the habit of throwing ignore sticky post equals true into all your queries unless you want sticky post. Another incredibly valuable one, especially when a combo is the post in array. You pass an array of post IDs, and that's what you get back. If you combo it with order by, then you can say, I want all of these posts, and I want them in that exact order. So the order by value I've set to post in, and I've provided post IDs to the post in argument. Author in if you want other articles by this author. Author in rather, sorry. And if you need complex stuff, go to the codex. No time today. But know that you can query based on taxonomy and metadata using WP query. So here's an example of a complete working. It looks a lot like the other one, but I've provided some arguments. So what this loop is going to do is get us, by default, the 10 most recent posts. We're going to loop through them, and we're going to show their title as an H3. And now we actually know why all of this works, I believe. If you're not sure how this knows what post title to show, let me know, and I can go back over it. So we instantiate a new WP query. We make sure it has posts. We start looping through the post. We set up the global post object, and now all our template tags work. When we're done, we clean up. We reset the query. Here is the SQL generated by that default WP query, just as an example. Now, all of this isn't completely necessary, but I thought it tied well together with the previous section on writing your own queries. So if you look at it closely, well, if the light was a little better here, you can see that in this query, it's saying where post type equals post, where post status equals publish, order by post state descending. This looks exactly like what we asked it to go get. It looks like all of our SQL queries. It's not magic, it's just helping you. Oh, additionally, the query object has the request property with that information at any time. So if you wanna run a really complex query and then see what that SQL looks like, go for it. Yes? I'm sorry, yes, so not all of these. Many of these arguments have default values. So there is the default values, I believe, and I might be missing something here, or something like the 10 most recent posts sorted by date with sticky at the top. I bet I would assume, and maybe somebody correct me if I'm wrong, but right after installing WordPress, how the homepage works, that's probably your default WP query right there. Does anybody know if that's incorrect? Yes? Yes, the reading settings, I'm not sure if that's specifically for the homepage or if that's for every query. Do you know, does that update the tag pages and the categories as well? Okay. Okay, then that's changing the default, essentially. Thank you. And so for the sake of the video, the question was is when you change in the reading, in the reading section of the WP admin, when you change the number of posts returned, does that change, is that the default value we're talking about with post per page? And the answer is yes. All right, so any other questions? We're pretty close here, all right. So one more example, that's a little more complicated. What I wanted was to get the 15 posts, give me 15 posts that have a featured image. We can do that with WP query. We're gonna use a meta query. First we're gonna say post per page, give me 15. And then we're gonna do a meta query where we say the meta key underscore thumbnail ID exists. We're gonna execute that within a loop where we call the post thumbnail and we're golden. We just, this much code we just did a complex SQL query and handled its output. So there's some other functions available in WordPress that you've probably heard of, query post and get post. They're very similar, but they work in extremely different ways. Query post will override the global WP query object. So keep that in mind. If we were to look at the query post code, which I don't have up here, it's like two lines and it says global WP query equals new WP query. So we're erasing the global WP query context again. But what it returns to you is just the array of post as a result. So that means since it's just an array of posts and it's not a WP query object, we can't perform a loop on the results. The reason you might use query post is to change the results of the page, not to run a custom loop. Get post does very similar to query post, but it does not replace the global WP query object. So instead it's a brand new instance of WP query and it also returns an array of the posts found. Neither of these can be used for loops. Get post can also take some slightly different arguments. So if you think you need get post, if you're looking around, the reason you might need get post is because you don't wanna deal with the global scope of WP query at all. Just take a look at the codex page for get post and you'll see that some arguments might be a little different, but really they just allow extra arguments. Any questions on these? Yes. Yes. Correct. Well you probably could. There's a lot of hooks and filters available. I mean you can basically make it do anything. It is a function that writes SQL for you. It's got a lot of extra helper methods. All of your conditional tags are also a part of the global WP query object. So is page, is archive. Those are all part of the WP query object. So when you run your own custom query, right here I could say my query little arrow is page two. And if the current page in the loop is two then that would be true. If not it would be false. Oh, you mean two. So if you wanted to, there are probably ways. So there's a lot of filters that let you, after it's done building a big SQL query for you, there's a bunch of filters that fire off where you can change that query directly or append to it. So one thing you can't do unless I'm mistaken with a WP query is a group by command. But with some of those later filters you can just manually append a group by state a group by command to the longer SQL statement. In general, as far as I know, it's only for posts. You could probably make it do other things but that might not be best. Right, right. Absolutely, any other questions? Oh wait, here we are, any other questions? All right, well thank you very much.