 and thus indicates the start of the online workshop. The Zoom lady has given us the go ahead. I think we need to give her a name. Welcome everybody who's joining us here today. As you join, please do let us know in the chat where you're joining us from while I do my usual introductions. So if you've never seen me before, never met me before, my name is Jonathan. I live in Cape Town in South Africa. I am a developer educator at Automatic and I am sponsored to work with the training team, the WordPress training team. So they are the folks that manage the Learn WordPress platform. We create tutorials, we create lesson plans, we create courses and we run these online workshops all around learning about WordPress, how to use WordPress, how to build with WordPress, how to extend WordPress. And my focus specifically is on all of the coding related tasks within a WordPress environment. So primarily extending WordPress using themes or plugins, but sometimes we also talk about core things and various other topics around that. So we have joining us today. We have Jim from Philadelphia, Pennsylvania in the U.S. We have Eagle from Santa Cruz. We have Nicola from Serbia currently in Greece on vacation. I hope this counts, it certainly does, Nicola, welcome. We have Jean from Northwest New Jersey, Valerie from Oklahoma, Gerald from Arvada, Colorado, Arvada, I don't know if I'm pronouncing that correctly. Mark in Issaquah, I think, I hope that's correct. Lisa from New Jersey, Tracy is from Reno, Nevada, Nevada. Tracy, I would just mention that because I sent you a direct message before this started, your messages are currently only going to me, so you might need to click on a little dropdown and switch it to everyone in the meeting for folks to see your messages, but I can see your message. Rico is from Switzerland, welcome Rico. Michelle from Atlanta, Georgia. Stefan, I assume from Germany, or maybe Steven, I don't know, there's different ways of pronouncing the spelling. And DK or Doug from Pacific Northwestern US, good morning. Oh dear, Tracy has just reminded me that I have not made him co-host, so give me one second, folks, good grief. Let me just do this here quickly. There we go, I'll make Tracy the co-host, there we go. Okay, so while we're on that topic, thank you to Tracy who is co-hosting with me today. He will be keeping an eye on things that letting folks in as they need to join. If Tracy has just let you in, please let us know where you're joining us from in the chat. And we will move on from there. Okay, so today we are going to be chatting about the WordPress database. We're going to be taking a stroll through the database. It's not going to be a very heavy coding session this week. It's more going to be a little bit of theory. We do have some coding that I will be doing, which you're welcome to join me with if you want to fiddle around with a few things. I think my microphone should still be working. My video might have stopped, so let us restart my video. Excellent, so what's just happened? Those of you who've been to some of these workshops of mine, you know that South Africa has load shitting. Load shitting just kicked in and my backup battery kicked over and when my backup battery kicks over, my Mac hub sort of resets itself and everything dies and then comes back again. So it's one of the reasons why when I know there's load shitting coming, I tend to, as my son says, waffle a little bit more than usual because I'm just sort of waiting for that kick over to happen because I would hate it to happen while I'm actually presenting something. All right, as I was saying, we're doing a bit of a stroll through the database today. We will be doing some coding if you would like to code along with me, but nothing as code heavy as it has been in the past. All right, a few announcements as always before we get started. Again, welcome to everybody and thank you to Tracy who's co-hosting with us today. If you can't see my screen right now, you should see a slide that says announcements and then it has all the announcements that I'm running through quickly. If you don't see that, please let me know either in the chat or unmute and let me know and then I will re-initiate the sharing session. We are presenting in focus mode. The focus mode enables myself and Tracy to see you all and for you to see us, but for you not to see each other. And this is just to prevent any instances of Zoom bombing causing any kind of problems. We haven't had as many issues of Zoom bombing as we had this time last year, but they do still happen every now and then. It seems to be very specifically in the afternoon or the evening US time. So we think it's a bunch of high schoolers or teenagers who are just kind of hanging around. They don't seem to be around this time of the morning US time. So I've never had any Zoom bombing issues, but it's something that we do do just to protect ourselves as presenters and everybody else in the workshop. Thank you DK and thank you Lisa for confirming they can see the announcements. Then as always, if I start speaking too fast and I'm rushing off and I'm going off in different directions, please do let me know primarily so that the captions can keep up. The automated captions do sometimes struggle with my Cape Town accent. So if anything is not clear or if I'm going too fast, please just let me know and I will slow down. You're welcome to pop a message in the chat. You're welcome to private message me if I'm going too fast. I don't mind either way. You're welcome to unmute and say, hey, slow down and let me know. The other thing that I forgot to add to my announcements for some reason is that if I'm going to, if I, sorry, not if I'm going too fast, I've just said that. If you have any questions, you are welcome to ask questions at any point in time, but I do just ask that your questions, if your questions are not specifically related to what we're chatting about on screen at the time, if you could keep those questions for breaks that I do leave for questions or just post them in the chat. And then when we take those breaks, I will look at those questions. Otherwise, if you have something specific about what I'm discussing on screen, if it's not clear, you're welcome to post that question in the chat or unmute and let me know. Tracy will be keeping an eye on the chat for any questions like that. I also keep an eye on the chat. So do let me know if there's anything that isn't clear, if I'm going too fast, anything along those lines. The other thing I want to do, I was not able to share the slides as I usually do in the meetup.com events page beforehand. So I'm going to share the slide you are on now in the chat. This is the presentation slides that you're looking at. If you want to have them open on your side, you're more than welcome to. Between Tracy and I, we will share any relevant links as we go along. And then what I'll probably do after the session is I'll upload the slides to SlideShare like I usually do and then include them in the meetup event. All right. And then I will be posting the session to WordPress TV afterwards, probably during the course of the day tomorrow. If you're looking for any other WordPress-focused content, please do visit learn.wordpress.org. And then I wanted to also just mention something that was recently mentioned to me and I'm just going to do something quickly. We have, or at least the WordPress project has recently launched the WordPress Developer Blog. And so this is a blog that is kind of along the lines of what we do in these workshops every week. It's very much specifically developer-focused. It's very much specifically focused on folks who are extending WordPress through themes, through plugins, those kinds of things. For some reason, the page is taking forever to load for me right now, which I'm not sure why that is, but the URL is developer. Maybe it's because I'm going, let me just check here. developer.wordpress.org and it should be slash news, but it seems to me that developer.wordpress.org might be down, which is kind of scary. Oh, no, there it is, there it is, there it is. Okay, so that worked. Let me go back here to developers. Okay, that's the developer documentation. And I'm just going to click through to reference and I'm going to add news here. There we go, there we go. Okay, it's working now. I don't know why it wasn't working earlier, but this is specifically the WordPress Developer Blog and all of the blog posts on here are specifically aimed at developers. So every month there is a what's new for developers blog posts. So it talks about upcoming developer features that are coming to WordPress soon or things that have changed or updated. And then there are more in-depth articles about things like navigating the block editor handbook and the style book that was recently released using block inspector sidebar groups and various other things like that. So if you're somebody who prefers to read your knowledge, I highly recommend subscribing to this blog. All of these articles are written by contributors to the project. They are curated by a group of developers, developer advocates, developer educators like myself. I've written an article for the blog as well. So I do recommend checking that out if you're looking for interesting bits of information with what's happening for developers in the project. All right, let us move on to our learning outcomes for today. So as I mentioned, we're going to take a walk through the default WordPress database schema today. We're just gonna look at all the tables that are there. There's a handful of tables. We're gonna just dive into what each of them does and how they work. If you have been developing in WordPress for a while, you probably know all of this. I learned some interesting things about the database this week in preparation for this workshop, one or two interesting things that I didn't know. So hopefully there will be some interesting bits of information there for you. We will also look at how you could interact with these different database tables. So we'll be diving into some of the functions that are available to get data and insert data and do various things with data. It won't be a deep, deep dive. My hope is that I will show you how I search for information when I'm looking for these functionalities and you will then be able to do it yourself moving forward. And then last but not least, if we have time, which we should do, we're going to briefly look at how you can create and interact with custom database tables. If you have been to any of my workshops in the past, you may have seen me do this. I use the DB Delta function to create custom tables and then the WPDB object to interact with those tables. So we're gonna cover that again, just to sort of remind ourselves how that works. Again, it won't be a deep, deep dive. It'll just be giving you some documentation to read and some information on how it all works. Right. I recently decided to add a requirement slide to my slide just to make sure they're all on the same page. If you want to code along with me today while we're doing this, if you wanna do things on your side along with me, you will need a local WordPress install, you will need a text editor of some kind and then you will need a way to access your WordPress installs database. If you don't have something like PHP My Admin or Adminer is the other one that a lot of the local WordPress installs use, you can use a plugin called SQL Buddy. I did mention this in a previous workshop once before. It is a plugin that installs on your WordPress site and then gives you access to your WordPress database. So I will show you very quickly here in my LearnPress test site, I have the SQL Buddy plugin installed. And if I activate it, it adds a menu item to the tools menu. If I click on SQL Buddy in the tools menu, it basically loads up all the tables and then I can click through and I can view things in the tables. I'm not going to be using SQL Buddy today, I'm going to be using PHP My Admin, which is what I have installed on my local environment. Here is my LearnPress database and here are the tables there. So that's what I'll be using today. If you don't have that SQL Buddy, also it works. And then last but not least, we need the Learn, sorry, the WP LearnDatabase.php script. So I'm gonna paste the link to the GitHub guest in or just how you pronounce it in the chat. And what I want you to do is to, if you would like to follow along with me, we're basically gonna create the script and this is going to be our playground today, our test bed. So what I'm going to do is I'm gonna copy all of this code out of the script. So if you would like to do this along with me, you're welcome to. I'm going to open up my VS Code instance, my Visual Code Studio instance, and I've already got the LearnPress folder opened and ready to rock and roll. Inside the roots of the LearnPress directory, I'm going to create a new file and I'm going to call it wp-learn-database.php and that's the file that we're going to be working with today. I'm going to create that file and then inside of that file, I'm going to paste all of that code from the GitHub guest. And Lisa, I did see your question there. We'll get to that in a second. And basically what this PHP script is, it's an old school PHP scripts. It has some HTML to set up your basic HTML structure. It opens, it has a header, which we'll see just now in the browser. It has an opening div. Then it has an opening PHP tag. And what it's doing is it's requiring the wp-load.php file, which is this file over there. And if you remember from the WordPress lifecycle workshop we spoke about this file being loaded from the index.php and it sets everything up basically. So by requiring this file, we're basically allowing this standalone script to access all of WordPress functionality. Then I'm just creating this data variable over here, which has a string called, that says hello WordPress inside of it. And then I'm echoing that string variable to the screen. And then finally closing the HTML content there. And that's all we're dealing with today. So this is a completely standalone script. I wouldn't ever leave this on a production environment or a test environment anyway. This is just for playing around with today. If you are doing this on any of your local environments, feel free to delete the script afterwards, but we'll be using this to fiddle with, not fiddle with, but to interact with the WordPress database of it today. To see this running, what you then need to do is switch to your local WordPress install in the browser, whatever the URL is. It could be a dot test domain or dot local domain. It could be a local host domain, whatever that domain is. But then just make sure at the end of the address that you use to access your local site, you add a forward slash. And then there we're gonna type in the name of the file we just created. So in my case, wp-learn-database.php. So what this does is the browser is now going to try and access that PHP script. And it should give you something like this. So this is the H1 tag that we output there. And this is the hello WordPress that we output in the PHP. If you are following along with this and you're not seeing that screen, please let me know in the chat and we'll help you out with it. But otherwise I'll have a look at Lisa's question and see what, which is, so is SQLBuddy mostly for development sites not production sites? So the answer to that question is I, I have used SQLBuddy on production sites, primarily in instances where it is either very difficult to access the database on the production environment for some reason. Or I just quickly wanna have a look and see what's going on. Depending on the project, depending on the requirements, sometimes you might be in a scenario where a new customer contacts you, a new client contacts you and says, I want you to quote on something and they just give you access to the WordPress dashboard, administrator access. And you don't have access to the hosting platform, you don't have access to the database. So there SQLBuddy is a perfect solution. Load it up quickly, access the database you need to make sure you don't change anything and then uninstall it before you go. So you can use it on production sites, but I wouldn't recommend leaving it on production sites. And Lisa says, I'm often nervous when I see a client with any plugins installed for file manager or database access via the dashboard. Maybe I'm needlessly worrying. No, I would not say you're needlessly worrying. In my limited experience, I've only worked as a freelancer for a number of years, I haven't been doing it for all my life. But in my limited experience, when I find a site that has these kind of plugins installed and active, one of two things has happened. Either the client or the customer has tried to figure things out themselves and they've installed these plugins to do that or some previous developer has installed these plugins and left it active. My personal rule, and this has just been something that I've always stuck to, is if I'm going to install this kind of plugin, I first let the client know that I'm installing it so they're aware that it's being installed. I use it to inspect and view, but never to edit. And then I remove it, uninstall, I deactivate and delete the plugin before I finish doing what I'm doing. That's just a general rule to follow. The nice thing about WordPress is there are plugins to do almost everything, but it's also a possible downside. So if you do find clients that are like that that have those plugins installed, do let them know that they're not a good idea to leave them installed and active and maybe convince them to uninstall them. I feel the same way about plugins that allow you to upload PHP code and execute that code in the plugin. I don't like those either because those are a possible security risk. I would rather have that code sitting in a plugin somewhere or sitting in a theme file somewhere so that there's less of a risk. Okay. Tracy says you may also access the database when using local WP for development, that's correct. I don't use local, but yes, I don't. Local DevKinster and one or two others, they have a built-in database access option. Okay. I'm gonna take a break and grab a sip of coffee and just give anybody who's still needing to get up to speed if they want to get up to speed with the script. Also just to force myself to take a break and slow down and then we'll continue after a short break. That's correct. Local WP uses Adminer, yes. There's actually, it's funny enough there's actually a similar software package like Adminer or PHP environment called SQLBuddy. It's separate from the plugin, it's two separate things but they're called the same name. So there's a couple of them out there. All right, so let us get onto the database. So I'm literally just gonna run from the top down. I'm gonna try and kind of group tables that make sense together. And so the first thing we see in our database are the WP comments meta and the WP comments table. This is fairly straightforward. The comments table, WP comments table literally just stores the comments anytime somebody comments on a page or a post or whatever the case may be in the comments table. Now in a default WordPress install I'm gonna switch back to my dashboard so we have that open in a tab as well. In a default WordPress install it sets up a default Hello World post. It sets up two default pages. It used to just be the sample page but now recently the privacy policy page was added as well related to GDPR things in Europe. And it also sets up a default comment which is a comment on the default post. So as you will see, if we look at the comments table and the comment meta table the comment table is the table that stores the data about the comments. There is currently one row in that table. The comment meta table stores any kind of additional pieces of data about comments in the comment meta table. This is a common schema that you will see in the WordPress database. A lot of the tables have a meta table attached to it. I'll run through them quickly. Comments has comments meta. Posts has post meta. Terms has term meta and users has user meta. Now the reason these tables exist is because to make WordPress as extensible as possible as extendable as possible. The meta tables exist so that you can attach additional data onto the parent record in a key value way. I'll dive into that a little bit more when we go and look at post meta in a second. But for now you'll notice that comment meta has no meta data attached to it. So comments by default don't need meta data. So the comments already have a comment post ID which is the ID of the post that the comment belongs to. It has a comment author, comment author email, URL, the IP address if it was able to capture it, the date that it was created. Now obviously there's no IP for this one because it was generated when the WordPress database was installed. And then the actual content of the comment. And then there are things like comma, approved and various other fields attached to the comments. So that's what the comments are. So anytime somebody comments on a WordPress post it will store that record in this table. Now the nice thing about WordPress is that it has a lot of built-in functions for accessing these records. So if we hop on over to, and this is why I was nervous earlier because if developer.wordpress.org wasn't loading I was gonna have a big problem in my workshop today. But if we hop on over to developer.wordpress.org and if you click on the code reference and click on visit the reference over here. This is literally just a list of all of the functions, all of the classes. You'll see you can filter by functions, books, classes, methods, all that kind of thing. And then it has a search box at the top. So anytime you want to deal and this is the kind of the great thing about WordPress anytime you want to deal with and just go back to the database here anything related to comments you can look for functions that have the word comment in them. So WordPress has been very cool about the fact that any kind of functionality related to the data type or the data table uses that data table name in the functions that are used to retrieve data from that table. And WordPress is nice in that it keeps it kind of simple. So there is a get comment function which allows you to get a specific, there's a filter that fires when a specific comment is retrieved. There is also, I'm gonna jump back one here. There is also get comments which is a function that allows you to retrieve a list of comments. So now we can use this function to very quickly access our comments in our script. So I'm going to switch over to my script here and I'm gonna change this data from Hello WordPress and I'm just gonna say get comments. And my Visual Code Studio is set up so that there's auto full for the function name. So it finds it for me, which is nice. I'm gonna do a session on that. We spoke about that last time about doing a session for setting up my VS code with all of the additions that I have but basically this get comments function if we read in the documentation, I'll share this with you in the chat if you would like to open it up on your side is it retrieves a list of comments. It has some parameters that you can send it and then it returns a list of comments or a number of found comments if the count argument is true. So we haven't specified count to be true so it's just going to return a list of comments. And usually in these functions in the developer resources, there are some examples of what you can do. So here's an example where you could say your arguments are any comments attached to post ID and then return the count or whatever the case may be and then you can carry on from there or you can do something like this where you can say use a date query and get comments from the last four weeks, get the comments and then loop through there. So it's this for each loop that we're gonna use for each is a way to loop through a collection of data in PHP known as an array. So we can use that over here so we can say for each comments as comment we'll just need to change this variable from data to comments. And then we can do something like this. We can say, let's say echo a single comment so it'll loop through all comments and let's say we want to echo the comment content. That's a good idea. So comment content and you'll notice that my visual code environment is kind of pre-giving me some examples and you'll see that the field name here comment content exactly matches the field name comment content in the database. So that's something you could generally rely on when you're using internal functionality internal WordPress APIs. The field that exists in the table will typically be a field that's available on the list of objects or the list of arrays that are returned in your code. So if we hop back over here we'll see comments, it'll get the comments. We could pass in arguments of the post or by date or whatever the case may be. I'm leaving that empty so it's gonna get all comments for me. Looping through the comments. If you've never seen a for each loop what this essentially does is go through this array of data and then return each item in the array as this single variable comment here and then I can do things with it. So if I load this on my browser right now I should now see something to do with comments on my screen. So let's have a look and there we go. It's picked up the first comment which says, hi, this is a comment. If we check the database, that is exactly what it is saying. Let me just do a quick edit here so we can see that. There we go. Hi, this is a comment, all the information with the link so we know that's working. And so we can interact with comments in that way. What we can also do is we can do something like insert a comment. So again, WordPress is very nice. It keeps things very simple. So the keyword is always going to be generally insert and then what generally exists and I wanted to sort of do this search quickly. Let me do that again. Insert comment, there we go. Notice when I do the search the first one is WP insert comment but it doesn't have the open and close brackets at the end. So that is the documentation indicating to me that this is probably not a function. This is probably an action or a filter hook. But if I go down to this one over here which has the open and close brackets that indicates that's probably the function that I want to look at. So that's the one I need to click on. And you'll notice that the WP insert comment function also has a WP insert comment hook. So inside of WordPress, when a comment is posted on the front end that it calls the function to insert the comment and then that also fires the hook. So you can hook into that and you can do further things if you want to. We're not talking about hooks today but just interesting knowledge. So I'm gonna click on the one that is the function in the list and I'm gonna go to that documentation there and there it basically says inserts a comment into the database. And the parameters are your comment data. So the parameters are an array of arguments for inserting a new comment. And you'll notice that every single one of those arguments agents approved or the email IP, dah, dah, dah, dah. Again, matches to, let's go back to the structure, matches to the fields in the table. So whenever you're inserting a comment you can rely on those fields being there and being needed. The other nice thing about the documentation as always is it generally gives you some good examples. If we scroll down here, I'm gonna pop insert comments into the chat in case anybody wants that. If we scroll down, there's usually an example of how this would work. Here we go, Mahedi Faisal two years ago gave us a nice little example of what this might look like. Okay, Tarek says he can't see the shared screen. Thank you for pointing that out. It might not be just you but I know it's a problem that sometimes happens. So let me just stop screen share. In a sec, stop share and share my screen. And there we go. Okay, Tarek, can you confirm that you can see the screen now? Sometimes it does happen if you join the chat after it starts. So just let me know if you can see it now. You should be able to see the WordPress developer resources and the example code that I'm looking at. Okay, perfect. Yes, that's a known issue. Sometimes folks who join after the session starts they don't see the screen so thank you for pointing that out. So here's an example of the data array, what it could look like. So you've got comment post ID, comment content parent all these things and you can then insert a comment with that information. So as an example, looking back at the code if we wanted to insert a comment and then get the comments and echo them we could do something like this. We could say comment data and we'll make it an array. And it's going to need to see GitHub, GitHub Copilot is nice to give me some options here. You'll notice that it needs a post ID because a comment must be assigned to a post. So we'll just assign it to post ID one for now. We'll just give it a name of John Doe and we'll just give it an email of JohnDoe at gmail.com and then it might be a good idea to add a comment content as well. So let's say comment content and we will hello world, that's fine. So that's the comment data. I will share this code in the chat in a second and then we can basically just do something like WP insert content, sorry, not content, comment. There we go. We can pass in the comment data and usually what you would do if you are writing this kind of code is you would do some kind of check. You would check if the comment was inserted successfully and then if it wasn't you would do some kind of error checking whatever the case may be. I'm not going to use that right now. I'm just gonna insert it and just hope that it all works. It should theoretically work but that's what I'm going to do right now. So this is the code. I saw the question in the chat. I will get to that in a second but that's what that looks like. For some reason my auto formatting doesn't seem to be working today. So something is broken some way. I'm not sure what that is but anyway, not a major problem. So if I now refresh my window this video panel keeps getting in my way and we go back to my script page. Here we go and I refresh this. There we go. It's added the comment and then it's gathered the comments hello world and the original comment from the list. So I know that that comment has been inserted. If I check the database table and I browse there we go. There's my John Doe. It has been inserted. So that is effectively how you interact with most data tables in WordPress. I'm using comment as the first example because it's the first table that came up but most data types in WordPress have insert, WP insert versions, WP update versions, WP delete versions for each data type. We're probably not gonna have all the time in the world today to get to them but that's just good to know. Okay, I'm gonna just answer this question very quickly. What VS code or what VS code plugins do you recommend for WordPress development? That's a difficult one to answer. I actually don't use VS code for development. I use PHP storm and a couple of folks have asked me to do a video on PHP storm versus VS code and to go through the plugins that I do have installed for my VS code. I can however, very quickly show them to you but I can't spend too much time on it today because I wanna focus more on the database side of things. So let me just go to my extensions and the plugins that I have installed are the ES lint plugin. I use GitHub co-pilots because it helps me with example code as you just saw. I have the HTML CSS support installed for auto CSS intelligence for HTML. I have markdown linting installed because I write a lot of markdown in VS code. I don't really use the path intelligence that much but I do have the PHP debug, PHP doc blocker and PHP IntelliFence plugins installed for PHP and also PHP sniffer and beautifier. PowerShell is not for WordPress development that's for personal things. And then the WordPress playground for VS code is an extension that I'm busy playing with because I wanna do a workshop on that in the future. So the top ones that I use really are ES lint, co-pilots, CSS support, markdown lint, the path intelligence and then these four PHP ones, PHP debug, PHP doc blocker, IntelliFence and sniffer and beautifier. I am presenting a workshop on all of these in the future and I'll dive deeper into what they are but very quickly those are the ones that I currently use. Okay, so now that we've covered that I'm not going to go into the functions for every future data type and data table in WordPress now because we know roughly how it works. We'll just refer to it again. Hopefully I've inspired you to go searching for these functions yourself but essentially what you can do is for example if we switch over to the next table which is let's look at posts and post meta. Posts are what store all the information about your posts or custom post types. So your custom post types could be your pages, they could be your products if you're using something like WooCommerce or WPECommerce or one of those. The post table is usually the table that gets the biggest because it has the biggest amount of data because of the fact that you can register custom post types. The post meta table is also the table that is used along with posts the most. So if we have a look at the post meta table you will see that there is for example the WP page template meta key being stored on post ID and post ID three. You'll see there is an edit lock being stored on post ID one that's because I have possibly opened it. I'm not sure why that is there right now and then there's other things additionally added. So you'll see within the meta tables any of the meta tables so comment meta, post meta, user meta will get to intersection. It's always stored as key value pairs. And so what I wanna do is I wanna show you how you can access a post for example and then how you can access a post meta. So let's go back to the script and what I'm going to do here is I'm going to say I can do something like post equals and I think it's just get post. I think it's just get post and you pass in the post ID. Yes. So you can say post is get post and the ID is one. There's other ways you can do it but this is the quickest way to do it. So this will get the first post. And if we just echo the post and let's go where let me just find the let's go with post title. So we'll say post and we'll echo the post title. There it is. Then that will give us the post title. Then if we have a look at the post meta table post, okay, maybe post one is not a great idea. Let's use post two because that has some additional content on my side. So I'm gonna go with post two and then I'm going to get the meta key this underscore WP page template meta key. Now, normally you wouldn't try to access these meta keys that have it underscore in the beginning because they're sort of considered hidden meta keys. They won't show up in the custom fields but it should be possible to access them in the code. So in that instance, you would do something like this. You would say post meta and you would then be able to say get post meta and you would pass in the post ID and then you would pass in the key that you want to get in this case, WP post template. And then the third value is whether or not you wanted to be returned as a single string or as an array and that's for whether you have a array stored or not, I'm just gonna leave that out for now. So that's what we're gonna do. And then I'm going to echo that post meta as well. And then I'm just going to make my life a little bit easier here. I'm gonna put some break tags at the end of this. There we go. I'll put break tag but that'll do and I'm gonna put a break tag there. Okay, this is what the code looks like. I'm gonna show it with you in a second and then I'm going to refresh the screen and we're going to see what happens. So there we go. So it got the post title from the first post and it got that meta key value from that first post. So if you need to work with that kind of data this is how you can access it in your code. Notice how when I echo the post meta I echo the post meta and then I'm passing this array notation. I want the first item in that array. Now the reason for that is when you query the post meta table by default it returns that data as an array because you can store both strings and serialized arrays to your post meta table. So the third parameter that I showed you earlier is the one where you can specify as true. And what that basically says is only return I expect because I know it's a single string I'm expecting just a single value. So return it to me as a single value not as an array. And then I don't have to do that over there. I can just say echo the post meta. If we test this now we should get the same results and we do sample page and default. Okay, similarly you can insert post meta. So just like you can get post meta you can insert post meta you pass in the ID and the key and the value you want to insert. You can update post meta. You can also insert a post. So just like you can do get post you can also pop on over to the documentation and it's just right up to the top here and let's do a search. So let's say WP insert post. Let's do a quick search on that. And here it is just like it was WP insert comment you can WP insert post. And again, it will require an array of arguments. The arguments will be an array with all of the fields it's all listed in the documentation and you can then insert posts in that way. Okay, any questions around posts and how to interact with them and how to work with them before we move on I'm gonna grab a quick break and a sip of coffee and then we'll move on. Okay, now you might have noticed that there was a table before posts that I didn't talk about. And that was there was actually two of them. There was links and there was options. Links is a table that doesn't really get used anymore. It was a piece of functionality that existed in WordPress up until about WordPress 3.5. It was something that folks used to use I'll share this link with you in the chat. Folks used to use this to create what they call blog roles. So for example, I have a blog I'm busy blogging about things. Adrienne is one of my friends, she has her own blog. I would then add her link to her blog on my blog site. And then when folks visit my site they would see a list of my favorite links and they would be able to then click through. This is before the days of social media. This is how we shared knowledge with each other. This is how we showed who we followed, who we liked. And around about 11 years ago when social media kind of really kicked off it was decided to remove the link manager from call. So it was removed. Let's put it this way. It wasn't removed. The functionality still exists. All the codes still exist. It was just basically turned off. And the table, sorry. The table still exists in the WordPress database. It is possible to turn this functionality back on if you would like to. It is essentially a plugin called link manager which you actually, I tried this today. It's very difficult to actually install this plugin from the plugin list inside of your WordPress site. So this is the plugin. As you can see, the 0.1 beta was released 11 years ago. That's how old it is. What is interesting though is it has 40,000 active installs, 40,000 plus. So some people out there are still using this functionality for their blog roles. But if you try and install this plugin from your add new plugins page. So if I click on add new over here and I click link manager over here. It is not the first one that shows up by a long shot. It's not the second one or the third one or on the second page or on the third page. And I didn't have enough time to go through every single page and find it. So what I did was I just literally from here, I downloaded the plugin zip file to my desktop and then I can manually upload it here. So I'm going to do that right now. There it is link manager.zip. It is a very simple plugin. It literally is about four lines of code maybe even less than that. It essentially just re-enables that link manager functionality. So once you install it and activate it then you will see a little links menu will pop up. There it is. A links menu pops up in my menu. And then I can add my links here just like I used to be able to before the change was removed. And it's effectively name, web address, the description, whether it has any categories and you can basically build up your blog role from there. So it's been removed from WordPress but because of WordPress commitment backwards compatibility, you can re-enable it and the table is still there. Okay. Moving on to the options table. The options table is used across WordPress so storing all kinds of options. The options table is very similar to the meta tables in the fact that it is key value pairs of information. So you will have an option name and an option value. The first two options that get set up in your WordPress install are the site URL and the home URL. And that is based on when WordPress installs, it picks up what the site URL is and then it saves that to the database. If you don't know why that is, it's so that when it references or when it puts down images or any kind of links or anything, it'll use the full path to that file using your URL as opposed to what's known as a dynamic path. This site URL and home URL is also one of the reasons that if you ever migrate sites from one domain to another or from stage into production, your process also needs to include updating the site URL and the home URL often, not so much maybe the home but generally the site URL to point to the new direction. And just as you can with comments and posts, you can also get and update and create options. So again, you can search for something like get option and there is the get option function. You pass in the option name and if it has a default value or not and then you will retrieve that option based on the option name. So let's have a look at the site URL option. So if we pass an option name, we should get a value. Let's have a look in the code. Let me switch back to the S code over here. Let's take out all this post stuff. So let's say site URL equals get option and we can say site URL. We don't have to specify a default if we don't want to and then we can say echo site URL and that should give us the URL on the page. I'll pop this into the chat if folks need that code but let's have a look at that and see what it does. And there it is. It can get the site URL in the same way that you can get it. You can update it as well. So there are update option is an update option function if I could spell it correctly today. So there's update option it allows you to update the option. You can add options with blank default values and various kinds of things. Options are often also used with what is known as the settings API. So if you have a look I'll take you back to the developer homepage and I'll take you through to the common API section and I'll link to the settings API. There we go and I'll share this with you in the chat. So the settings API allows you to create admin pages for settings. Essentially, I'll show you a simple example of what a settings page looks like the general settings page. Every one of these fields and we just saw that the WordPress address for example is one, the site address is another are stored as options in the options table. So the title would be stored there the tagline would be stored there. So the settings API is what manages the building of this page and then the options API manages storing the data from settings to the options table. So if you're ever building a plugin and you need settings for the plugin you get to know the settings API which you can find here and then also get to know the options API which is how to actually store and retrieve and update that data. So there we go add option, delete option can add site options and delete site options. Those ones are for multi-site. Okay, any questions on options before we move on to the next set of tables while I grab another quick sort of copy. Okay, so I'm gonna kind of run very quickly through the next bunch of tables because they are... We already understand how to interact with these things. So if we look at terms and terms meta and term taxonomy and term relationships basically these four tables manage your categories and your tags. So when you create a category for posts when you create tags for posts it's these four tables that manage it just to show you what's stored in these tables. So in the taxonomy table it stores the type of taxonomy. So it's either a category or it's a tag. The terms table stores the different in this example category type. So we have in a default WordPress install we have an uncategorized category. So because there's a category called uncategorized there is a taxonomy record called category which is the sort of the group taxonomy. And then the term is the actual category name that gets created. If we go to... Let me just hide this control again. If we go into our database sorry into our WordPress site and we click on categories for example there's uncategorized if I create a new category and call it say general for example, let's see what happens in the database. So in the taxonomy table you'll see that a new taxonomy type category was added with a term ID of three and a term taxonomy ID of three. If we go into the term table we will see that there the general term was created term ID three and then the relationships table is what manages the relationship between the two. Now if you want to listen to an interesting interview about these things there is a... I'm gonna find the URL quickly make sure I've got it here. Maybe it wasn't there. Okay, give me... Okay, so the book is called milestones. It's the story of WordPress and it's the first 10 years of WordPress. And if I open this up there is a link in here somewhere of all the interviews that were written. No, not that one. I want number two. I want number one. This is the building blocks one. I want the first one. Hang on one second. I'm trying to find the right repository here. I think it's this one. Yes, here we go. Milestone is the story of WordPress. Let me share this with you. This is basically a book on the first, I think 10 years of WordPress. But then I want to show you the interviews that were conducted as part of this project. This is where it gets interesting. There we go. It's archive.wordpress.org slash interviews. And one of the interviews with Mark Jayquith. Let me find it here. Taxonomy. So this one over here. The one that is 119 minutes long. He actually talks about the taxonomy system. And he talks about how it evolved. And he talks about how it's slightly over complicated to kind of understand and work with, but it works. So it's an interesting interview about how taxonomies work and how they fit together. It's something that I fully still to this day don't have in my brain. It's one of those things. I trust the WordPress developers when they wrote it and they built it, it works, it works well. So I don't tend to try and remember how it works. What I do tend to try and just remember is that the term is what stores the actual category by the name that I've created. So if I'm looking for a list of terms, I will look for get terms as a function. Whereas the taxonomies are the sort of the parent type, if you will, for the different types of terms. So taxonomy will be category or it will be tag. And then the different categories inside the category taxonomy will be general and categorized, whatever the tags might be. If you're blogging about food, your tags might be bread, cheese, milk, whatever the case may be. They all fall under taxonomies. Sorry, they all fall under terms. They just have different types of taxonomies. Okay. And custom post taxonomies are stored in those same tables. So yes, correct. Any custom taxonomies that get created are stored in those tables as well. Okay. And then last but not least, we have the users and the user meta table. Very straightforward. Stores information about users, stores user meta as needed. If I have a look at my current user table, there is just the admin user that I created. You can see that the user pass is currently stored as an MD5 hash. There are plugins to change that to use any of the other PHP encryption methods. That's basically the user table. And then the user meta table that stores additional information about the user. So things like what capabilities it has. So this user has the administrator capabilities. What session tokens are enabled for logging in and staying logged in and various other things. And just as you could with posts and comments and all of those things, you can, let me jump back over here to the developer code reference. As you might have guessed, you can just call get users, for example, there's the get users function. Exactly the same as get posts and get comments and get terms and all of those. You pass in a list of an array of arguments and you return your users that way. You can also use a meta. I'm not sure if it's single. There we go. You've also got just like you've got post meta. There we go. It's been deprecated. So we get the new one. Just as you have post meta, get post meta, add post meta, update post meta, you also have user meta related functions. So get user meta, update user meta, add user meta and those kind of functions. That to me is one of the things that I like about WordPress is they've kept those functions very, very easy to remember, very straightforward. You know that if you're going to be getting stuff, you get the name of that thing. You pass in an array of data, you get it back and you work with it. Okay. Any other questions around all of those tables that we've covered, anything that you've seen, anything that you... Sorry, let me jump back to here. Anything that didn't make sense to you in these little code snippets that we were fiddling with. Okay. Most of the questions are coming through options versus settings. Okay. So options versus settings, as I mentioned, options are where the data is stored and how it is stored. Settings are what allow you to build settings pages. So typically what you would do is you would register a setting and then you would register a render method for a setting and that would render either a text field or a text box or drop downs or whatever. And then when your settings get saved, you will save them as options. So generally when you're building plug-in settings pages, you use settings and options together. Settings is for managing the page, the visual interface, the saving of it. Options is how the data is stored in the database. I do recommend, if you want to learn more about settings and options, I do recommend reading the documentation. There's a great... I think I did share it earlier, but let me jump back there quickly. Can I just say while we're talking about it, I love how all of the learning material on the WordPress.org site is under a header called learn. So if you want to go learn WordPress, that's right at the top. If you want to go documentation, it's right there. If you want to go to the forums, you can go to the developer documentation. I spend all of my day in this dropdown here. So I love that update to the menus. But if you go to the developer resources page, developer.wordpress.org, and you click on the common APIs, go and read about the options in the handbook and how that all works, and then read about the settings API in the handbook and how that all works. And then you'll get a better understanding of options versus settings. Jim says, let's do a direct DB insert command. Okay. So I was hoping we would cover this a little bit today, covering custom tables in the WordPress database. We're not going to have time today. So I'm going to leave this one next week. So we're going to do custom tables next week. We're going to give ourselves a whole hour to play with it. I think that's a much better idea. But let me show you, let me give you a little bit of a teaser about how I would do something like that. So the only time that I would use a direct DB insert is if I was working with a custom table, I'm going to very quickly throw up some code here that I'm not going to actually type out myself. I'm going to cheat out registry. But if I had a function that did something like, let me pop it in over here. So if I had a function that created a custom table in the database, in this case it's just called custom table and it has various fields, ID field, time, name, all that kind of thing. And then that would create that table. That is the time that I would then look at using some kind of custom insert. And the way I would do that is I would use the WPDB class. And I'll show you what that kind of function might look like. I'll leave this on screen if folks want to see it. But you'll see here I'm accessing the WPDB object, the instance of the WPDB class, setting up the table name, and then using the WPDB insert function to automatically insert the record. Now, what I would never do is I would never do something like this. I would never do... Let me just give you two examples here. I would never do something like, and I think it's just query. I'm not sure. Insert into table name. That's wrong. But those of you who know MySQL queries know that that's not going to work, but you get the idea. I would never do that for one valid reason. If I use this, there is no sanitization of my values. If I'm receiving those values from an external source, I don't know what could be injected in those values. I'm possibly leaving my code open to SQL injection. If I use the WPDB insert functionality like this, and I give it the table name, and then an array of fields and values, then the insert function does sanitization on the data. Is it sanitization? Yes. So it then means my code is more secure. And if you want to watch a tutorial around that, I can actually share one with you that we did... Adrian will remember this. We did it a while ago. There's two actually that I want to share with you. So let's go security. The first one is... Let me find it here. The introduction to security developing plugins, where we talk about the five things that you should do in your plugins to organize security. Nicola, I did see your question around admin privileges there. I will get to that in a second. And the other one is the extending WordPress common security vulnerabilities. So the first one is basically covering the theory. And then in the second one, I actually take the same plugin code from the first tutorial and actually fix all the security vulnerabilities. And I take in that code, I have insert, a direct insert, and I turn it into a WBDB insert. So have a look at that to kind of understand more of the theory behind all of that. But yes, I wouldn't do a direct insert. I would use the WordPress database objects method to insert my data that way. Could we add a user with admin privileges? Just like a comment from the beginning of the workshop. Do you mean could we do it in my script or should we do it? Is that your question? And are you trying to catch me out, Nicola? So could we do it? Yes. And you would essentially, we won't have time to do it now, but essentially if you have a look at, let's have a look at the user table. You will see that I mentioned that my user has a meta key of WP capabilities. And then this is a serialized array of data. So the serialized array as administrator with a value of one. So what that would look like is I would create my user using the WP insert user function to insert the main user record. Then that would return the ID of the user that's been added. And then I would use, I think it's add or update, add user meta, pass in the ID, pass in the WP capabilities meta key, and then the relevant serialized array. I've never coded it myself. That's the theory of it. I trust, I let WordPress handle that. I've never coded it myself. But yes, it theoretically is possible, whether you should do it is a different story. I hope that's obviously your question, Nicola. Kaiser says in a custom plugin, do you prefer to keep relationship primary, foreign key mapping in database table, or do you manage from code level? If primary, I delete to delete. Ah, interesting question. Is it possible please take a session how we can optimize database and how we should design database and custom plugin. Okay. So in a custom plugin, I, so let me put it to you this way. I have never had a requirement where I've needed to create custom tables that had relationships like that. The only, the majority of the work that I've done in WordPress has been using the custom post types. And then when the custom post type gets deleted, it cleans up the meta or doesn't, I can't remember exactly, but it's something I've never really had to worry about. If you were doing it as custom and you were keeping relays, you could do it on the SQL level where you force those relationships. I've never, as I say, I've never had to do it. So I don't know whether there would be some kind of side effects that I'm unaware of. So I can't really answer that one 100%. But it should be possible. Maybe we should do that as an experiment one day. Set up a table with primary key, foreign keys, and then see what happens if you've set up those relationships in my SQL. Because as I understand it, it should be possible, but I've never done it in WordPress. And then second question, is it possible to take a session on how to optimize the database in WordPress, how we should design database and custom plugin, and then clean up and optimize. Sounds like a good topic as well. So clean up and optimize is an interesting one because it depends on whether we're talking about cleaning up custom tables that have been created by other plugins or building plugins that have their own clean up and customization routines. And that also has pros and cons. So here's an idea. Here's what I'm going to suggest to Kaiser and Lisa and Alana. I hope I'm pronouncing your names correctly. If you go to github.com, slash WordPress slash learn, I'm going to ask you to do a little bit of the work for me. And if you go to that URL and you go to the issues list and you click on new issue. Now you will need a GitHub profile for this. So you will just need to register a profile. But if you click on new issue, there is a topic idea template. And basically, this is you saying, there's an online workshop template. There's also a tutorial template. But start with topic idea template. And here you can say, I want to see a workshop on database design. I want to see a workshop on clean up and optimize. And then in that content, ask the questions that you have so that I can better understand what knowledge you want to know. So if your question is more around optimizing the database for speed and those kind of things. Or if your question is more around cleaning up a database that has been around forever and multiple custom plugins have installed custom tables. Or if your question is more around how do we build a plugin that automatically cleans up those tables and the pros and cons to both. If you want all three create three separate issues. But if you can create those, then I can kind of work on specific workshops around those topics. I would love to hear from you as developers. What are the information that you want to see? I'm basically just guessing mostly as to what I think folks need to see when I prepare these workshops. Okay, there's a little bit of guessing. There's also a little bit of planning on my part. So I will share this with you. In the make WordPress training blog, the team blog that we use to manage a lot of projects. There is a introduction to WordPress syllabus that I sort of worked on. Wow, it was March already this year. And at the moment, my goal is to get through every single one of these items. And so you will see that one of the items in this list here today is the WordPress database. So I'm going to tick that one off after the session. But there will be, I'm planning another syllabus after this for sort of intermediate topics. And then another syllabus after that for sort of more advanced topics. So when you give me these ideas, I'll be able to slot them into one of the syllabus and we will eventually get there. So please, if you want to see those workshops and those tutorials, please do log those issues. It'll be great to know exactly what you want to see and what you want to hear about. Okay. Any other questions around the database that we covered today? As I mentioned, it has been decided now. Next week, we will dive into custom tables and we'll talk about how to create them, how to work with them, how to add and insert and delete data. I was silly thinking we would get to all of it today. I should have realized that, but that's fine, because now that gives me something to plan for next week. If you have any further questions about this, if you're watching this on WordPress TV afterwards, you are always welcome to reach out to me. The easiest way to find me is to go to the Make WordPress Slack, which you can access by going to, I think it's chaff.wordpress.org. Yes, let me share that syllabus now. That's a great question. Thank you, JS, for asking that question. Here is the syllabus. It needs a little bit of updating. Some of these sessions I have done and turned into tutorials. Some of them I haven't yet, but you'll see the ones that are linked are actual tutorials on Learn WordPress. And eventually, I want this whole syllabus to be links to tutorials, and then anybody can come along and work through them. But if you want to get hold of me, the best place to do it is inside of the Making WordPress Slack. If you don't have a WordPress profile, this is a great time to create one. Once you've created it, you can create an account in the WordPress Slack. You can message me there at any time. I won't always reply instantly, but I will reply within 24 to 48 hours. The other way you can get hold of me, if you want to, I'm a little bit slower on email, so I don't mind you if you email me, but I'm very much slower. So if you go to my blog, which is jonathanbossinger.com, and you click on the About Me page, there is a link somewhere along the lines here about my email address. It's down over here. You can email me. It's very easy. It's my name, jonathanbossinger.com. You're welcome to use that as well. But going via the Make Slack will be a lot quicker, and I'll be able to give you information a lot faster. Okay. Kaiser says, do you prefer caching mechanisms to serve static content? Posts, et cetera, which we pulled from the database frequently, but doesn't change it a lot. That is a whole topic on its own. And maybe caching is another workshop we need to do. My personal preference is to use something on the server level, so object caching on the server level. So I tend to use hosting platforms that do that, because, and then also caching things like the JavaScript and the CSS and those kind of things, as opposed to caching the whole page. So I prefer to cache the data objects and the JavaScript and the CSS and those kind of things, because when those are refresh, it's quicker to refresh. It's quicker to rebuild that cache, and it doesn't require rebuilding the whole page cache. That's just my personal preference, but there are multiple other ways to do it. I just like to sort of do the caching earlier up the cycle, if you know what I mean. So you've got the browser, then you've got the PHP side that gathers all the data together, and then you've got the MySQL side, and then you've got object caching on the server. That's where I prefer to do the caching. But that's just the personal preference. Okay. We have run out of time for today. I should have probably planned an hour and a half session, but anyway, we'll do custom tables next week. Thank you to everybody who has joined us today. Hopefully I've answered all your questions if I haven't. You're welcome to ping me in Slack. You're welcome to get hold of me online. I look forward to seeing hopefully all of you again next week, when we talk about custom tables and plugins, but otherwise enjoy the rest of your Thursday, enjoy the rest of your week and your weekend, and I will see you next week. Bye.