Custom Database Queries in WordPress | WordPress Tutorials

welcome back to the channel and welcome to another video tutorial in this video we're going to talk about custom database queries [Music] so I'm going to show you guys how to do a custom database query inside of WordPress right and it kind of concerns me that you're asking about that if you know how to do a database query outside of WordPress it's pretty much the same thing what concerns me about you guys asking is if you don't understand databases and the way databases are indexed writing a custom query can greatly slow down the loading of your website and can also put a lot of heavy load on the database itself so indexing the columns being indexed could greatly slow down the the loading of your query so if you're doing a for example you're doing a query on using a keyword that someone is put in a search box and you're querying the post title or the post content in the posts table neither one of those are indexed now if you don't know what indexing is indexing is the just like the index in the back of the book if you want to know where our certain term is in a book you go to the index and the index will tell you what page that term is on so you go to the back of the book you find that term in the index because it's in alphabetical order it's really easy to find and it says this term is used on page 47 and 19 so you go to page 47 and there it is there it talks about that term that you're looking for same thing in a database the database index tells you what rows is using that term now the post title and the post content are not indexed the post name is which is basically the slug of the title so if your title is hello world this lug is going to be all lowercase hello - world so if you're searching for world in the in the slug or in the post name it's going to quickly find it because it has an index of those terms if you're searching that for the title or the post content then that's going to take a little while depending on the number of posts you have because it has to look at every single post in the database for that term so I hope you guys understand that let's get started I'll show you how to write a post or write a custom database query inside of WordPress alright so here on the idea Pro IO website which we use for testing if you're new to the channel we use this for testing and just to for an examples and to make and to make some of these videos so I've got an example post open here which is our hello world post here in the post we've got several different you know a few different posts here and we can go into this testing post and that is a shortcode that we were using let's not use that one hello world post let's do that so this is hello we're all post and we've got some custom fields down here opened up and we can add a custom field to this post now when you add a custom field to a post basically what it does is it puts a a row in the post meta table that is the the name is the key and the value is the meta value so it's meta meta key and meta value and I'll show you that in just a just a second here alright so we're gonna go to our code editor now one thing I want to talk about is if you're writing this inside of a function in the functions dot PHP or you're writing this inside of a plug-in WordPress automatically pulls that plug-in and that functions PHP from your theme into the WordPress structure so I want to show you really quickly how we can do a database query outside of the WordPress structure but still use all of the WordPress core I do this sometimes for admin areas like we have a site that we've been working on for a while now that the entire sites built into WordPress but they wanted an admin subdomain so we created an admin subdomain which basically makes it a whole different website but we pulled in the WordPress core structure so that we can access all the core WordPress features and access the same database without with very little coding on the admin side so we were able to build an admin area that's separate from the main area but still accesses the same database and the same core files and reason why I want to talk about that is because I want to talk about creating an a standalone page that's outside of your WordPress theme or structure and this is good if you didn't build your own theme and you're using a theme that someone else is built and you don't want to modify that theme or you don't want to create a child theme from that or you don't want to put it in the function dot PHP file or in a plug-in if you want to build a standalone page that doesn't include the header and footer which you can do that also and you just want to display those results from that bad based query right there on the page you want just a blank page maybe your building landing pages and or what they call squeeze pages right sales pages and you don't want that the normal websites header and footer on there but you want to be able to pull from the database and stuff like that so I'm gonna show you with a with a test page here on how to access all that core WordPress stuff and then we're gonna do a database query from there all right so we're gonna create a new page here and we're gonna call it we're just going to call it test dot PHP okay we're gonna make some PHP tags here so we created this page test dot PHP and it's actually in the root or the public HTML folder of our website which is also the same folder that contains our Devi config file our duppy cron file it contain contains all these main files here right and so the one we're going to look at right now is the WP load file this WP load file if you bring in that load file into your page it basically brings in every bit of the WordPress core functionality right so up here on the top of our page we're gonna say require once and we're gonna say WP load PHP now we can say WP load PHP without changing any directories or anything like that because it is in the same directory the WP load file is right here it's in the same directory as our test dot PHP page alright so a good thing to do to make sure that it's loading that is just to say welcome and if we go to our idea Pro dot IO site and take test dot PHP it says it gives us it returns welcome here now if for whatever reason you don't include that load file correctly let's say we go back a directory it still should work if we go back a directory because it's still the root no it didn't it says the page isn't working now that's reason why I put this here to make sure that that load file is working because if you just used include sometimes it won't give you that error right so it's because we're including it and we're actually not loading this because that file doesn't exist in that dot dot slash directory so if we include it like this it will work but the problem is because we've included it if it doesn't find that file including we'll give us a warning if we have debugging turned on but if we don't it will it'll just say it'll just give you blank they won't they won't tell you that it's not included all right so I like to use require once now don't use required because if you build multiple pages and you bring in a page that says require once and you've already required you've already included that WP load PHP then it will duplicate again and it will it will cause some errors so if you say require WP load then it's going to if you have another page you include that also has require WP load it can cause some problems so require once means if it's already been required if it's already been included don't include it again all right so now that we have required once WP load we will check this and we get our welcome message so now we know it's working all right okay so now by just doing that right there we've included in the root of our website on this page all the WordPress functionality now what I want to talk about and this is important to to know what this is is the dopey DB object and what that object does is it is a database connection to the WordPress database connection right so if we do a print Park which is going to show us the array or object and then we do WordPress DP it will actually show us all the details of the WordPress database right we're not worried about that or not so we want to make sure that that's included and that's working now if you're writing a function let's say we we make a function here and test function okay inside of a function you need to clued to a global WordPress DB like that so that you bring that WordPress database object into that function okay so we're not going to do that right now we're just going to do do a get variable and we're going to say equals WordPress DB object get results now we're press gives you this is a wordpress property of the WP d wordpress object or that simple logging so get results is kind of a default there's get results search get row there's get there's bunch of different ones that you can use but if you just use getting results it'll give you the ability to do basically any database query that you want I don't recommend it on all of it I recommend using the different functions that WordPress gives you and the different properties inside this WordPress database the way they're used and you can find those different properties inside the WordPress codex alright so inside of this now we can write our query so we're gonna say select select star come on select star from and now we're going to call this WordPress DB object again and we're gonna say prefix now in your WP config file you should have a prefix defined for the database and typically when you install WordPress the prefix is WP underscore and then the table name that changes depending on who installed your WordPress what your hosting is sometimes GoDaddy Bluehost stuff like that they will give you a very random prefix and that's good for security if your WordPress website has WP underscore in the table structure I would suggest changing that and I can do a video on actually changing that later in another in another video so the WP d be pre and then we're gonna say posts right so now we've got our that's our database query there and so now we can show we can do a print our which I always use the the pre in the close pre to kind of show that that array is stretched out or that object stretched out alright so we're going to do inside here we're gonna say get and so now we're gonna go to our page and we're going to refresh the page and now this is basically an array and then inside of each one of these array items is an object and this is the post author post date post GMT which is general mean time for the day post content post title I hope you guys know what each one of those are so as we go down through here we're going to see images that probably don't exist anymore because if there's a test site if these images did exist they would actually show up they're so different just so this is just the different posts throughout the site so we scroll all the way through there just keeps going and going alright so basically we did a we select star from posts now that gave us every post in the database now posts are posts pages attachments inherence where it inherits of post revisions if you've done a revision or a post just pulling all the posts is going to pull everything from the post table and it can be very very large so what we want to do is we want to add where post type is equal to post right so now if we do that and we come back here and we refresh it now it just gave us the posts that are a type of post now if we want to change this we could change it to page and now that will give us just a list of the pages inside of WordPress all right so we could say give us the attachment riposte type is equal to attachment then we come back here and refresh and now this is all the attachments inside of the post table right okay so we're gonna go back to post clips we're post type is equal to post and post status is equal to publish so now what we're doing is telling it to give us all the posts that are published so we're gonna do refresh so now this it changed a little bit from our last query and that was a ditching for some reason alright so now it's just gonna give us the posts that are actually published and if they're in draft or in trash it's not going to show up and we could say draft let's see if we have any in draft and we don't so we're gonna go back to publish alright so now we have a total of seven and that's because this starts at 0 so 0 1 2 3 4 5 6 7 so key 6 is actually the number 7 in here alright so that's how quickly I'm not sure what that even was oh that's not watch so it that gives us a list of the posts and the salmon that we have in there so let's go back and look and we have 1 2 3 4 5 6 7 all right so whoops wrong page so this is a list of the the posts that we pulled from the database ok so now let's go into this fellow world and we're gonna add a add custom fields in our custom field and we're gonna call this let's say featured and we're gonna do a value of yes add custom field and we're gonna update so now if we do a pull this over here so now if we do a refresh it's still going to give us the same post because we haven't changed our query and this query we can't just say we're featured is equal to yes because when you actually add a custom field like this in a post or page or custom post type it actually adds it as a meta key so this is the name meta key and the value meta value all right so now we need to search for coasts where meta key is equal to featured and value is equal to yes now there's a couple of ways to do that so I'm actually bringing this down I like to kind of bring this down to individual lines here to give me an idea to it makes it a little easier to manage and then I can kind of put it back in place later on so let's say an post types equal to alright so now what we've done is we've kind of broke it out into select star from block all right so what we want to do is we want to add we want to name our post table since this is WP d prefix posts we don't want to use that throughout our query so we're gonna label this as a specific name so select star from posts as posts where posts dot post type and posts status is you want to publish and then we're gonna come up here and say posts dot star now we can change this to where we say ID post spot post title dot post content we can specify each column that we want to pull but we want to get the entire post so we're going to say post dot star all right so now we want to add a meta table so that we can pull the meta fields right so what we're doing that is we're under a left outer join and it's gonna be the WordPress DB prefix post meta meta so what that is is it's the it's a left outer join for the post meta table as a meta is what we're gonna call that that table on now this is telling it that we want to join the left join the outer table post meta with the table posts alright so on post ID equal to meta dot post ID because in the post meta table the meta ID is the main ID the primary unique ID the post ID is labeled as post ID so we're going to join the posts table with the post meta table on ID equal post ID right and meta dot meta key equal to and we're going to come back here to our featured meta key equal to feature so basically that saying get the get the row from the meta table where medics meta key is equal to feature now down here so let's say in this we say featured is know so we want to make sure that and we want to make sure we don't pull the ones that say no and we want to pull the ones that say yes all right so then down here in the where clause we're going to say post post type equal to post and post post as the is equal to published and meta meta value is equal to yes now we can just pull the posts that equal that where we can actually pull the meta died meta value as featured featured right so now we're going to save that and we're going to run this page and it gives us one post that one post is the only post that we've added featured as a custom field and now we've pulled that here where it says featured is equal to yes so let's go back in and let's go to another post burg is awesome or go to this post alright and we're gonna say this is a list of just random stuff that we've done over the time where it's feature that LC feature doesn't show up so let's enter a new one notice I featured and we're gonna say no let's say no on this one and then we're gonna do let's go back to posts it's just a test post alright so we're gonna say enter new featured and this one we're gonna say yes okay so no update so now our query will pull the two posts here zero and here's one these are the two posts that say featured is equal to yes so a featured is equal to yes here and featured is equal to yes here now if we just wanted to see even all the posts where that meta key featured exists then we could take out this here save it and refresh and now it's going to give us all all the posts with featured either blank yes or no so but we want to make sure that we pull let's just pull the ones that let's put that back in place and let's just pull the ones that are no no all right so out that gives you a little bit of an idea of how to do a custom database query you guys didn't give specifics on what you were actually querying from the database or what you needed to create from the database so I just wanted to give a general idea of how to do a query you can also do inserts and updates you can do basically anything that you can do outside of a wordpress database query so let me know if you guys have any questions make sure to LIKE the video and we'll see in the next one thanks

Channel: Joshua Herbison

Views: 42,473

Keywords: wordpress database, custom database queries, wordpress database queries, custom wordpress queries, wpdb, wordpress codex, wordpress database object, wordpress wpdb

Id: IM6zohH4B3E

Length: 24min 39sec (1479 seconds)

Published: Wed Oct 23 2019