 Good afternoon, good evening, good morning, wherever you are in the world. I've tried to remember that line from the Truman Show, and I think I get it wrong every time. But anyway, welcome everybody to today's online workshop. Today we're going to be talking about custom tables in WordPress, which was a little bit of a follow on topic from last week's session. So if you are joining us from last week's session, welcome. If you're not joining us from last week's session, welcome as well. As you join, please do let us know where you are joining us from in the chat while I do my usual introduction. So my name is Jonathan. I live in Cape Town in South Africa. It is currently the middle of winter in Cape Town, which I have been told by my Canadian friends is not as cold as I think it is. But it's cold enough for me. What I what I hate about Cape Town weather in the winter is that it is wet and windy. So you generally get soaking wet wherever you go in and around Cape Town. So I tend to stay indoors with a fire and a glass of wine to keep warm. I am educated automatic. And what that means is I'm sponsored to work with the WordPress training team. And we create these workshops, we create tutorials, we create lesson plans for learn WordPress.org. And if you want to find me online, you can find me at Jonathan Bossinger.com. That is where I generally hang out and post blog posts when I remember to. But all of my social media links are all on there as well. And as far as I can tell, I am the only Jonathan Bossinger in the world. I have not found another Jonathan Bossinger anyway. I regularly Google my name, not because I want to find out about myself, but just to see if there's another me. And I've yet to find another me. So we have Adrian from and I am California welcome Adrian. Good to see you again. Gerald from Arvada, Colorado. Michelle from Atlanta, Georgia to wreck from Egypt, Jim from Philadelphia. Tracy from Reno, Nevada. John from Chicago can now from India. Welcome everybody. It's great to see you here. All today, Stefan, Stephen from Munich. I'm going to assume that Stefan and not Stephen because Stephen is the English way of pronouncing that name. So I'm going to do that. Assume that Stefan. All right. So while folks are joining, let's get through the announcements very quickly. So once again, welcome everybody and thank you to Tracy who is kindly co hosting with me again today. If you can't see the slide, please do let me know or if at any point in time you can't see the slides on the screen, please let me know. And we can fix that. As always, we are presenting in focus mode. So this means Tracy and I can see you all but you all and you all should be able to see our screens or at least my screen. My video, but you can't see each other and this is to prevent any issues of zoom bombing that could happen. But you're welcome to enable your video if you would like me to see you. You're welcome to also remain in the dark. I don't mind either way. You are welcome to ask questions at any point in time. You're welcome to post your questions in the chat or unmute to ask your questions. The only thing that I do ask is if your question does not specifically relate to what we're dealing with on screen at that time. Please keep it for one of the pauses that I do allow for to grab a sip of coffee or water as we go through. Then if you want to code along with me today, make sure you've got a local WordPress install ready. If you're watching this on WordPress TV later, make sure you've got your WordPress installed ready. If not, you're welcome to pause. You can't obviously pause live yet today, but if you're watching this on WordPress TV, you can. If I am going too fast, please do let me know I sometimes tend to speed up when I get excited. Or when I'm coding, so do slow me down if you need to. If I'm not explaining myself or something is not clear or the captions are not keeping up the automated transcriptions that zoom provides are not keeping up. Please do let me know. I will be posting this recording to WordPress TV afterwards. I usually post them sometime during the day on Friday. If I don't get to them on a Friday, usually latest Monday. So if you want to catch up on this later, you can watch it there. And then if you want to look for more tutorials and courses and workshops, you can go to learn.wordpress.org. And then also if you're looking for additional WordPress developer news and updates, please do consider visiting the core dev blog at developer.wordpress.org slash news. This is a fairly new initiative that launched earlier this year. And there are some great articles and blog posts there about various different things that our developer focused. All right. I think everybody that wants to be here should be right here by now. So once again, if you can't see the learning outcome slide that I'm presenting on screen right now, please do let me know in the chat. But today we're going to be focusing on the fundamentals of creating and managing custom WordPress tables. This week we spoke about the default tables that ship with the WordPress install. Today we're going to be looking at how you can create and manage custom tables. We're going to talk about when you should set a custom table and when the defaults custom post type and post meta is probably enough. I'm going to share where to find documentation on this topic. It's something that I don't think it's for a lack of folks not wanting to share this documentation. I think it's just a case of nobody's thought about migrating it to the new developer docs yet, but it doesn't exist in the current developer documentation. It exists in the original what's known as the WordPress codex, which is the original sort of developer slash user documentation. So I'm going to share that link with you and that is effectively the core document that I refer to every time when I need to create custom tables. Then I'm going to show you how to create a custom table, how to interact with the data in that custom data, so how to select data, add data, update data in a safe way. We're going to cover a very brief overview of doing table updates. We're not going to necessarily code them today. But we're going to talk you through the process. And then I'm going to show you some options around, well, one option around cleaning up your custom table and also chat about when you should and or shouldn't clean up your custom table. So hopefully we have enough time for that. I see that William asked, sorry, not William, William says I have no audio. So I'm going to assume that is a problem, possibly on your side because nobody else has reported that yet. So maybe just check if your sound settings are set correctly. If anybody else can't hear me, please let me know. Jim asked about the slides being shareable. I did share them in the, in the meetup group but I'll find the link for you here quickly. I'll just go to the meetup page. It's just easy that way. And then I'll paste the link to the slides there. There is the link to that there. I see the gene since you're having a problem with a zoom link. So I'm going to just copy the link for Jean quickly. I'm going to copy the wrong thing. Copy. No, that doesn't copy the whole thing. That's fairly annoying. So let's do this. Let's spell link properly. There we go. So hopefully Jean will be able to join us with that one. Okay. So I've shared the slides in the chat but they are available on the meetup page. I do try and share them as much as I can before the session. Then requirements for today's session. As mentioned earlier, you will need a local WordPress install if you want to code along with me. You will need some form of text editor. I will be using VS code. And then you will need a way to access your database. Last week I mentioned the SQL buddy plugin. You're welcome to install and activate that if you would like to use whatever comes with your local WordPress install. Most local WordPress setups have some kind of way to access the database. But if you don't have a way, you can install the SQL buddy plugin. And then the learn wp-learn-database.php script. So if you joined us last week, you might still have a version of the script in your WordPress install. If you don't, you can click on this link and it will include all the code that you need in that PHP script. And I'm going to recreate it now for folks who would like to see the process of recreating that. So I'm going to open up my VS code instance. Here is my learnpress install. And just in the root of the learnpress where the index.php file sits and where the WP setting sits. I'm just going to create a new file. And I'm going to call it wp-learn-database. You're welcome to call it anything you want to. Just remember the name of the file. And then you can open up this just that I've shared in the chat. And it's just very simple little PHP script. You can copy the entire contents of the script out and pop it into your learn database or whatever you've called that file. And essentially all this is doing is requiring the wp-load.php file, which includes all of the setup things that WordPress needs to configure and set up to be able to access WordPress data. And then there's just this data variable and it echoes the data variable inside some HTML. If I browse to my learnpress site and I go to wp-learn-database.php, there it shows me just learn WordPress database and then the hello WordPress string that I've set up. We're going to use this today to test the custom tables that we're sitting up to test the functionality. Anton says, does anyone have last week's slides link? I will find that for you, Anton, if nobody else can. And I'll share it towards the end. Maybe I'll share it now when we take a break. So give me a second for that one. Actually, this is a good point in time for me to take a break. If anybody's still getting their environment set up, I'm going to go find those slides for Anton from last week's session very quickly. And then we will kick off. So from here, I'm just trying to remember how this works. Oh yes, I go to learn WordPress online workshops. I go to past events. And I should have the link. I'm pretty sure I did share the link in the past events. Let me find it here. So it's there somewhere. Yeah, here we go. Here are the slides for last week's slides. So there we go. Okay. Cool. Hopefully everybody is ready to rock and roll. If anybody is still setting up their learn database or their whatever the case may be, please let me know. Otherwise I'm going to continue after a sip of coffee. Those of you who have been coming to my session since I started, or remember that I started in about June. And one of the reasons I'm always drinking coffee at about four in the afternoon, even though I shouldn't, it's just to kind of keep warm. I'm not from water to coffee now this time of year. Okay, so we've got everything set up. I also included at the bottom here an empty plugin. We'll create this plugin as the workshop continues to don't stress too much about it now. We'll just create an empty one and we'll put some code into it as we go along. All right, let's talk about custom WordPress tables. Now the main link we're going to be working off today is this one over here. I will copy this and paste it in the chat. Before we continue, I see that Tarek says he can't see my shared screens. I'm going to fix that very quickly for you, Tarek. By disabling and re-enabling this screen share, which I do. I can't remember how this works anymore. Oh, there we go. You're probably seeing nothing right now because these are all controls that I have. There we go. Okay, so I've disabled the screen share. Let's share it again. And Tarek, if you could just give me an okay or a thumbs up if you can see the screen. If you can't, then I'll check back. As I mentioned, the link we're going to be working off today is this one over here. It's in the codex.wordpress.org creating tables with plugins. I'm going to open that piece of documentation right now. Tarek says it's working. Excellent. Thank you for letting me know, Tarek. I hope I'm saying your name correctly. I apologize if not. So this is the creating tables with plugins codex page. It is the page that I have referred to every single time I've needed to create a custom table. It is not the most extensive piece of documentation around creating custom tables. It does require you to have a little bit of SQL knowledge or SQL knowledge. I'm not going to have an argument today with folks about how that is pronounced. I'm going to pronounce it SQL for the rest of the day. But if you're someone who pronounces it SQL, that's also okay. But it does require you to have some knowledge around how to write SQL queries in a MySQL or a SQL database. And some basic understanding of how plugins work. But it's a good piece of documentation to read. Right at the bottom of this piece of documentation, there is also some links that I recommend reading. The first is a WordPress hackers mailing list answer to a question about a plugin requiring additional tables. This is kind of the original place where this discussion sort of happened. You'll see this happened back in 2005. And the person who suggested this way of doing things was using what a function we're going to use later today called the DB Delta function. And this is pretty much how things have been done ever since. So this is what are we, 2005, 2023, almost 20 year old data that has remained the same. In a perfect world or at least in my perfect world, there would be a slightly better way to do this, but it is currently the way things are done. There is also a very good WordPress stack exchange link at the bottom of this page post meta versus separate database tables, which I highly recommend reading. Whenever you're contemplating whether you should use the custom post type and post meta functionality, or whether you should switch to using a custom table. Now one of the things that I wanted to cover in today's learning outcomes was when you should consider a custom table. So we're going to talk about that now. If you read through this answer and an additional answers and all of that, you will see that the general recommendation is, and this is also my opinion, is if the content that you're dealing with makes sense as a piece of content. So what do I mean by that? Well, let's have a look at the WordPress database. So I'm going to go into my PHP admin. And I'm going to go and log in. And I'm going to go to my loan price database. And I'm going to go to the post table and I'm going to click on the structure tab because I just want to have a look at the fields that exist in this table. So the fields are things like post author, post date, post content, post title, status. Those are the main sort of fields that this post needs. It has modified dates and parent types and menu orders, but those are related to other bits of functionality. But if your, if your item that you want to store data on needs an author and a title and has some content and might have an excerpt and might need a featured image. Then it makes sense to just use it either as a post or register a custom post type and then use the default functionality within WordPress. Or if the additional data that you're storing, so let's say you want to store something like a book. So a book has a title and author content featured image and then it might have one field like maybe an ISP in that you want to store additionally onto that piece of piece of data. Then it makes sense to use a custom post type and a post meter field of ISP and on the custom post type. When you start needing to add a lot more post meter fields. Then it's time to start thinking about well maybe this would make more sense as a custom post type. And the example that I always use if you've been following the WooCommerce development cycle, if you've been following Restrict Content Pro, which is a digital version of not Restrict Content Pro. I've forgotten the name of the plugin now. It's a SandEals development product. But it's basically a e-commerce, let me just search this. Easy Digital Downloads. Thank you, Kasaba. Thank you for that. Easy Digital Downloads. That was the one I was looking for. Easy Digital Downloads is a digital version of WooCommerce for example for selling digital products. There they started talking about the concept of well does it make sense to use posts for products possibly because posts will have a title and some content and a featured image and maybe a one or two post meter fields. Does it make sense for orders to be using custom post type functionality because orders generally have other fields. And so you started having to store a whole bunch of post meter. And the minute you start adding a whole bunch of post meter, you're now doing more complex queries than you should do. And so there was a drive a while ago for I think it was Easy Digital Downloads did it first. They searched from using custom post type to using custom tables for their orders. And I think WooCommerce is in the process of doing that or they have done that. I can't remember exactly still to be corrected on that. I've also switching over their orders to using custom tables. So when you're planning these things, when you're thinking about a custom table, think about the content that you're storing, think about data that you're storing and whether or not you need a custom post type or whether a custom table makes sense. As an example, when I was working on seriously simple podcasting. Simple simple podcasting is a perfect example where custom post types make sense, because a podcast has a title it has some content. It probably has a featured image it probably has a author, and the only poster that we stored for this was the audio file and one or two other pieces of data so it made total sense to stick with custom post types. But if you were going to store maybe things about documents that have a file upload and then some additional data on top of that that didn't make and you were using the title content excerpt, featured image functionality, then it might make sense to use a custom post type. Another good example is is form submissions. You will often see in my example code that I built for a lot of these workshops, I build a simple form submission table, because I'm just storing name and email for the form submission for a simple contact form. And they're using using a custom table over using a postmates or a custom post type just to me makes more sense because I'm literally just storing two fields. Great. Thank you Tracy for for sharing that link about the plan for the WooCommerce custom order table so that's the plan it was shared in January of 2022. I haven't been following the development of that so I don't know where it is now, but I do know it's something that the folks at WooCommerce are working on. Okay, so we've discussed about when you should consider a custom table we've discussed about where you can find documentation it's this codex that I shared with you in the chat. Let's move on to actually using these things. So the first thing is how you should create a custom table. Now, you can just read through this document, and it will give you most of the information that you need so if you're finding the workshop boring you're welcome to to to ignore me. I see agents other questions are custom table is for when there's a lot of custom IDs and types. I would say yes, when you've got a lot of custom IDs and a lot of custom types going on is just when the fields that you're storing don't match the post table then it's good to go for a custom table. That's my general general rule. So if we if we pop down to this document one of the first things they talk about is when you create a table. It's a good idea to access the database table prefix. If you don't know what the database table prefixes I'll show you very quickly it's in the WP config file. The default prefix is WP underscore. Some managed hosting companies will default that prefix to something else as you install your WordPress site. But the default is WP underscore. And so the reason that you want to be able to access that prefix all the time is depending on your installation you don't have to hard code that WP underscore but so to be able to access the prefix and a whole bunch of other functionality which you will need later on, you will need to be able to use this global WP DB object. Some of you have heard me mention this before I sometimes get the spelling of this incorrectly I call it the WP BD object sometimes. But basically this object is a global variable that exists in every WordPress request. It's what WordPress uses to make its own queries to query custom post types and those kind of things. But when you're creating a function that you're going to create custom tables you're going to need to be able to access that global. It will give you the prefix using the prefix property, and then you can just prefix that onto whatever your table name is. To begin, let's start by creating our custom plugin. So what I would like you to do if you're if you're following along with me is navigate to your plugins directory. Open up your plugins directory and we're just going to create a new file in the root of this directory we don't have to pop it into a folder. And I'm just going to call it WP learn custom tables for today. You're welcome to call it anything you want to mine is going to be custom tables. And then inside of here we need to do a few things to start off with we need to open up our PHP tags which is a less than sign question mark PHP. And then we need to add the plugin header right at the top so that's a forward slash and an asterisk is the minimum required you can use the two asterisks is the PHP comments way of doing things. That's the way I prefer to do it. And in in visual code studio when I enter to create this second line for me like this. If it doesn't you'll just need to code this yourself so this is just an asterisk below. And then I can go plug in name and you'll see there my copilot is already creating some things for me so I'm going to just use that. So WP learn custom tables if you don't want to code this I will share this in the chat you welcome to just copy paste it. And I just like to also give it a description. Learning about custom tables that is the minimum required data we need for the plugin header for this to be a valid plugin. Jim I saw your question they'll get to that in a second I'm just going to paste this code into the chat for anybody who wants it. Jim says to keep a plugin independent is it reasonable to create a custom database instance for your custom table. I wouldn't necessarily recommend that personally. And the main reason is you now have to manage to database connections by default your WP DB object is going to be connected to the database that is configured in the WP config. So you're going to have to manage a different instance with different login not logins but different database name and those kind of things. It honestly depends on your use case if there's a specific security reason you want to do that then you're welcome to, but it's not something that I would suggest because you're going to have to manage you have to switch connections and you have to do custom connections, custom MySQL connections in PHP WordPress handles all that for you so so I wouldn't necessarily recommend going that route. Okay JS is he can't share my screen so let's fix that quickly for him this is just a zoom thing that I have discovered recently so I'm just going to stop the share quickly and then start to share up again. And then JS let us know if you can see the screen Jim says would keep me from messing up WordPress stuff. I don't know what quite what you mean they. But we can we can have a chat about it maybe offline. Okay, so JS can see that's great. So let's create the function first of all based on what the code is that we're seeing on the pages I'm just going to copy this code out. And I'm going to paste it in the roots of my of my PHP file. I'm going to give it a different prefix just for my own purposes I'm going to call this WP learn custom and then install that's what mine looks like. It's got to be all underscores. So this is my function that will create a custom table for me. So I at the right at the top I get hold of the global WP DB instance globals are a thing in PHP which are kind of frowned upon, but it's part of WordPress core functionality so you you create it by calling it a global and then you can access it by by using the global statement and then you and then you give it the variable name. And then we're going to set up this table name variable over here so table underscore name is WP get the prefix property. And then let's change this live shot box to something else now for my purposes I'm just going to call it custom table because I'm boring. I'm actually going to yeah I'm going to leave it like that underscore custom table. So there we go so all this is doing is setting up a variable to create the custom table. Once we've done that if we have a look at the documentation the actual part about creating or updating the table it says you rather than executing the SQL query directly, you will use something called the DB delta function. The DB delta function sits in the WP admin includes upgrade PHP file. It's a file that WordPress uses whenever it needs to run a database upgrade or any kind of table upgrade. What the function does is you pass it some some SQL statement some SQL query. It and generally it's a create table statement so you pass it a create table statement. It will then examine the current table if one exists with the same table name and compare the structure of the two. And if the structure is the same it will not do anything it won't run the query. If the structure is different then it will make the necessary changes. So what's really cool about that is you can create a new table this way so it'll check does the table exist no it doesn't okay create the table using this query. You can also use it for future upgrades or downloads so if your table changes you want to add fields you want to remove fields, you can use the same concept. And you will see that there are some specific rules that you need to follow when writing your query and this one always catches me out when I'm busy writing the queries. You must put each field on its own line. You must have to and this is very specific to spaces between the words primary key and the definition of your primary key. If you don't know what primary keys are we can chat about it at this time later but it's specific to creating my SQL table tables. You must use the word key rather than its synonym index and you must include at least one key key must be followed by a single space. You must not use any apostrophes or back ticks around field names if you've written my SQL queries before you'll know what we're talking about there. Field types must be all lowercase. SQL keywords like create table and update must be uppercase. And you must specify a length of all fields that accept a length parameter so an example an integer has a length field you need to length parameter sorry you need to specify it. But here is an example of what something like this could look like so I am just going to copy out all of this code. I'm not going to copy the global part there because we've already got it in our functions I'm copying everything from char set collate down. And then I'll explain what we're doing there in a second. My phone is just lit up so I'm going to make sure it doesn't beep. But I'm going to copy that code out and pop it just below my table name here. And I'm going to clean it up a little bit. My water formatting is broken for some reason but anyway, there we go. And then I will share the full function with you in the chat. So let's quickly talk about what this is doing so it's creating the same variable which uses the prefix and the word custom table at least the string custom table. It gets the char set co later value from WPDB. And if you have a look back at the documentation. It says, we set the default character set and correlation for the table based on what the current character set and correlation is. If we don't do this some characters could end up being converted to question box so the system make sure that all the characters stay the way they should be. So we set up that char set collate variable there. Then we have the this equal query and I like to indent this a little bit so it's a little bit more readable so I move it so it's right under the table. So let's see the query is in double quotes because we're mixing up sequel queries and variable names. But it's create table with the table name at the top and then we start with the ID field on one line, the time field on one line, the name field text there's a URL that's being stored, and then we set the primary key. We close all the field brackets and then we set the char set collate to the bottom here, we finish the query string up with a semi colon because my sequel needs that to run the query. And then that is all wrapped in the double quotes there. Then we need to require the upgrade PHP file and this is because when we run this function, the best practice to run this function is either on a plugin update, or a plugin activation. The update process or the plugin activation the upgrade the WP admin includes upgrade PHP file hasn't yet been included or required. So we do that inside of our function. You'll notice we also make sure we require it, require it really once. This is a special PHP thing it says if this file has already been required. Don't require it again. If I require it here and it gets requested to be required later don't require it again this is a special PHP thing. And then finally we run the db delta function against the the sequel query that we've passed. The other thing I'd like to show you is I'm going to open up the db delta I don't expect you to do this. But if you have a look right at the bottom of the of the db delta function which ends me just find it quickly. There's a whole bunch of things I wonder if I can get there quickly. I want to make sure we don't. There we go. You'll see it returns this for updates. Array. And if we have a look at the db delta documentation. Basically, where was it right at the top here sorry folks, it will return an array of strings containing the results of the various update queries. So if you want to you can capture that array and do something with it. You can check if the results passed or failed and then and then action action certain things, if they either went right or went wrong I'm not going to do that today, but that is possible. All right. So let's have a look at what we've got just we've got our function it's doing the custom install setting up global wdb setting up the prefix setting up the charset collate running the query requiring and then calling the function. We need to action this somehow and the best place to do this when you are creating new tables in a plugin is on plugin activation. So in WordPress, you are able to let me get back to my resources here and find the right link for you. You are able to register both an activation hook and a deactivation hook. I'm just going to work with the activation hook for now. What the activation hook does is it allows you to call a function on plugin activation. What's cool about this is it'll only happen when the plugin is activated. So if you were to hook it into some other action or filter hook like WP like in net or WP or whatever, that's going to trigger every time WordPress executes a postal page. We don't really want that we just want creating of a custom table to happen one time when the plugin is activated. So we can use the register activation hook. You'll notice that it has two parameters, the file name of the plugin, including the path and then the callback function. So in a very simple plugin like ours, we can do something very straightforward like this. We can say register activation hook. We can use the PHP underscore underscore file constant to set this file that we're currently in because that's where our function resides. And then we can pass it the WP learn custom install, which is co pilots picking up for me. If you're if you're at your callback resides somewhere else in a different file somewhere you might need to to pass that file path into the into this activation hook. If you're using class based functionality, you might have to change this variable up front here. I generally prefer to register my activation hooks in my main plugin file. And then if I want to then hook them into class methods or variables if they're sitting elsewhere. Rather than trying to to mess with us. Okay. So that should theoretically all going well if we now activate this plugin this should create our custom table. So let's see what happens I'm going to switch back over to my database. And I'm going to make sure that we don't have the custom table sitting in the database currently. There is no WP custom table. It's not there. So now if we pop on over to our site. I'm going to before I do that. I'm going to just share this code in the chat. I think I may have done already actually. Yeah, I did. I didn't share the register activation hooks. I'm going to share the whole code just I did see better. I did see your question there. I will get there once we have activated this plugin. So I'm going to go over to here. I'm going to find my plugin. And there is my learn custom tables plugin. I also want to just to make sure that things are working well. I want to make sure that I've enabled debugging. Because if anything goes wrong, then it'll log that for me. So I'm going to switch debugging to true. But I'm going to change debug display to false. If you were in my debugging WordPress workshop, you'll know exactly why I'm doing this. I don't want to see it on screen, but I want to enable the debug log. And then I'll see any errors in the debug log. So those are the three. If you want to do that in your WP config, you're more than welcome to. I've shared that in the chat. That's inside of your WP config file. I'm going to close that down. Okay. There we go. So let's see what happens when I activate this plugin. Okay. So the plugin activated without any major errors that I was aware of. So that's good. I'm not going to check if there's anything in the debug log, which will be in the content directory. I'm going to just close my plugins. Okay. I don't see any debug log. So it looks like everything went fine. So now I should, if I look at my database and I refresh my database, I should see there it is. There's my custom table has been created. If I open it, it has the ID, time, name, text, and URL fields. So I'm happy that's all working. All right. I'm going to scroll back and just check out Ben's comment or question. This is good stuff, but I've been doing this in the past and seem to get a PHP notice on fresh installs when creating the tables for the first time. Table, table name doesn't exist. Here's a link to the bug. It's getting called on the init hook versus activation. So it can check for updates to the table as new releases get published. Maybe there's a better hook to be using. Okay. I'm going to, Ben, if you don't mind, I'm going to put a pin on that for now and see if we have time to swing back later. If we don't, then I'll maybe see if we can, yeah, I'll try and make sure we've got time right at the end to sort of dive into that. But I want to make sure we cover today's workshop, but then I'll come back to that if that's okay with you, Ben. All right. So if there's anybody who was curling along with me and has had any errors or issues activating their plugins, now's a great time to let me know. I'm going to grab a sip of coffee and then I'm going to move on to how to select add or update table. I'm also going to make a note for myself to get back to Ben's question. All right. So the other cool thing that you can do in the plugin activation, and they actually talk about it into, into in the codec stock, which I don't have open anymore. So let me find it quickly. Is they talk about inserting default data when you initially run your, your activation. So you can do things like this. You can say welcome, blah, blah, blah. And then you can say insert data and off you go. I don't want to do that today. I just want to create a function that inserts some data for us. So to do that, I'm going to, under this adding initial data section, I'm going to copy out this WPTV insert part. Then what I'm going to do in my code, I'm going to create a second function called WP function. WP learn. Insert custom data. And I'm going to pay that code. Because I'm using the WP DB. Object I need to glow. I need to access it using the global statement there. And this is also a good time to look at what is needed when you auto formatting has broken today. When you, sorry, I also need the table name. So let's copy out the table name over here as well. There we go. When you need to insert data into your database. So the WP DB class. And I'm going to share the link or that with you as well while we open that up. The WP WP DB class. The documentation talks about over here, common tasks. So you can select a variable. You can select a row. You can select a column. You can select generic results. You can run general queries. I'm going to jump right down to that. So you can run general queries by just calling the query method on WP DB. However, it's not a good idea to use the query method to run insert or update requests because then there's no sanitization happening. So the best thing to use is the insert row method, which requires the table name. And then, excuse me, I'm burping there. And then the data in an array. And to update the rows, you would use the date, the table name, the data, and then the where clause. So we're going to talk about those right now. So to insert data, I get the global object, set up the custom table, again using the prefix. And then I call WP DB, WP DB, insert. And the first parameter is the table name. So I use the table name variable that I've set up. The second parameter is the array of data that I want to insert. So here we're passing in the time as the current time, the current MySQL time. We're going to give it a name. So I'm just going to change this to my name for a second here. It'll change to Jonathan. And the text will be some text. So we'll say welcome, Jonathan. And yeah, why not? Let's give it an exclamation point. And you will see that is how that is the best way to insert data into a custom table. The insert method of the WP DB object will run sanitization on strings for us. We don't need to do it ourselves. So that's a good reason to use it. It will also, if we switch back to the documentation and we have a look at the insert row option. I think it mentions it here somewhere. Here we go. This method returns false if the row could not be inserted. Otherwise it returns the number of affected rows, which because it's inserting a row will always be one. So your response will either be false or one. So you can use that to check, did it work? And if it did, we could do something with it. So with that function in place, I'm going to copy and paste this into the chat. If you want to use it, you're welcome to change my strings to your name if you would like to or leave it as it is. I now want us to be able to call this function. And so this is where we're going to use our learn database dot PHP script that we created earlier in the root. And we're going to call this function. So we can take out the, actually let's leave the data there for now and we'll say data equals that function. So because we have loaded WordPress, that function should be available to us. And then I'm just going to echo that data variable. So we're either going to see nothing, which means it failed, or we should see a one if it succeeded. So that's the only change we're going to make there. I'm going to copy this into the chat. If anybody wants to update their learn WordPress database script. So that's all we're going to do for now. To trigger that functionality, we will switch back to the script in the browser. There it is. Wplurndatabase.php and simply refresh it. Okay. So we're seeing nothing there, which might mean there's a problem somewhere. So if I now switch back over to my database, okay, it looks like it did work. And so there might be a reason why I'm not seeing what I need to see there to make this a little bit easier. What I can do for myself is instead of echoing the data, I can use something like print R on the data and see why I'm not actually seeing anything. This will be one of the first things that I'll do. So let's have a look. Let's just insert it again. Why not? Let's refresh that. And I'm still not seeing anything. I'm not quite sure why that is. Maybe there's a problem in my script. That's weird. I should see something. Oh, wait, wait, wait, wait, wait, wait, wait, wait, wait, wait. I know why. I know why. There is a bug here. I'm not returning the results of WPDB insert. So I should, at this point here, I should update this to return the results. There we go. So I'll share that with you in the chat if you want to grab that. I was just running the query. I'm returning the value of the query. So let's go back and refresh. And there we go. Now we see one as expected because my original function wasn't right. And if we have a look at the database, I should now have three Jonathan's in my database. There we go. I've got three Jonathan's in my database. So that's how we insert data. Now let's say we want to update data. So as I mentioned earlier, there is an update rows option. You pass in again, the table name. You pass in the data that you want to update, and then you pass in a where query. And that can be as simple as passing an ID equals one. And again, that's an array. So if we have a look at how we would do something like that. So let's take this function, this WP learn insert custom data function. And let's make it a update custom data function. And in a real world scenario, you would probably pass the ID and the array into this function. But for example, today I'm just going to hard code everything. One of the things that I like to do when I'm coding is I like to hard code all the variables, make sure it works, and then figure out what's going to get passed around. That's the thing that I do. So the global WPDB stays the same. The table name stays the same. This time I'm not going to insert, but I'm going to update. That's going to stay the same. I want to update. I don't want to update the time necessarily. I just want to update, for example, let's say the name. So let's make it a pool. And let's say, welcome, Paul. And I'm going to update the third record that I added. So let's switch back over to my database. It's going to be ID three. So there's the ID field and there's ID three. So in my code, I now have a second array of parameters. And in that array, I'm going to pass in ID and I'm going to pass in three. Now I want to just show you something very quickly. You'll notice that, let's go back to where the table was created. Here we go. The ID here is lowercase ID. That's the field name. In the update, let's scroll down to where the update code is in here. No, that's not what I want. It's not here. Okay. There's no example here. But in reality, the reason it's picking up ID uppercase like that is co-pilot is used to ID sometimes being uppercase. As I understand it, it should be the same as the field name. So whatever the field name is, you should pass in as the array key. So in this case, ID, and then the value is three. You can leave it as an integer. You can also cast it to a string if you want to because it's stored as an integer. We might as well use an integer. So that's what our update would look like. You can also use different where clauses. So you could wear combinations. So you could say where name is Jonathan, and then it would be passing in the name field and then the value you're searching for. But I'm going to stick with ID for now. I'm also going to just pop this on a new line just because then it all looks nice and clean. And I'm going to make sure that I'm returning the update value because that'll also leave me either a false or a number of records updated. So let's leave that like that. And let me share this code with you in the chat. And then if we go to our learn database code, now we're going to instead of inserting data, we're going to change it to updating data. So let's do that over there. I'll leave this as print R for now. Print R or A code doesn't make a difference. But now I should update that record. So let's see if this will work. Let's see if I know what I'm talking about. So I refresh my code. It's firing things. OK, it looks like it's fine. It's returning a one. If I switch back, and by the way, this is my process when I'm testing out code. I switch back and check if anything's been logged to the debug log and nothing has been logged. So it looks like it's all good. So let's hop on over to the database and let's refresh our data. And there we go. ID of three has been updated to pull and welcome pull. OK, so that is how you would do updates to custom data. If you wanted to delete data, you would do a similar thing. You would use the delete rows option. Again, it's passing in the table and you would delete where ID is three or whatever the case may be. So getting to know all of these options on the WPDB global object is very, very important for working with custom data. Making sure you know how to select a variable or a row or an entire column. Sorry, an entire row or a column, selecting generic results, inserting data, replacing data, updating and deleting are all very, very important pieces of functionality. OK, I'm going to take a break and check if there are any questions at this point in time. And if not, then we will move on to quickly table updates and then considerations about cleaning up your custom tables. Actually going to have some water instead of coffee for change. All right, we don't seem to have any additional questions. We do have 10 minutes left, but hopefully folks are happy to hang around for a bit longer because we are going to go a bit over time. If you're not, this will be recorded and will be posted to WordPress TV afterwards. So you will be able to catch up with it there. OK, table updates. Now I'm not going to code any of this. Our coding portion is finished today. This is going to be me talking and sharing information in the documentation. You'll see here, it talks about table updates in the doc that I shared with you. It's the adding and upgrade function. I'm actually just going to scroll up to here and click on it so I can get that link because this is very useful reading. Jim, I'll get back to your question in a second. The recommended way of doing things because WordPress doesn't necessarily have an upgrade process as it were, or a migration process. Some frameworks have a migration process where when you run an update, you can run certain queries or certain scripts to update or migrate your database. The recommended way to do things is to store a database or a table version number in your options table. And then based on the version number, when you release a new version, if there's a change to the database to get that version number from the options table using the getOption function. And then if the current version is not equal to the version that you're running. So you will need to update a version in the code and then you'll need to check it against a version in the database. And if they're not the same or if the one is bigger than the other, then you can run some kind of upgrade step. So again, get the table name, adjust your query for whatever the upgrade is or add or remove fields or change fields or whatever. Again, require the upgrade.php and then run the dvdelta. And then last but not least, make sure to update the option to whatever the current version is so that it doesn't run again. Now, there are a couple of ways that you can trigger this upgrade. The one way is to use the... I don't know if I... No, I didn't. I think I added it to my references. But I do have the link here, which I will share in the chat. There is an upgrade process complete hook. So this fires when the upgraded process is complete either for a theme, for a plugin, or for a core or for a language back. So you can use this when your plugin upgrade happens. So when someone upgrades your plugin, this hook will fire. And based on the context, you will see that the type here is plugin and then based on whether it's a plugin or a theme or whatever, you can then fire off your own functionality. That's one way to do it. The thing to note about that is that it runs when the download process for a plugin or update finishes, not when WordPress refreshes after that update. So sometimes this action hook in your plugin is run on the old version of your plugin, which if you're doing a database upgrade is not probably not going to be a problem because you just want to run that query anyway. But there might be a reason why you don't want the upgrade to run until after everything is refreshed and the request is refreshed. The way I have done this before, and I'm going to share this with you from the Seriously Simple Podcasting plugin, if I can remember where I put it, I think there was an upgrade handler that I created. Yes, there it is. So I created an upgrade handler that's hooked into the, I think it was into the WordPress action hook. Inside of the WordPress action hook, it then runs this run upgrades function. The run upgrade checks the previous version versus whatever the current version is. And depending on whether or not you're upgrading from a specific version, runs the specific upgrade. One of the reasons I did this was we were trying to make sure if anybody upgraded from quite a way back and upgraded to the current stuff, they got all the recent changes as well. So that's one of the reasons we did it that way. The downside to doing it this way is that at least a little bit of code. So the check that calls run upgrades is going to run on every request. And so it means that there's a little bit of extra overhead into your WordPress request. But the upside to it is you have a bit more control over when the upgrades happen and how they happen. So there's multiple ways to do it. You're welcome to choose whichever one works best for your scenario. I just want to get back to Jim's question. What is the difference between update and replace? So that was in the... Let me hide this. That was in the WPDB class. So replace... So replace replaces a row in a table if it exists or inserts a new row if it doesn't already exist. Whereas update will just update a specific row. So if you have a look at replace, doesn't have a where option. So you can't say specifically on this record make these changes. It will attempt, it'll look for it. So let's say you pass in an ID of something. It'll look for that ID and it'll then update that value. If it doesn't find it, it'll insert it. So that's the difference between update or replace. And depending on your use case will determine when you use it. I've never used replace personally, but there may be some instances where you want to either update a record if it exists or add a new record. Logging is often a situation where you might use something like that. Okay. So that covers plugin table updates. Then cleaning up your custom tables. So there are two schools of thought around whether you should or shouldn't clean up your tables when the user deactivates your plugin. First of all, just to mention, there is a registered deactivation hook. So you can use that. You can create a delete custom table function. You can hook it into the deactivation and then that'll fire when the user deactivates it. Just remember if the user goes into via FTP or any other way and just delete your plugin files, it's not going to trigger the deactivation. So it's not foolproof, but for the majority of users, they will probably trigger the deactivation process and then you can run a WPDB. It'll be, you'll probably want to use a custom query, general query. And then the query you'll run in delete. So let me show you what that might look like. I'm not going to run it today, but it might look a little something like this. I'll share it in the chat once I've pasted it here. So there's your delete table, for example. Let's actually just make it learn, delete custom table. And essentially again, you get the WPDB object, you set up the table name and then you can run a drop table if exists query. Here it's fine to use the default query method because you're not needing to do any checks. This is only going to run on plugin deactivation. The downside is if somebody is able to access this function. So you definitely should include some kind of can the user run these things. You might want to do a can current user can manage options. You don't want somebody to access this from anywhere else. You want to include that into this function. And as I say, if the user doesn't deactivate this code won't run. But the other thing to think about is sometimes you might want to keep the custom table because in an inner scenario where the user installs the plugin uses it, it stores data. Now one good example of that is like analytics data or stats about some in seriously simple podcasting. We created a custom stats table, which stored the stats of listens on your podcast. If somebody deactivates the plugin, but then six months later decides to reactivate their podcast. It might be nice for them to keep those stats. So it might be nice to keep the table around. So a good idea is to create a unplugging deactivation process, which before the plugin is deactivated, it asks the user, do you want to delete your custom data or not? Or maybe there's a setting in your setting somewhere where you say, when this plugin is deactivated, delete the custom data or not. And so awesome gene. And so you need to think about that when you're running deactivation. My recommendation would be include some process that warns the user when they deactivate this plugin, they may or may not lose their custom data. I generally on the side of rather leave the custom data there, but allow the user to enable something that deletes it if they deactivate, either in the settings or in the deactivation process so that they have the choice. So that if they opt in to delete that data, they know what's going to happen and they won't be able to get it back. That's generally my rule of thumb because I want to think about, well, if they decide to come back to using this plugin, how can I get their data back? I can't. Remember that in a custom table, if it's sitting there with data while it might grow the database, it shouldn't have any effect on any queries on any of the other tables unless there are relationships between your custom tables and the other tables. So there's no major overhead in leaving custom data there. But yes, it does increase the size of the overall database. So if they're moving around the site, it means they've got to store and move that data. So it's just something to think about. But the easiest way to do it would be you would register the deactivation hook, register deactivation hook. Same way that we registered the activation hook, we would pass in the file to where the function is sitting. And then we would call that function. And now if I run this and I deactivate my plugin, let's just test it. So if I log into my plugins over here and I deactivate my plugin, it should now remove that table. So let's just double check that if I go back to my learn press database, yep, no custom table. All that data is gone. So it's a quick and easy way to clean up that data. I will share this with you in the comments if you want to create your own deactivation hook. Oh wait, it's not copying for some reasons. And there we go. Okay. And so that's just something to think about when you're running deactivations. You do need to think about how is that data stored? Is that data important? You know, if your custom data is unimportant, then it's probably fine to delete it on deactivation. If it's something the user might want back, maybe you want to provide some option for them to download that data before they deactivate, pop up a warning and say, hey, if you deactivate this plugin, you're going to lose the data, maybe export it first and point them to your settings page and export option. I always try and give the user at least one or two options there so that if they want that data, they can export it and reuse it later. Okay. And that is my bits on custom tables today. I haven't forgotten about Ben's questions. So if there are no other questions at this point in time, we can get back to Ben's question. But I just want to leave a space, grab a sip of water, and check if there are any other questions before we go back to Ben's. I'm just scrolling back to find Ben's question. I'm going to open up this GitHub link because Ben has kindly shared it with us. Okay. It's difficult for me. So Ben was saying that back here, PHP notice on fresh installs were great in the tables for the first time. Table doesn't exist. Here's a link to the bug. It's getting called on the net hook versus activation. So it can check for updates to the table as new releases get published. Interesting. So looking at this with the limited knowledge and information that I have been, I would, and this is my opinion. This is not the opinion of the WordPress project or anybody else. This is just my personal opinion based on my experiences. It sounds to me like you need to separate your activation and your updates in one of the ways that I chatted about earlier. I would consider, and I want to almost find this. I can't remember where it is. So I'll make a note of this, Ben, and I'm going to share this in the meetup comments when I remember where I need to, I haven't worked on serious podcasting since 2019. But I'm just trying to remember what hook I hooked into for my updates. And I found that to be the best way to handle this kind of thing. So leaving your activation as the activation hook and then your upgrades as probably on the net would probably make more sense and would probably prevent this issue just because of how WordPress executes. That would be my sort of gut suggestion based on what I'm seeing there. So maybe have a look at that, maybe try that. Otherwise, I don't know, to be honest, I'd need to dive into the code and dig into the code more, but that would be my suggestion to maybe try and separate your activation and your upgrades because your plug-in activation, I can't remember the exact process, but I think a net runs before plug-in activation in the WordPress request. And so that might be why you're having that error. So I think I might have actually moved my upgrades to the WP hook and not the net hook for a similar-ish problem. But let me dive into it. I will share where that was in the code in the Meetup event after this, maybe not today, maybe sometimes tomorrow. And that might give you some suggestions and ideas, but that sounds to me like what I would maybe consider doing is moving the activation into the activation hook and then moving the upgrades out and using that whole storing of the upgrade thing into a separate hook somewhere. I hope that is helpful in some way, but that's how I would do it at least. Okay. I thank everybody for their time. I hope that this was useful and enjoyable. There's a whole world of, Rico says, can you share the GitHub URL? Yes, certainly. Happy to share that. Let me grab this quickly. I will freely admit that there are some design decisions that I made here that weren't the best. So don't use this as a perfect example, but it does work. It powers something like 60,000 WordPress podcasts. So yeah, it's doing pretty well. And yeah, thank you all for your time. It was lovely to see you all and interact with you all again today. Next week, I don't think I've decided what I'm doing yet next week, so I can't say what is coming next week. But next week will be something else. I do remember that somebody asked me about sort of more database things, foreign keys and all that kind of stuff. That is definitely a topic that I want to plan for the future, but I don't think I'm going to get to it in the next few weeks, but we will get there. But thank you all for joining. I hope that this was useful and informative and enjoy the rest of your day, the rest of your weekend, and I will see you again next week, Thursday. Bye-bye.