 Hey everybody, this is Brian. Welcome to the third ye tutorial. Today we'll be discussing MySQL, GEE, and CRUD. A lot of acronyms. All right, so open up your main PHP. You should know where that is. If not, it's under your web root protected config main.php. And if you've been following along, you should have uncommented out the GEE section in the modules part of the array. Give it a password and give it an IP filter. That's important. That just means people from outside that IP address can't hit it. And also scroll down to databases. We've commented out the MySQL, or I'm sorry the SQLite, and we've uncommented the MySQL. We're going to be using a database named test with the username of test and a password. All right, so now that that's all in main.php, how do we actually set that up? Well, I'm going to be using something called MySQL work branch. You can use whatever you want. Some people use command lines. I wanted to use a GUI interface because I realized that some of you databases may be new. So just log in. Users and privileges, we're going to actually create a new account. Week password. Who would have thought that password was weak? Anyways, account limits. We're going to give it unlimited. We want to, for these tutorials, just give it DBA access, which is basically God mode. You can do everything. In a production system, you would want to follow industry best practices and lock this down as much as possible. But for these tutorials, I'm just going to do everything because why not? All right, now that we've got our user account, we need to actually create the schema. Schema, which is also called a database. We're going to call it, you guessed it, test. Hit apply. And the thing I love about MySQL work branch, especially for new people, is if you have no idea what's going on, it actually shows you the query it's going to run. So we're just going to create a schema named test. There it is. Now I have to actually put some thought. What do we want to do? Let's do the school problem. If you've ever gone on a programming IT interview, they typically throw a bunch of questions at you. One of them is, how would you design a database for a school? If you think about that, it's actually a very complex question because, well, you have classes that have students and teachers. So how do you link all that together? We're going to create a table. We're going to call student. And notice how it creates this primary key for you. We want the primary key not null, unique, and we want it to auto increment. That's actually very important. I'll discuss that in a second. We want to first name and last name. And we'll flush these out a little bit later. This is, like I said, just for these tutorials. Not going to be doing a whole lot of data access here. This is going to be for ye. And if you're new and you have no idea why I'm changing 45 to 255, let me explain. VeryCare is an array. It's just an array of characters. The default was 45. I'm changing 255. So the size of that array is 255 characters. Believe it or not, there are people with names that will hit or exceed 255 characters. That's rare, but it happens. 45 is kind of small for a name. So we're going to go ahead and hit apply. Once again, you'll see the query that it's going to pump out to the database. And you'll see whether it executed or it failed and will give you an error message if it fails. And you can see down here, I have to apologize if you can hear my cat whining in the background. I'm the invisible man in this house until I start doing a tutorial. And then the cats just want my attention. So we're going to plug on here and do teachers. Oh, how the f***ing kitty go away. Kitty, I'm teaching people. This cat, I love her to death. I've had her for 20 years, but she's going blind and she's deaf, which is why she meows so loud. First name, go to sleep kitty. Very sorry. All right. 255. Hold on a second, guys. Kitty, go bye-bye. Go do something else. Go. All right. So once again, primary key, not null, unique, auto increment, apply. We're going to save those changes. Oh my gosh, she must have just woken up for a nap. She's very chatty. All right. So now we're going to create the classes table. This is going to be a little different. Once again, primary key, not null, unique, auto increment, but we're going to add what are called foreign keys. So we're going to say student kitty go away. Cats like swiping at my hand here. And we're going to set that type to int. And we're going to say int. Now if you're using a larger database, you can use big int instead of int. The only difference is big int is a massive number where int is a smaller number. You can go ahead and change that if you want, but we're going to hit apply. Now we're going to add the foreign key. To do that, you just click, select students, select student ID. And you notice how it automatically says ID students. I'm going to stop right here and say if you messed up, it will not automatically select that field. It won't even let you select it. And typically when you do that, jump into students, is because you didn't set it to primary key, not null, unique auto increment. Auto increment really isn't needed, but it's good business practice. If you don't have a minimum of those, it will not let you create a foreign key. So what is a foreign key? What we're saying is that the ID students field, this column right here, this points to a student in the student's table. It's literally the primary key of that student. It's a foreign key. Get it? All right. So we're going to create another foreign key called teacher. And we're just going to, oops, we don't want that. Derp. All right. Sorry about that. Cat's playing with my mouse. So the student foreign key points to the student record that teacher foreign key points to the teacher record. Now there's some other things like you can do operations or options. So what you can say here is the student on update, you can restrict cascade set null or no action. We're going to say no action because if we do something with that student record, we don't want it to impact the class table. Example on delete. If you delete that student record, that student no longer is there. You could say cascade, meaning it deleted the whole class. Well, the class doesn't stop just because the student decided not to go to school anymore. So we're going to leave it to no action. Just wanted you to be aware of that. It's kind of an advanced feature of some of the database functionality. Hit apply. Boom. So now we have our class with students and teachers. Pretty nifty, huh? Well, we're going to keep it that simple for these tutorials. I don't want to really cloud these with high end database tutorials. Those are a totally separate topic. The reason why we're using these foreign keys is because you will actually when you're building the models, remember model is a representation of the data, it will actually build in the relationships. So let's actually jump back to our web browser here and you see where it says index PHP question mark are equals. Well, that are equals is a route that tells index PHP where to route you within the system. We're going to actually select the slight the site slash contact type in G G I hit enter and it says please enter your password. Now this is the password that we set in main dot PHP. If you're confused, just jump back in and you'll see under the modules array, we've uncommitted key and we've set a password. So we're just going to key in a password and it says welcome to the code generator. That's actually what he is is the code generator. Notice how you can generate a controller crud form model and module. Well, high level overview will go into more detail in future tutorials. A controller controls the flow of information through the website. Notice how you can give it an ID. So you could say like class. That's a class controller or you can say pets a pets controller and then actions are actions within that controller. Typical actions would be like the index to read to write delete. That's actually what crud stands for create read update delete, which is by the way our next one create read update delete. This is how you would modify information out of a database. So you can actually generate the cloud using a model and using a controller. Now form is simply a form is just a web form. That's all it is. You can do the same thing. You can select a model and select a view and select a scenario. Scenarios are a special case like login or registration. And this is all template based. You can actually modify the code that this is going to generate. Model generator. This is actually what we're going to play with right now. This is where it reads out of the database. So you want to key in the database name like you notice how it autocompletes classes. That's how you can tell that really it's reading your database and your database access is working. Now you should know if you want to generate the models for everything, you can just type in star. Make sure build relations is checked. This will build the relations between the models for your foreign keys. Remember we have a class. That class has teachers and students. They have foreign keys pointing to these students and teachers. That will build the relationships. And you can actually use the column comments as the attribute labels. Attribute labels would be the labels of the attribute, a definition of what it is. When we do star, we're just doing a wild card saying do all the tables. It says you may also enter star or schema name dot star for everything. When you click on preview, you see what it's going to generate. And you can actually click on the link and see exactly what it's going to generate. This is kind of your last chance of, hey, do I really want to do this? And you can check and uncheck and say, well, I only want that one or I want them all. Now a module, we're going to click generate here in a second. But real quick, a module is like a site within a site or a sub site. So you have your normal website. And let's say you want like an administrative section. You'd make an administrative module. So let's actually go into modules. And you can just give it a name like administrator of doom. And it would make an administrator of doom module. And it actually puts it under the module sub folder in your site. So for this, the first thing we want to do is create our models. And we're going to just do all of the tables. Oops, not student, all of the tables. Base class is C active record. It's very important that you know that C active record is the super class, if you will, or the inherited class that we're going to use. Basically that access is the database for us and hides all that functionality in the background. So we're going to do that. And then we're going to just generate. If you get this error message, there were some errors generating unable to write. Basically what that means is, wow, yeah, we have right permission issues on that folder. The reason why I left that in is because, well, we need to fix that. And a lot of you are going to get to this point and go, what, how do I fix that? What is this? And you're going to just panic. So let's actually, we're going to open up our shell here. Kitty, seriously, go away. We're going to say CH mod. Actually, we're going to say CH own. We want to, sorry, cat is like totally distracting me. Enter your name. Mine just happens to be root shell. It's like my gamer handle. And then the group is dub, dub, dub data. And we're going to say fair, dub, dub, dub HTML. And this is test. And we want to recursively do that. And what we've done is we've changed the owner of that folder now. And then we want to sudo chmod user group other plus read write. And that's pretty bad practice. You don't really want to do that in a productive system. But once again, this is my dev box. I'm just showing you very quickly how to fix this. And we're going to recursively set that. And what we're doing now is we're changing the mode to user group and other read write. So we're basically saying everybody can read write to that bad practice. Once again, don't ever do that in production. All right. Now we're just going to preview it again, try to generate, and voila, we've generated. So that is how you fix a permission issue. Common pitfall for new beginners. Typically, you'd want to actually set it correctly rather than what I just did. I'm just showing you, hey, that's the quick and dirty way of doing it. Now that you've generated them, well, what has it really done? It's generated the models. I like models. Models are pretty. But anyways, when we go into the models folder, you'll see we now have students, PHP, teacher, PHP, and classes. So let's just crack one of these open. And we're going to go over this in-depth in a future tutorial. But some of this should look pretty familiar. You've got your primary key and your foreign keys. So let's actually go back into our database here. Maybe. And here's our classes. And here's our classes. I'm just going to revert that way. We have any changes. And let's say we made an oopsie. And we're like, oh, we need a name of the class, right? Because class is going to have a name. And then you hit apply. And you see that's going to just alter table. Well, when we go back in here, notice how it hasn't automatically updated. So we've got to fix that. So you just go back into models. And just for the sake of argument, you could do classes or you could just do star. Maybe if my keyboard quit freaking out. Preview. And you notice how unchanged, unchanged. So it's smart enough to know that there's no real changes needed. And it can overwrite. Meaning, oops, this one's going to have differences. And you can click diff. And it will show you the differences of name has been added. Pretty sweet. So you can see what it's going to do. It's going to delete that. It's going to add it here. It's going to move it there, et cetera, et cetera. Just wanted you to really be aware that's a very powerful feature that a lot of frameworks just do not have. And you go ahead and generate it. Jump back in that beans. And you see, there it is. And we're going to go over models in depth in a future tutorial. But you should just be aware of that's how it is really done. All right. So let's actually jump back up here. Now we're going to create our crud. Our create, read, update and delete is crud. Here you have to actually give it a model class. What does that mean? We're going to give it a name of one of those that we just generated. So we got students. Make sure that name actually matches up. Well, through the magic of copy and paste. And you'll see this is what it's going to generate. Now, crud, create, read, update and delete generator. So this is creating the web pages that will allow you to create, read, update and delete. And you can see them here. It's going to create a controller, which we'll talk about in a future tutorial. Some forms and then various web pages. So we're just going to go ahead and generate those. And it says the controller has been generated successfully. You may try it now. Interesting, huh? Wasn't exactly what I wanted. So we're going to say, Now you may be stuck at this part like I get sometimes. And it says, hint, you may log in with demo, demo or admin, admin. So we're just going to say demo, demo. Now you're logged in. What we've done is we've just demonstrated that just right out of the box with any modifications, this has already got security baked into it. Being a certified ethical hacker and a CISSP, I'm really big on security for obvious reasons. So I looked at a lot of the frameworks and I wanted security baked in from the ground up. And that actually has it with you. But anyways, so here's our students. You can create and manage. So let's, we can create a student or we can manage the students. Now we're going to go over those in a second. But we want to go back here and we want to actually say, all right, let's do the classes and teachers. Whoops. Teachers doesn't exist, huh? Teacher. Ooh, did I boo boo? I did boo boo. Ah, yeah, there's no S there. That's why. All right, well, it is what it is. We're going to go back here and you can generate it. Or if you're real nitpicky like I am, you can actually go back in and fix the problem. But for these tutorials, I think it's just fine. Notice how when you hit try it now on teacher, it just logs you in. It's because you're cached as login because you're logged in as demo. So we've got our stuff in there. Notice how it says our question mark equals teacher. That's the route. We're going to talk about that in our next tutorial. That's all for this tutorial. This is Brian. Thank you for watching. I hope you found this educational and entertaining.