 But we are moving I'm moving our stuff into WordPress, so it's taken a few years to get it done But we're pretty much all our online stuff is going to be in WordPress before too long Do a little bit of coding on the side for some friends, but that's it and as you'll see from the slides I'm not a designer so why do you want to know anything about the database and Some of the things you can do with it are change your username if you go to the WordPress dashboard It says username. You can't change this. Well, you can change it Used to work with a guy and his name was Phil Ricks and they it was a university and they automatically assigned you your User ID was your first initial and your last name. He was not happy So you can go in and change the username from here You can make a user an administrator Have you ever inherited a site and you don't have access to you don't have an administrative access to the site But you have access to the database Well, you can make yourself an administrator with the date by editing the database. Can everybody hear me? Okay, okay? Deactivate plug-ins. It's if your site's broken you and you uploaded a plug-in and it Totally white screen to death. You can't get in to do anything You can go in and Change your plug-ins folder. You can rename or delete that one plug-in or you can do it from the database Probably a lot easier to do it using FTP, but if you don't have FTP and you have access to database. It's another option Why are you here just for general understanding what's going on behind the curtain and You can also have if you have your own plug-in You can have your own database tables that are in the WordPress database but you can add your own tables and Access the data store data read and write data to those to populate your own tables your own data your own plug-in and We'll see we'll show you what that looks like So when you go to create your WordPress install It asks you for the database name Username and password. This is the database username and password and You can create The database be whatever you want to call it Password don't use password But in this case it's local host and usually your database is going to be local host because it's local to the It's on the same server as the as your WordPress installation. So it's going to be local host in most cases the prefix is Can be anything I think now they automatically by default it'll generate Wp123 or some random set of numbers, but I'm using Wp underscore. It's used. That's what it used to be They some people say you ought to change that for security reasons. I For security reasons, it's probably a good thing to do, but I don't think it's a major somebody has access to your database There you got bigger problems You got other issues you need to fix But feel free to change that but that's going to prefix all the tables in your database. We'll have Wp underscore users Wp underscore if you change that to be xyz it'll be xyz users So when you create when you when you go through that step and say yeah create my site It'll take that database and create all these tables So let me go here and so on your site if you using a host that has cPanel you'll probably see something very similar to this and This my SQL databases table or page that has a list of all your tables You can create tables and users and things from there This my SQL database wizard will let you it'll ask you the questions like what's your username? What password do you want? What table do you want? What permissions do you want the users this user to have and you can do all that and you'll may probably see PHP my admin and that's the tool we'll use to go in and actually get into view and edit content in the database But these are tools that make it really easy for you to Man create your database. So before you create your site. You got to have the database on your server and That's the tool you'll use to create that database So when you create your database or when you create your site when it goes to the install process It's going to create what? 11 tables The and we're going to go through each one of these tables and explain what data is in there How it's structured and what you can't can and cannot change if you change stuff in here everything from WordPress is Generated from the database tables you can break your site so do so back up always back up everything before you do anything in here, but Only change what you know you what you're changing so if you're interested here's the database structure and You can go to that site and it has a list of all the tables and it's it's a Overview of everything that I'm saying today or not everything but give you a good overview of what I'm going to talk about and The plugins can add their own tables to that So these are just the default minimum tables that it come with in WordPress by itself that WordPress needs to have Show that so this is PHP my admin so when you log into PHP my admin you it creates this table and you can use this to go in and Manipulate and view your thing your your database So you've got the left column and you can look at the structure of each table Browse lets you see all the contents of everything in that table and we'll play with this later so we'll start out with the users table the first thing you're going to is create a user and User number one is the user that you created when you started when you created the WordPress site It asked you for a user the admin user. So user one is always going to be an admin user The user login that's what you put in so these are the the columns are rows in the table and Are that a column these are columns if you look at it in PHP my admin the Columns and the values for that so for each user You they've got an ID everything referring to that user. It doesn't refer to J. Dornary. It refers to user ID one That's why you can change that user login here, and it's not going to break anything shouldn't break anything Now you notice that user password that is not my user password That is a MD5 hash of the user password now different plugins will make change it So it uses a different hash code, but you should be able to put in an MD5 hash and The plug-in will accept it you can log in and then it'll change it to whatever hash it's using But I'll show you how to change that So if you forget your login, and you can't get in but you have access to the database You can change that password, and I'll show you how to hash that the user nice name and Is different from the username? I learned this one the hard way If you go to the the authors page each pay each author each contributor to your site has an author's page if the username has spaces or apache fees or some other characters that aren't underscores hyphens and alphanumerics Then the user nice name is what's going to be used for the slug of that user So if you change one you probably want to change the other 99% time they're going to be the same unless the username has some character. That's not a URL part User email user URL I can come in here and change the email and not worry about Notifications getting sent so sometimes I want to touch things without Warning anybody without sending out notifications. This is one place. I can do that shows the date when I was registered Activation key that's usually going to be empty But if the user goes in and says I forgot my password it will put a value in that field and then that's what's used that's What's it is sent to the user to click on to? Get their past to reset their password. So and then once they reset their password That's wiped out User status is no longer used that's a left over from a really old version of WordPress and the display name is What is shown when I log in up in the upper right corner? It says welcome John Donner That's what's shown up in that upper right corner so if you go into a The dashboard this is that these are where all those values are coming from or are used It pulls it from the database and puts them in here So to change the username you go to PHP my admin is the easiest any database editing tool You can use but PHP my admin seems to be the most common and popular. You have questions. Okay So so I'm going to go to my users table And here's the here's what that table looks like in PHP my admin I can double-click on a cell and type in it replace it So if I wanted to change my user nice name I can double-click there and it puts me in the edit mode and I can edit that cell Escape key or hitting enter will accept that change. Yes No, it is not So I can click edit if I wanted to change the password. I'll click edit and Here's this user pass I Can change this to say md5 and then replace that with My awful password And I go and you'll see it's no longer got my password my awful password in there It's got some 32 character hash of that. So now I need to log in with my awful password So that's really quick easy way to change it. Yes No It's a it's a one-way thing. So I can't look at this now if I had I'd have to have a Table that has all the possible passwords now a lot of places what they do is they'll have hackers will have database of Common passwords and they know what the hash is and they can say oh this one's in our database We know what that what that is that corresponds to password. So that's how you change the login And we change that. Oh This is just showing what we just did Here I can also go in and change the Display name user nice name all that the same way and we talked about that. Yeah, gotta be sure you click go or save Okay, the user's meta table This is where you get the the user role is in here. So this is information about the user So you've got the User nickname the first name last name description. Do I have rich editing turned on or in The when you create a user or edit a user you can give them permission to use rich editing or just plain text I don't know. Does that still even work? Is it still there? It used to be but I don't know why they would still have that Anyways syntax highlighting the admin color a lot of things that I can change as a user This is where it remembers if you were look using the editor and you clicked on Text view Instead of visual you select text when you come back tomorrow and you open another page It's going to be in text view in one of these settings one of these meta keys has How are you using the visual editor or the text editor? So it knows every time you open a page. It's gonna say, oh, he likes the text editor I'm going to show him the text editor instead of the visual editor These WP capabilities these are the roles that I'm that I have User level that's deprecated that used to be there used to be multiple levels But now they've done away with that instead using capabilities serialized data you notice that for the user of the capability it had this funny string of text That is what's called a serialized array and it is a string representation of that single Ray now you can have multi arrays multi level arrays in here and they can be stored as serialized data and There's a couple of tools that I've found that really work that I like a serialized editor you sample the app That's broken so It let him know yesterday or the other last week there things not working right So let me know I can't copy it from here Come on Copy this but I can put text in there and it shows me so I can change administrator to Editor I'll come on really. Oh, that's because I'm doing it in the wrong place Sorry because that change editing it there will break your site So or break that user from being able to log in I need to edit here And this is shows me what the serial what the new serialized data looks like So, you notice a one this s6 indicates that it's a string of six characters So this is why because there's serialized data in your database You can't just go and say I want to replace all the occurrences of this String with this other string because if there's any occurrences of if they're if the two strings are different lengths It will break all the serialized data It won't be able to unserialize that array and use it So if you want to edit anything that's serialized you want to use a tool like this paste the old one in put your values in here and then Copy this and replace that in the database. So you're not breaking things. You still have a valid serialized Yeah It's a string of one character and then the value is one So this one is representing this value. Oh, that's that's basically saying it's a true false So that value represents true So for the editor permission I have I have permissions but you'll see serialized data elsewhere in the site and That's why you don't want to use just globally go in and replace everything in the database because if there's any if that values Somewhere in serialized data it will break it So if you want to change somebody to be an administrator You search for that user ID in the table. So that's gonna be in This user meta table. I'm gonna look for user with the ID one if I find what my ID is so in this case my idea is one and I'm looking for a meta key with the value of WP underscore capabilities and I'll paste that a 1s 13 administrator be one and that will make user one an administrator So if you don't have access to your site And what I do I don't remember that I go to another site where I'm an administrator go and find that code copy it and paste it Or you can find this presentation say here's the string of text that I need to copy in that to make make me an administrator And this uncerealized comm does the same thing you put your block of text your serialized data in and it tells you Here's here's the uncerealized. I like the other one because I can go in the serialized editor lets me actually go and manipulate it Okay, the WP options table This has information about the site so these are all where all your options for the table are for your data for your site You can a plug-in can store options in here So a lot of sites you may have some very variables that you plug in that you answer questions And it'll store it in the database So the site URL I was doing this just the other day. I was moving a site Well, I had a database and I Put it over on the new site. I restored it used Upload draft and I don't have the pro version So I've moved the site over there and everything worked. I restored it to the new location But when I clicked on when I went and visited the site it Loaded the index dot PHP file. It looks at this site URL and say oh, here's the URL So it redirects to that URL which was the old the demo site that we were building It's like I couldn't get to my new site that I just edited so what I did is I went into PHP my admin and Changed the site URL and the home page so that now When I click on when I go to that page, it doesn't redirect me to the other site So this was a tool that came in really handy just yesterday Friday But blog name description all these are Options that you can set about the page about that site and there are literally hundreds and the longer you have the site And the more things you do with it. There may be thousands of rows in this table So looking for through here could be quite long But these are just some of the type things that you would find in that WP options table So deactivate plugins. Here's if I wanted to deactivate plugins There is a active plugins Field and I would just change that to a this a 0 colon 0 a colon 0 colon Curly bracket curly bracket will say it's an empty array. So basically there are no active plugins So let's go and look at that. So I'm looking for active plugins in the meta value So we're gonna go here We're gonna go to the Make this a little bigger we'll go to WP options and Use search and the option name is active plugins And it brings this up. I can Copy all that I'll go to my serialized editor Paste it in there and here's the value Of that. I don't want advanced custom fields to be active. I can remove that copy that and paste it into Replace that this field here the option value With just the one plug-in and I've disabled that one plug-in But you got to use the you got to use the serialized editor to do that You can't just come here and say oh, I don't want it looks like I just want to get rid of this You can't just go and delete that one plug-in that's listed there Because then it breaks that's no longer a valid serialized data and it will break your site So that's another that's another way to get around your Changing the plug-in. Like I said, you can do that with FTP and remove rename or delete that plug-in folder Or you just totally rename the plugins folder and that will deactivate all your plugins And then you rename that plugins folder to plugins That plugins folder is in the home slash Plug-in WP content slash plugins folder So the WP content folders where your plugins are any questions so far everything Yes, how many I think it's how many that very how many key how many Yeah, how many elements are in that array and I'm not sure if it's how many elements or how many top-level elements I'm not sure I'd have to get in play with it. I don't care. I use a serialized editor and it does it Calculates it for me Yeah, yes Yeah, yeah, it just makes it so it's no longer active And then your site would if it was working before you installed and activated that plug-in It'll continue working that plug-in is still there if you click activate it It'll probably break your site again unless you make some change or fix whatever the cause of breaking your site was Well, you can delete it what once you if you did it in the database and and deactivated it from here Then you can go to your plugins file page and and delete it from there Or if you and that would be if you don't have FTP access if you got FTP access You can just delete that plug-in folder and It's gone and it'll deactivate it automatically. Yes Yeah, right So this is just an alternative to FTP I could do everything or for that deactivating the plug-in I could do it with FTP or I could do it here. So whichever That one plug-in Well, when you go to the page it'll look and say that plug-in is not here. I'll deactivate it It will update your database when it based on the fact that the file is not there Yes, sir Okay. Oh, thank you. I'd need to repeat. Thank you for binding me to repeat the questions for the camera She asked is Do I if I delete the plug-in? Does the database get updated? So if I'm using FTP or whatever file manager, however, I get to the site and delete the plug-in folder Not the plug-in folder, but the active plug-in folder then when you visit this page with WordPress when WordPress connects it'll go and look and say, oh that plug-ins There's no file there anymore that folder doesn't exist and it'll update the database to that field in the database to show that That plug-ins no longer in the list of active plug-ins So it doesn't matter whether you do it by deleting the folder that plug-in folder or Doing it from the database Either way work So depends on which is fastest and easiest for me. Do I have access? Do I have easy access to FTP? Do I have the database open or do I have FTP open? Where's where's my finger? All right, the post table. This is the big one. This is where all your posts are It's not the biggest table. This is where the information about your post the title and stuff is So who's the each post has an ID and Posts are pages pages are posts Attachments are posts in Gutenberg the Remembered blocks the remembered blocks that what they're called Remember blocks. Yeah, remember blocks that you create in Gutenberg are actually a post Revisions each revision is a post I think Custom every custom post type has every item in that custom post type is a post And each one has an ID if you go to your post or pages editor You'll notice the ID at the top in the URL. That's refers to this ID Post author it stores the ID the user ID for the post author is stored in this table When the post date was made and the date GMT time so the post date is based on the time on the time zone of your site the post date GMT is GMT time for that same. It's the same time just local versus GMT if you change your time zone on in your settings, it's not going to change all your posts until they're Resaved so you would have to go and update each post for that post date to get giant change to the local time zone The post content that is the content block of that post now if you've got custom Fields or other information in that that stored in the post meta table But the content the the content of that post is in this table here in this field The post excerpt post status whether it's a draft Revision published. That's the post status Comment statuses. Are you allowing comments or not? So that'd be a one or zero ping status same thing The post password. Did you put a did you assign a post a password to the post? It's stored here the Post name is going to be the slug of that post the permalink for that post Yes No, she asked does a plug-in like that would be like Yoast add information to the post table. No, it does not All that data goes into the post meta table now Do they have their own tables? Well, I'm thinking they may have both it may be a combination of the two I'm not sure exactly how Yoast does it, but it could be done either way Um Those parent Like if your page your post has a parent that would be the ID of the post ID of the parent GU ID is a unique identifier and it's got the URL of that Post don't worry about it. Don't change if you move your site the GU ID will have the old site URL in there You don't need to worry about changing because it's not used for anything It's just it's a unique identifier, and I don't know where if it's used anywhere. I haven't found anywhere Menu order if this is a menu item a menu item it would have the order The post type is it a post is a page is an attachment is a revision. Is it some custom post type? Same mime type if it's an attachment. It'll have the mime type. Otherwise that mime type field will be empty How many comments? So the post meta table Has information for each post a lot more information for each post so in this case the Meta post ID was the I had a page that had the big boom design logo center PNG was the featured image so Post number two had that image on it. So this is letting me know that the thumbnail ID For post two is number 19 The post and that's the post ID of this attachment So it's also saying the attachment is WP attached file. Here's where the file is and Here's metadata the width and height information. That's a that's another array and The alt tag is here So this is two different posts and how they relate So let me let me go back in We'll look at one of these So here's what that post table looks like or some of this stuff And let me show you the post table. So we'll look at posts and Number two, this is I'll edit so it'll show it a little So this is this is just a let me scroll down and make sure I'm speaking right. This is a page So the post type is a page And we'll scroll back up to top its ID is number two. The author was is number one It was posted before I changed the time on the time zone on my site And Here's the content of that page the post title was sample page and There is no excerpt for it is published There are no comments allowed on it things allowed The post name so that's the slug so a sample hyphen page It was last modified Both local and GMT time It does not have a parent There's the the GUID Not on menu and the post type is a page We go back Yeah, no all all post the the posts table Has all pages post so this ID field is unique. So there could there's not anything else with that idea of to It would become a post now. It would probably not work, right? Or it would probably confuse it because like I need posts have something else and some fields aren't there Yeah, but yeah, that would Very well might break it. We look for well, I know 19 we look at 19 was a was a logo that I uploaded so The ID is 19 author. I'm the one who uploaded it the date and time I uploaded it. Here's the title of that Post status it's going to inherit status open I think anything else in here of interest So here's where it says it's an attachment and it's got a Image it's a P&G image and there's no content for this and no excerpt because It's a it's an image. It's an attachment They wait when you say that post meta in the post meta table. Oh, yeah. Yeah, plugins will yeah They can stay saves data in plugins can save data in the post meta table Okay, I was the post table. This is the post meta table and yes plugins do save a lot of advanced custom fields We'll save a lot of data in this table for each post So if you've got an advanced custom field on a post It's going to put that value what the field name is here and what the value of that field that you put in here And it'll actually actually have another I do know but ACF it'll have another field. It's got the key and the The the field name and the field key a table a row for that for each of those items Okay, the terms table This has all your all your all your categories and tags are in this terms table and what this what the slug is What the idea is and then you've got a term tax not taxonomy table which says, okay, this is the term ID Is it a category is it a tag is does it have a parent so that stored in the term taxonomy table and Then we have a terms term relationships table which says this tech this object ID is The post ID and the taxonomy ID and then what order so usually orders all zero, but this is saying that post number one has tags is tagged with The three categories one two and three and the tag so it's got all these categories. That's that post had all for those category or all three of these categories and the WAWC AVL tag We can look at that later. So You look at your database and you say I've got a lot of other tables other than those WP ones that we just looked through and they come from plugins So So right now, I don't have any of the all I've got are these dozen WP tags. So What's going was it my awful password? Yep, that was it I got two updates since this morning So I've got plugins Hey, there's a new update for Gutenberg. I hate to see those things I'm gonna activate word fence Did you know that that little spinning thing when it's going counterclockwise it's sending data and when it's going clockwise It's receiving data. It is Matt Mullerwig's first plug-in. It's just a It's a plug-in for you to copy and use just as a starter You can delete it has no value. It is a worthless plug-in But it's on every WordPress site initially Yeah So I'm going to refresh all I did is click that installed WP or word fence and now I've got a Bunch of tables in my database You know word fence those a ton of tables in your database Now some plugins will use the WP options to store data Some will use the post meta to store information about that post some will create their own tables. So Depends on the author of that plug-in and how it best does it there. So what their issues are? So now each one of those tables has its own Here's the here's the WP config. So these are the settings for WP or word fence So I'll notice all the word fence. They all start with the WP underscore But then they all prefix with WF. So I know that okay, those are word fence Hopefully you can figure out from the prefix which plug-in that those tables so Yeah, so yeah, if the developer did if I go and delete Uninstall and delete word fence these tables stay here so I and the advantage of that is if I come back next tomorrow and say I want to reinstall Word fence. It's got all my settings all my all the old data is still there Now a lot of plugins will give you the option if I delete this plug-in Do you want to delete all the all this stuff with it? Some do and some don't So this one doesn't so if I deleted word fence and I really want to get rid of word fence off my system I'd have to come here and delete all these tables So yes He's asking are there plugins that will go in and delete these tables. I don't know of any I've never looked at it because it's easy enough for me to come in and and do that. Does anybody else know one WP sweep. I don't know. It's not saying anything about It's not doing Site ground has one. Okay So it looks like WP sweep does a lot of stuff, but it doesn't remove It optimizes database table, but it's not removing unused database tables Looks like a good plug-in what nine months 60,000 users. Those are good things four and a half stars. That's all good Up to the current version. So that's good All right, so now I can go through here and do all my settings and then it'll populate all those tables Questions yes that you would do at your host That's that says that's your hosting service determines which version of PHP you're running So yeah, you need to communicate with them and depending on how you what kind of a host it is what? Yeah, they may earn it. Yeah, they may say no you're on yours is at 7.0 or 7.1 I think is 7.1. What's the current version 7 1 7 2 is the new version of PHP? Okay 7 2 is the new version of is the current version of PHP But if you it as long as you're running 7.0 something You're you're good seven obviously 7.1 and 7.2 would be better but uh If as long as you're not on five something There was no six so if you if it says you're on six something you got it somebody's lying to you they skip six so if you're a plug-in developer you can this is the code that you would use to Connect to your own databases and you can use them my WPD actually would be yeah You use WPDV and you can look at the reference for WPDV if you want to use your own plug-in Use your own database tables in wordpress in your plug-in That's where you go to start with that and I'm not going to get any deeper into that tonight today Then just say there's if you're a developer and want more information start here or come see me and I'll help you here's a bunch of Tutorials and help and where you can go find stuff. So again, these will be on the site. They're available And that's it Any other questions? That WP sweep will optimize a database Yeah, there is an operations you can go here and Maintenance analyze check defragment optimize the table. So It's in PHP my admin you can do that WP sweep would be easier because it'll go to do them all But yeah, you can do some optimization here and if you know SQL and a good browse This is the SQL statement you can edit it in line or you can click here And it'll help you with your SQL statements. If you want to learn SQL This is a good place on your local dev environment not on your live server Yeah, and it's nice it before you go and do that under operations I can go and Copy my table So this will make a real quick way to make a copy of that database table before you start messing with it So if you break it you can Rename this one back to what it should be Yes Yeah There are tools. I've used razor SQL. There's a lot of other peach my SQL tools editors or Programs that you can install locally And you can have the program installed locally and use and work on a remote database Yes, I'm sorry say that again. I want to use Okay, I'm not familiar with what you're talking about Does anybody else can anybody help them using cloud air doop? Sorry, you need to be in a more this is not the advanced Becky people. Yes, ma'am You'll want to use the rest API and WordPress has a built-in rest API and endpoints and that way you get your She asked about Using data from WordPress outside of WordPress So MBC is So you would use the rest API to get the data and then whatever program you're using to Display it you would use that data So you have an endpoint For every site there's a WP JSON and in return I Didn't have enough information on here But this returns Data so each post will have its own All the data about that post you can pull out of here And it's pulling out of the database, but it's parsing and doing everything you need So you could get it out of the database or this would probably be better way to do it because it's parsed and run through the WordPress function I Don't know that resting the rest You have a question about savings Yeah, yeah, that's exactly what you do So like Sometimes has updates What is the Updating from one version to another No, what's being updated is the My SQL code so the code that's reading the data and All the functionality that my SQL has is what's being updated when you go from my SQL 5 to my SQL 8 But it'll have new functionality and hopefully better speed and So it's It's like changing versions of PHP. It's the data doesn't change. It's changing how how How how my SQL works how it's parsed How my SQL is you hit you hit the my SQL program to do its work to I Send that select asterisks from table name And then those who gets it reads the data file, but it's those who runs the stuff It's like updating word that you're not changing the documents You're really a change updating the program that's dealing with the doc. It hasn't been features and speed changes like that So that's what updated my SQL Any other questions This one again, these are these slides are available So here's the most here's the endpoint for work in If you have it closed yeah, so if you go to the schedule page and click on And I don't have the slides up yet. Oh, I don't have the slides up here That's my job for tonight Thank you