 What is going on everybody, my name is Roddy and you are watching my channel, Roddy the Brand. Today, I'm going to show you how to create a full crit application using Node.js and MySQL. We will be using a few dependencies such as Express, Express Handlebars, the body parser, .tmv and MySQL. To run my database, I will be using XAMP but feel free to use whatever software you wish. For the layout of our website, I will be using Bootstrap and we're just going to load it from a CDN and I know that most of you are familiar with Bootstrap and it's very easy to get going. Now, before we start, let me show you a quick demo of what we'll be doing. Okay, if this is something that you want to build, please stick around. This tutorial did take a very long time to create so I would appreciate it if you smashed the likes, consider subscribing to my channel and if you have any questions, comment below. Now, let's jump in on the computer and get started. Welcome everybody and let's get started by creating our project. Before we begin, make sure that you have a MySQL database available, whether it be locally or remotely and for this video, I will be using XAMP. XAMP is free for Mac, Linux and Windows. If you wish to follow along, you can pause the video, go ahead, install XAMP, configure it and make sure that MySQL and Apache are running. We'll come back to this later but if you are using alternative software, don't worry, that would be absolutely fine as long as you're using MySQL. Let's minimize this and start by initializing the new project. As you can see, I have created a new project folder called user management tutorial and on Windows, to go straight to this folder in PowerShell, I can do left shift, right click and open PowerShell window here. This will basically just CD to the project folder and if you're Mac or Linux, you might have to just CD manually, which is not a big deal. Once you do that, CD to your project folder, let's do NPM in it and create a new project. The only thing I'm going to fill here is the package name. I'm just going to call mine something like Node.js and then user management, something like this would do and then just key press and enter until we're done with the setup. As you can see, this created package.json file for us in our folder and now we can start installing the dependencies that we need for this project. We can actually install all of the dependencies at once doing NPM install and we can start listing them. The first dependency that we need to install is express and this is for all express server that we're going to create. So let's do express. Then we need .emv and .emv is basically going to keep all database credentials. We have express handlebars. We have the body parser and the body parser is basically a middleware that is going to help us pass data. For example, from some of the forms for me submitting, we can grab some of the data and maybe insert it into the mask girl, which leads me to the last dependency that we need to add, which is my scale. Okay, press enter and this should take a couple of seconds to install. The next thing that we need to do is install Nodemon. Nodemon is a development dependency and that's why I'm installing it separately. So let's do npm install dash dash save dash dev Nodemon and Nodemon will basically restart the server for us automatically every time we make a change in your project. Otherwise, we'll have to do that manually and that could consume a lot of time. Now that we have pretty much all of the dependencies that we need for a project, we can finally open our project in our favorite editor for me. This is video studio code, but just open it in the one that you usually use. So I can do code dot and this will open the project for me. And if you use another editor, obviously just go to file and open your new project. Once you're an editor, just open package.json so we can have a look at it super quickly. And as you can see, we have the name, version, description and so on. The important bit here is the dependencies that we just installed and the development dependency, which is Nodemon. Now if we want our project to run with Nodemon, we actually need to add another line and the scripts. So let's do comma and then inside here, we need to do start and we need to tell our application to start with Nodemon. To do this, we simply do Nodemon and we need to specify the name of the file that Nodemon needs to start and we don't have it yet, but we're going to create it right now. Let's call this one app.js like so and save. Now let's create this app.js file quickly inside here inside the explorer on the left side and we can just do app.js like so. All right, we are done here. Let's start by setting up an express server. Setting up an express server is actually fairly easy. The first thing that we need to do is require express. We need to initialize express, set up a port number and then listen on that port number. Let's do that. So let's do const express equals require and then we require express like so and close. We now need to initialize express and to do this, we can do const app equals express like so and close and then we need to specify a port number for our express server to listen on so we can create this. We can create another const port equals and here if you wish to publish your app somewhere, we could attempt to use the environment port number just by then process dot environment ENV dot port or we can use a default port number of 5000 for example. If this port is in use, you can try something else and that should work. Okay. The final thing that we need to do is tell express to actually listen to the on this port to do this. We can simply do app dot listen and then we can pass the port number comma and then we need a arrow function here. So we can do this and let's console or something like listening on port number of 5000. So we can do console log log and then with the template literal, we can do single slanted quote and do listening on port and then dollar sign curly brackets and we pass the port in here and we should be good to go. If we now go back to the portion, I've actually cleared this just now, but if you go back to the partial and we do MPM start, you will see that hopefully you will see that not one is starting the note app.js and it's listening on port 5000, which means that or express server is now working and we can continue by adding or other development dependencies. Okay. I was thinking that it might be best to just list all of the dependencies first and then we'll work our way down. So the first dependencies that we need is the express handlebars. Let's do const EXPHBS and this is basically short for express handlebars and this will be equals require and then we are requiring express handlebars like so and this is basically or rendering engine. Let's now add the body parser so const body parser like so require and then we require the body parser so and the we also need to require my SQL. So let's do const my SQL equals require and then my SQL. The last thing that we need to require is the dot EMV. But this one we can do slightly different. We can do it by doing require and then inside here, we just say dot EMV and then config like so. Now that we have all of the dependencies, we will go through each one and set everything up. I'm thinking let's start with the body parser. So let's include the body parser in here and the body parser is basically a passing made aware. As I mentioned earlier, this will help us to pass JSON data through our forms. So let me show you how to do this. I'm going to keep everything clean with a few comments. So everybody knows what it is and you can reference it later in the article that I will be creating. So let's do passing made aware and this one will be pass x www form and URL encoded. All right, to do this, we can do up dot use body parser and we can do the URL encoded in here and we need to set this to be extended as false. All right. If you want to understand more about this, you can always go to the documentation under npm, npm js.com, search for body parser, go to body parser and inside here, you will see someone here. Here we go. The URL encoded returns made aware that only passes URL encoded bodies and only looks at request where the content type header matches the type option and so on. So feel free to explore every single dependency and the documentation. It's pretty good usually. So yeah, let's continue. Let's go back. One thing that I didn't show you is that our service running, but if we go to the website and if we refresh, you should get this message cannot get. And this is because we actually haven't set up any routes and we're not rendering anything just yet, but we'll do this in a second. Let's go back to the application. We've done the URL encoded, but we also need to make sure that the body parser is using a JSON. So pass application slash Jason, and we can do app.use and then body parser.json. Close this and we should be good to go. We're done with the body parser. We can now use it and I will show you how to do that later on once we created some of the forms. Another thing that I want to do early is set up my static files. Now static files are the files that you might want to add on your front end such as you might want to add a custom CSS file. You might want to add some custom JavaScript files, or you might want to have an easy access to your images and to do this, we need to basically set up this comes from express. We need to set up or static files just by using app.use express and then we say static and then inside here we say the name of the static folder. In my case, I'm going to call my public to stay consistent with all of my tutorials. So let's go public and close. We will need to create this folder as well. So we might as well just do it now as we're here. So let's create a new folder in the Explorer here and call it public. Inside here, I'm going to create another folder called CSS and inside this folder, I'm going to create a file called something like let's say main dot SCSS. I just like using SCSS. But of course, if you don't like using SCSS, just use main dot CSS and write your styles in there. Obviously, when I compile my SCSS, this will create the compiled main dot CSS for me. So let's close this and we can include this later on in our HTML. And the same thing in here, you can add an images folder or JavaScript folder and so on. I'm only going to be using CSS today. So let's keep it simple and straight to the point. Save this, go back to app.js. And let's have a look at what else do we need. Let me comment this actually. So this will be static files. And the next thing that we need to do is set up or template in engine. If you've watched my previous tutorials, I usually use EJS. But I've mentioned a few times that I really don't like the syntax of EJS. It's a little bit hard to write. And this time, I wanted to show you handlebars. I believe that handlebars is so much easier to write and it looks so much nicer. And you will see as we start writing it later on. But anyways, let's start by setting up or template in engine. So and handlebars, it's very similar to EJS. It uses views and layouts folder as a default. I will show you in a second. But one thing about handlebars is that the default file extension is actually dot handlebars, which is fairly long. So in order for us to change this, we can actually do app dot engine. And we can do HBS comma exp HBS, which we have here at the top. This needs to be outside. So and then inside here, we pass the options and the option is extension name ex here it is extension name just like this. And then the extension name that I want is dot E dot HBS to stay consistent with everything online with their documentation as well. So let's close this. And we now need to set up or view engine, which is handlebars. So app dot set and inside here, we do exactly the same as EJS. We do view engine and we set the view engine to be HBS. This is it. Now, as I said, this uses by default uses views folder and layouts. Now, layouts can be very useful, which I will show you in a second. If you watch my EJS tutorial, you probably already know how to work. Much easier to maintain your layout. Basically, that's pretty much it. So let's create a folder called views and I will explain as I go. And in this folder, we're going to have to create another one called layouts. And of course, this is the default, but you can change it if you wish. I do like the default option here. So I'm going to keep it as it is. And inside here, the default layout file, it's called main. So let's do main and HBS, which is the handlebar extension that we've set in app dot JS. So this will be our main. We also need to create maybe a homepage. So let's create one inside views and you can either call a index if you wish, or we can call it just home dot. I'm going to go my home dot HBS. It doesn't really matter that much as long as you know which one is home. It's fine. This needs to be inside views. OK, this is all looking good. We have a layout setup. We have we have a home page and I will show you how the layouts work in a second. But the last thing that we need to do in here is actually we need to render this page to do this. Let's say, I don't know, router or navigation. Maybe I don't know. It's probably route is probably the right way. OK, let's say app dot get and we need to get the front page. So basically, if we don't specify anything, this is the home page. If we specified something like, let's say about this, we'll go to slash about and this will render and this will be the route for the about page. So that's why I'm not specifying absolutely anything here. And then we can get the response, sorry, request and then response. This will be a narrow function. And inside here, we can use the response dot render. And we can render the home page. So home and we don't need to specify the HBS extension here. It's smart enough to know what to render the engine. And we can actually close this. Yeah, I think we're done and let's close this as well. And we're done. Hopefully, if we go back to the MPM here, sorry, the PowerShell, we shouldn't get any errors, which is good. And if we go back to a browser and under localhost portal, 5000, if we refresh, you will see that we no longer get that, cannot get this means that our pages are actually rendering. So if I do control new, obviously, we don't have any HTML, but let's do that. So first of all, let's set up the layout. So this is going to be our main layout of our website, which we're going to use throughout all of the pages. Of course, you can create different layouts, but I'm not going to do today. I'm not going to do this today because I want to keep the tutorial as short as possible and as simple as possible. So let's set up all main layout. First of all, let's write some HTML. And I'm going to type HTML, select HTML5. And this will create a blank HTML document. So let's change the title, user management system. Save this. And in order for pages to use this layout, we actually have to do some handlebars here and with three curly brackets like so, one, two, three to open and one, two, three to close. We need to do body. What that means is that everything we put into the home page will be rendered in here. But obviously, we don't have anything in the home page at the moment. Let me save this and show you how this looks. So refresh, we don't have anything. Obviously, the title changed, but if we do control new, you will see that we're getting the HTML here, which is good. And now if we go back and if we write, if we add some content to home.hbs, so let's say we do an H1 of hello, save this. Let's go back, excuse me. Let's go back to this refresh. As you can see, we get hello. And if we refresh the code, you will see that the home page is now rendered in here, which means that our layout will be consistent throughout all of the pages and we can just continue working on this. Now that we're here, I think it's a good time to start doing the layout of our page. And for this tutorial, I'm going to use Bootstrap because it's the most familiar framework out there. And most people know how to use it. And we can make a fairly decent looking website quick. So if we go to get bootstrap.com, I will be using the current version, which is version of five point zero point zero dash beta one. And if we click on get started, we can go to CSS here, grab the CSS. Go back to main dot HBS. And just after the title, we can paste the CSS. Let's go to view and talk world wrap. So we can see a little bit better. I think I've zoomed in quite a lot and that's why. And also I want to add the JavaScript of Bootstrap, which we can find here. I'm just going to grab the bundle. So let's grab this and this we need to paste just above or any body tag here. So let's paste it like so. We should be good to go. And the last thing that I wanted to do is add the bootstrap icons, which separate. So we can go back to grab the icons. I think. Icon's OK. Yeah, go under icons and the URL is icons.getbootstrap.com. And then under install, we should be able to grab the CDN. So let's grab that and we can paste it in here. Save. Now we should have everything that we need from bootstrap and we can start building all out. As you can see, the icons are pretty cool. You can just find the icons that you like, select on one, and this will give you the code. I already know which ones I'm going to use. So I'm going to save you the time and close this. We can also close this one as well, but I will need the documentation. So under docs, we're going to be using this. So first of all, I want to create a nice navigation bar kind of like this. It's actually pretty nice, but yeah, let's do enough. Click on the bar. And the one we can use is this one. I'm not going to spend too much time on like making the website nice because we're mainly learning Node.js here, not design. But of course, design is important. So let's grab this and paste this just above our body because we want the header to be consistent on all pages. So let's paste this and actually I can make this smaller. You can see there is a lot of this because I've zoomed in quite a lot. But if we right click and format document, this will make it slightly better, maybe. And I don't need all of the links. So I'm just going to remove some of the lists. Yep, remove. OK, let's remove all this. I'm just going to keep home for now. Because this is the home page, we can just slash. And I'm going to change the logo in here. Well, I don't have a logo, but I'm just going to say user. Manage my system. Save this and yeah, and change the link. Save this. And we should be good to go. If we check this out in the browser, you will see that. Well, I've zoomed in so much. You see that we're getting the nice navigation bar. I mean, this could be improved definitely. So let's quickly change a little bit. Instead of having the nav bar as light, I'm going to go with dark and then dark and then the search button instead of success. I'm going to do light. Hopefully this will look much better. OK, I'm happy with this. This would be OK. Now, the next thing I want to do is grab a container. The container is basically going to center a line or page. If you want to learn more about container, just go to the docs. Con containers and they give you loads of options in here. But the one I'm going to be using is just the default one here and copy this. And for the container, I'm actually going to wrap the body itself because I want all of the pages to be consistent. So let's paste this in here and cut the body and paste it in here. Safe, go back, refresh. And as you can see now, our whole page is rendered inside here inside the container, which will be center aligned. But the only thing that I don't like is the spacing. So let's just give it a little bit of padding for now. So maybe patting top five, patting bottom five. I'm sure that would look nice. Yeah, that's looking good. Before we start building or database, let's actually build a little bit more on or from page and on the home page. I want to display all of the users. And if you're displaying a lot of data, we might as well just use the table from Bootstrap. So let's do table. Grab the first table that we see this one here. Maybe we can modify it to make it slightly better looking. So let's grab this. We can now close this because we're done with it. And let's open the home HBS page, which is our home page. And let's start building our table. So let's paste this. This is our table and we're going to need to display a few things. So, for example, this is fine. Then we can display the first name, name, then we can display the last name. Then we can display the email of the user. And let's create another row in here. So, Alt, Shift and down arrow will copy this line so we can do phone. And the last thing that I want to do is action. Action will be things such as viewing the record, deleting the record and editing. So let's do action. And we need to, I mean, yeah, to keep it simple, let's remove those rows. They're going to be rendered from the database. We're going to render the information from the database. So this is going to look basically and create more rows for us. But for now, just to make it look nice, let's populate it with some fake data. So we have number one, mark auto. And then we have email.mda.com. And then we have a phone number for the phone number. We can just use this. Doesn't really matter. And for the action, we're going to have for the action, we're going to have three buttons. So we might as well create them now for the buttons. I'm just going to use three links and we'll link them as we go along. So let's create a link, well, a button link. So href equals I'm going to keep this empty for now. But of course, we'll come back to it very soon. So this will be href and inside here, we can say type button. And then class name will be btn, btn, btn dash light. Then we let's make it small. So btn small. And last but not least, we need to put something in this button. So this will be our view button. And I also want to add an icon and to add an icon. As I showed you earlier, we can do I and then class name will be bootstrap icons and then bootstrap icons dash a I4 view. I think that would be a good one. Let's just make a little bit of space. This is our first button. So let's duplicate this two more times by holding or shifting down arrow twice, and this would be or edit button. And this would be or delete button. OK, I think this is good enough. What else can we change? Yes, the icons need changing. So this icon can be pencil and the delete icon can be bootstrap icon and then person dash X, which is like a delete record icon. If we save this, go back to the page. Let's have a look at what we got. All right, this is not too bad at all. I could do a little bit of I mean, it would be nice if the text was centered and it would be nice if this was right. So let's modify ever so slightly so it doesn't look as bad as it is. So to modify the table, we can do it with borders so we can do table dash boarded, then we can make it dark at the top and class dark. I actually want to right align this action and the buttons. So let's do so let's do a class of text and let's copy this class so we can use it on the buttons here like so. Let's let's tidy this up. Save this, go back refresh. Much better, I think. I don't know if the dark bit worked. Yeah, I'm pretty happy with this. Obviously, we can spend a little more time to make it nice. I think that now that we have this setup, I think it's a good time to start working on the database and see how we can do our first query. Let's start creating our database. As I mentioned earlier in this tutorial, I will be using XAMP and I've already started my Apache and MySQL, which means that I can go to localhost slash PHP admin and visit the PHP admin page. So localhost slash PHP admin. And as you can see, I'm at the PHP admin admin page and my username is set to root, which we'll need later on and my password is set to password. So let's press OK and login here on the left side or databases. These are some other projects that I've worked on, but today we're going to create a new database and let's to do this. We can go here on the left side at the top, create new and we need to give it a name. So I'm thinking of just cool in this one, use a management underscore tutorial and well, underscore to tutorial and let's create the database. As you can see on the left side here, we now have the database. But we don't have any tables. Now, this is a good time to create a schema. So let's create a new table and this will be called users. Obviously, sorry, this will be called user. Obviously, you can name it whatever you like. And we need to and we need to give it a few columns. The amount of columns that I want are going to be seven. So let me change this and let's press go. And by the way, you can do this with SQL. If you're familiar with SQL, it might be faster for you. And also, as I am creating this, I will post the SQL file for you on my article if you wish to save a little time. But it's fairly easy to create in both ways. So I will show you. So first of all, all table is called user and the first record that I want is going to be called ID. This is going to be a unique number for every single user that we have. And that's why I'm going to leave it as an integer. This needs to be auto incremented and also set as a primary key. Now, the next one I'm going to have is first name. This is basically going to be the user's first name. We can set this to voucher. And I'm going to keep it simple for now, maybe like 45 characters. But of course, depending on your application, you have to make the choice of how much length you want for that and so on. So let's keep it simple for now. Last name. Vacha 45 email Vacha 45 phone. Vacha 45. Then comments can be actually comments can be text because that could be like a big text. And the text takes a maximum length of 65,535. As you can see, yes, so let's select this. Obviously, we probably won't have that many comments, but it doesn't matter too much at the moment. And the last thing that I want to do is status. Now, status you can do in so many ways, depending on your application, you could keep it as integer and you could have, for example, zero to be an active user, one to be deleted, three to be not to be banned and so on. But to make it very like understandable, I don't know if this is correct to say, I'm going to use it, I'm going to use Vacha and we just keep it with words. So I want to make sure that the status of the user originally, when we insert a record is actually set to active. To do this, we can change the default value here to as defined. And I can just put active like so. And I think that should be good enough. As I said, depending on application, you can choose. Now, all of this could have been done with an SQL. So if we preview the SQL, this will give you the code of creating the table. And what I'm going to do is I'm actually going to copy this for you. And I'm going to save it in the folder here. Just so later on, I can publish it on my article. And if you wish to save a little bit of time, you can just insert it. I will show you how in a second. So let's say what is this user schema? Is this how you type schema SQL? And let's paste it in here. Like so. Obviously, this doesn't need to be in our project, but I'm just going to keep it here for now. And what you could have done is you could copy this. And instead of pressing save here, I mean, I will press save in order to save all this, you could go under SQL and insert the command manually. So let me save this first of all, please enter valid length, valid length for comments or valid length for this. Oh, OK, let's say 10. And I'm going to have to get this again. Close and save. All right, let me update this super quickly here, save it and remove it. All right, as you can see, we now have all user table. Everything is in here. And as I said, if you wanted to insert the code, you can go to SQL, paste it in here and just go. And this will create exactly the same structure as I have inside here. We have the key, which is good. This is our primary key and we're good to go. One thing that we could do is create a record just for now. But later on, I have prepared a little SQL file that you can insert as well. And use instead of creating records manually, but also will create a form as well. So that would be easy. So one thing, let's let's do one thing and create a record. As the first one ID, we don't have to specify. This will be unique and it will be automatically generated. So first name, I'm going to have ready. Last name, I'm going to have the brand email. I'm going to have a little ready phone. Let's just use this one here. Comments, hey, hey, this is a comment. And status is active and we can press go. OK, if we go back to browse, you can see that we have one record, which means that we can start using our database and we can pull this record. Let's go back to our page and let's go back to the home HBS. Now, obviously, this needs to be populated from the database, which we'll do in a second. But first of all, let's go to app and see how we can connect to it. So if we click on app and if you remember the top, we included my SQL. So let me show you how we can actually use it. Maybe here under the template engine, let's make some space so you can see a little bit better. And first of all, we need to create a connection pool. A connection pool is a cache of database connections maintained so that the connection can be reused when future requests to the database are required. I'm going to create a new connection pool. So to do this, let's comment connection pool. And to do this, we can do const pool equals my SQL and this will be dot create pool. This is how we create a pool. And inside here is where we can specify the connection limit, the host that we want to use, the user of the user, the password and the database. Now, if you remember earlier, we included we require this dot EMV file. The reason I'm using dot EMV file is because we can have like a central place for credentials for database credentials. So let's create dot let's create this file and enter all database details before we start, before we create the pool. So to do this, let's create a new file dot EMV and inside the dot EMV file, we're going to have DB, we're going to have DB underscore host. And the host name for me is good local host because I'm using XAMP here locally. The database name will be equals. I actually forgot what it was called. So let's go back to the database and copy this. This is our database name here. Then let's go and create the database user. And for me, this was you root as default and database password. So let's say just database pass and this would be password for me. All right, I'm done with the dot EMV file. And now I can get those variables and pass them inside here so we can reuse them and we can reuse this on many pages and we have one central place where all details are saved. Let's start by doing the connection limit. First of all, we can specify this by doing connection limit. And you can read a little bit more about this in their documentation. It's all really well explained. And this would be something like 100 for now. And then let's specify the host. Let's tidy it up so it looks a little bit neater. And to grab the environment variables, we can actually do process dot EMV. Dot EMV dot DB host is the host. Then we can do comma, excuse me, comma. I hate this pop up here because you can't see very well. So then we have user and for the user, we can do exactly the same as here. So we can do process dot EMV dot database user. Maybe we can do comma, copy this line and change this one to password. And this will be DB underscore pass. And this was a bad idea. So now I need to tidy it up. And the last one that we need to set is the database name. So database like so. And OK, I miss better. So database and list the process. EMV and then I think it's called the B name. We don't need comma here as it's the last value. Let's take this and now we need to connect to and now we need to connect to a database to do this. Let's do to connect to DB something like this. And let's do pull dot get connection. And inside here, we can get two values. We can get an error or we can get the connection. And this will be another function like so. So if we close this as well, if we get an error, we can check by doing if error, we can throw the error like so. Throw error, OK. And this would mean that something is wrong and we did not connect. So not not connected. And then if we connect, we can console log something like connected and we can get the connection thread ID if you wish to. So to do this, we can do console log and let's just console log something like connected as ID and then space. And then we can add the ID here by using connection dot thread ID and close. Technically speaking, if we have entered the database credentials correctly or date and or database is running, if we save and refresh or browser, actually, if we only save and we go to partial or terminal, you should see this again, we are listening on port 5000. And you should see this connected as ID 247. And this is because I've probably used it quite a lot. And this is all good. We didn't get an error, which is good. But if I do make a mistake, let's say in my dot in the file, I mistakenly put the wrong database name, maybe to one or 12. Save this and go back. Refresh, actually, here. This isn't refreshing. Maybe it's not refreshing. Why are you not refreshing? Maybe I need to save something else. Let's save here. Otherwise, I'm refreshing. Let's restart it. OK, as you can see, now we're getting a error. And this is saying my skill message, unknown database user management to 12. And so if you get this, maybe your credentials wrong. So let me go back and fix this by changing it to back. Save this and let me just do have to restart. Come on, man. There, Jeff. OK, it's all good. We're connected. Technically speaking, we could do everything inside here. This could we can create multiple routes and do everything inside here. But as the code grows, this could get really messy and it's best to be organized. And this is why we're going to create another folder now called server and inside this server, we're going to separate the routes from the logic. So we will have routes and controllers. Let's start by creating the folder. First of all, and we'll come back to this in a second. Let's create a new folder. Not in views. It needs to be outside view. So let's create a new folder called server. And inside the server, we're going to have controllers and controllers. As I said earlier, this will be basically where our logic goes. And let's create another folder called routes. And the routes are basically kind of like the pages that we'll be creating. Inside the routes, let's create a new file called users. Sorry, inside routes, let's create a new file called user. And this will be a JS file. And this is because we only today we only work in with a single table name user. So I'm just going to be having a few user routes. But of course, as your code gets more complicated, you could add more. Or you could just have one route with multiple controllers, which I can also show you how to do as well. So inside controllers, let's create a user controller. So let's do user capital C on a controller.js and save. This should be good. OK, let's now make our app work with the routes and a simple controller. To do this, obviously, we need to move this now. And we let's let's actually cut this. I might end up using it in a second. And we need to first of all tell our app where all routes are. So all routes are obviously inside server and then routes. To do this, we can create a const that will hold the folder path. So we can do const routes. And this will be required. And inside here, we can just specify dot server slash routes slash user. This is going to be the path. And now we can tell express to use this route. So app dot use. We can just put slash here to use the first route to the home page. And then we can just pass the the path in here, which is on the route. Like so. And that should do the job. And now, technically speaking, if I go to uses dot JS and let me close everything else so we don't get confused, technically speaking, if I do this, you might think that this will work, but almost we actually need to require express here and we need to get the express route as well. So to do this, let's first of all do const express equals require and then express. And now we need to create the router. So const router equals express the router like so. And now we can use this router instead of app. So let's replace app to router and save. This should work exactly the same way as it did before. So technically speaking, if we go back and refresh, we're getting an error. And this is most likely because we need to explore this router. And this is because we actually need to explore explore this router. So we can do module dot exports. And that would be equals router like so. And if we save this now and go back, you will see that this is working or should be working. And yeah, everything is working as it used to before. So we're good in here. But also we want to separate or route the logic into the controllers. Now to do this, let's include our first controller and only one, which will be user controller. So let's do const and maybe we can give it a name of user controller equals and then we need to require the path to this user controller. So that would be dot once going back, is that one going back? I think so. OK, two dots, controllers. And then we need to use and then we need to select user controller. We don't need to specify JS and we can just close this. And now in order to use this user controller, we can actually do router dot get. And because we want to hit the home page, we can just this dash then comma and then we can specify the router that we want to use. So user controller and then we need to specify specify which which function that we want to use. For example, we might have a couple of functions, for example, we could have a create one. We can have find, we can have updates. We can have delete and so on. So we need to create that as well. And for the first one, let's just call this one a view, because when I hit the home page, I just want to go to the database and query all the user names. So let's just kill it view for now. And we can actually grab this now. Safe and this is going to be our first round here using or or user controller. And now inside user controllers, we we're going to actually have to create this view to do this. We can simply do exports. So let's do view, view users. And let's do exports view equals. And then this will be exactly the same as we did earlier. So request and response. So let's do rack and rest. And this will be I think I'm doing it wrong. Am I doing wrong? Now, this is correct. Yeah, that's correct. And this will be like so, I believe. OK, yeah, this is looking good. And now if we wanted to render the page, we can simply just do exactly the same thing as as we done before. Rest dot render and we render the page that we want. So all of our logic is going to be here now. And to test whether this is working, let's study it up a little bit. Safe, go back, refresh. And as you can see, everything is working. Now, all routes are separate to our controllers, which will make our code much cleaner. And as I mentioned earlier, I will show you how to create multiple controllers and to create another controller is exactly the same way. You just create a different file. So maybe you can create a client controller. Then you include require your control here. So it will be like client controller. You change this to client controller. And then you can obviously copy this in here and have different functions for client controller, or you can even spare or you can even do this for every single user controller as well. You can do different ones, but all of is not that big. So I think one controller will be good enough and we'll just separate it with different functions. All right, we were close to doing the database stuff. And now that we have our first router and it's all working, we know that our logic needs to go under the user controller. Now, here is the thing. We actually need to include the MyScale here as well. So let's do that const MyScale equals require and then we require MyScale. And if you remember in app.js, where we created this pool and we created the connection, we can actually copy this now and reuse it. So this is the great thing about doing the about doing the process.emv is that we have the database credentials in one place, so that's very easy to maintain. Anyways, this all this logic is going to have to go inside or view. Sorry, we use a view. So obviously, we're going to have to remove this in a second. But let's start from the top. Actually, we need to put this outside so we can make a connection in all of all of all of our exports here. So let's do that. Sorry. OK, we get the connection pool here. It's all good. And then inside here is where we can do the queries. So let's remove this comment just to make it a little bit cleaner. And of course, we don't want to render there. I'm going to cut this and we get in a connection to the database. If something happens, we get an error. If we connect, we get connected as ID and it displays the thread ID. And now this is this is where we can start doing some queries. Let's use the connection to use the connection and create a simple query. We can do connection dot query. And inside here is where we do the MySQL queries. If you're familiar with MySQL, this should be very familiar to you. It's exactly the same as you would do in. Well, it's MySQL, isn't it? It's exactly the same way as you would do it in PHP. So we can do something like let's do a simple one for now. We can do select. Everything star means everything from user. And actually, that's it. That's really that's the most simple query we can do. We're selecting everything from the table user. Now, we can get here an error or we can get the data back. And I'm going to use gross to get the data back. And this will be a arrow function like so. And OK, close. And inside here is where we can do some of our logic. This is also spread out. But OK, this is where we can do some of a logic. For example, let's say first, actually, first of all, when the connection is done, we need to release it. So let's do a simple comment here. When done to do this, we can do connection dot release. But so close it. And now let's just add a very simple if statement. So if we get some data, we can then render the page. But if we don't, maybe we just display a console, maybe we just console log the error to do this. We can check if not error with exclamation point. If we don't have an error, then we can do rest dot render. And we can render the home page, which is inside views home, as we had before. But else, if we have an error, maybe we just console log it. Log, I guess you could render like a page saying, I don't know, like there has been a MySQL error. You know, that's totally up to you. So let's just do error if we want to save this. And also one thing that I forgot, which is probably the most important thing is that we need to pass the data that we're getting from the database using rows. So to do this, we can inside here, inside the render, we can pass objects by doing comma and with the curly brackets, we can just pass the object, the object of rows. And also if we if you wish, we can display this inside the console by doing console log log. And then we can do a simple message here, like the data from user, the table, then we can do slash and rows. OK, this should display the data in the console as well. OK, this is good. If we don't have any errors, let's say if go back to the page refresh. No errors so far. If you go back to the PowerShell, you will see that we're actually getting the first record from the database, which is awesome. And now let me show you how we can display this using handlebars. If we go back to our HTML home handlebars, we want to basically do a loop inside here and replace the data. So table row. OK, with EJS, it's actually fairly simple to do this. We can do two squiggly brackets like so and then hashtag, hashtag each. And then we can grab the object, which is called rows from here. So let's grab rows and then we need to obviously end this somewhere so we can do open curly brackets, close curly brackets and to end it, we can just do slash each like so. If you want to replace the data, we can now do open with squiggly brackets, close with squiggly brackets. And we can now use this dot and then the the table ID that we want to select. So for example, so for example, I can do the ID, which is if you remember, if you go back to the database, it's this here ID and then we have first name, last name, email, phone, comment and status. So let's do that. I can simply copy this and replace, replace everything. And that's it. So let's do this first name first underscore name, last underscore name. What was this email? And then we have phone number, phone number. OK, say this. Hopefully if we refresh the page now, something has happened. Do we get an error? OK, I don't know what happened. If you refresh the page now, you will see that we're getting the data from the database. So if I was to update this record somehow and I don't know, let's say I removed ready one, two, three and save this record. And if I refresh, you'll see that this is actually coming from the database. And it's actually console load in here as well, just for testing purposes. So that is pretty awesome. So far we've managed to get some data. But of course, it will be nice to have a little bit more data. Is this zoomed in? Yeah, this is zoomed in so much. But of course, it will be nice to have a little bit more data. And I've actually prepared another SQL file that you can insert into your SQL here. So I'm going to post this if you wish to use it on the blog post as well. But let me find the first of all and I will paste in here. So I found the records. I basically added some random users, random names and phone numbers. It doesn't really matter. It's just for testing purposes. Let me close this and let's go back to the page. And I can insert some of the users in here. As long as the data matches, of course, I've got ID, first name, last name, email, phone, comment and status. So if I press go and we go back to the users, as you can see, we have quite a few users and this is looking good. So if I go back to my page and refresh, you will see that we are getting all the users listed, which is pretty cool. My PC is being a little bit slow, so I'm going to reset this. Let me restart it. All right, this is all looking good. Of course, by doing different master queries, you can order your database, order your records the way you want. But I'm going to keep it simple. And one more thing that I wanted to show you here is that let's make this query slightly more complicated. So, for example, if we remove this user, basically, I've been I've been bad and we need to ban this user, maybe we can just do I don't know. Let's do removes and press go. Obviously, at the moment or query just displays everything we can make or query very specific just to display first name, last name, email and phone number. But at the moment, I'm just using all this is not really a mass scale tutorial. So I'm not going to go in much detail. But what I wanted to show you is that what you could do, for example, is select all the users with status of active. So let's say we have select all users, sorry, select everything from users. And then we can do where? Status is equals, maybe active. I don't know, you can make it more complicated. You can make it better for yourself. All right, if we say this technically speaking, I shouldn't be popping up anymore. So I'm here now. But if you refresh, I don't know why my PC is being so slow. The data from the iPhone. Oh, and I have a spelling mistake. So status, so if you go back. OK, if we go back and refresh, you will see that I'm no longer appearing. But my record is still in the database here. And this is how you could potentially keep the user data in your database just in case something happens. Like maybe you delete one by mistake and so on. You can also just delete them if you wish. And also you can have if you want to make your database even nicer, you could have date created and date modified inserted into here, which would be much better, I believe. But yeah, just a few ideas. Now that we have this, we might as well just wire up the search because this would be very similar to what we just done. So let's do that. I'm going to tidy this up a little bit. So for the search, let's create another export just like this copy this and let's paste in here. And let's do I don't find I think find is OK. And close, of course, we need to wire this up. And let's comment is all find user by search something like this. And we need to wire this up inside the routers. So user and we can simply just copy this here. So router, this time we need to post because we'll be posting from the search box. So let's change this to post again. We're posting on the home page, but we need to change this to find. So uses the new block of code that we just created. Save this and we should be good to go here. If we go back to user controller, this is where we're going to be adding the query. But before we do this, we actually need to modify the form. Now, the form was under views left main and inside the header here. So we need to so we need to make sure that our form here is set to method post and the action is set to the front page. So to do this, we can do method equals post like this and then we can do action, not accept, not accept. Action equals slash and this will be our home page. And from now, let's not validate anything. But of course, you can validate this if you wish. So no, no validate would ignore any validation just in case. But yeah, of course, if you insert in anything into a database, you should validate your data. And I've actually done a full on tutorial on this, which you can watch our link in the description below and the article, how to validate your data on the back end. And of course, it's nice to validate it on the front end as well. Bushcraft makes that super easy to do. Just have a look at the forms we've wired or form. But one more thing, we need to find what the input name is. So the moment we don't have a name, let's add a name here. Let's call him John. Now I'm jacking it's going to be code search. OK, this is what we're going to have to grab. Let's save this. Let's remove it, remove this page as well. And let's focus on the find. Now, technically speaking, we should be able to reuse most of the stuff from here. And we might as well. So let's copy this and paste everything here. And let's just modify slightly. Now, let's do it like this so you can see a little bit of what's happening. Of course, we're going to have to change the query to graph the value from the search box, which we named search. Very good name. We need to grab. We need to grab the value and to grab the value we can use the rec dot body. Let's do let and maybe we can call it search term. And this will be because rec dot request dot body and search search is the name of the actual input, actual input that we just changed. And technically speaking, I can just console of this right now. And if I input something that should give me the text that I've entered. We know that this is going to work. So let's move on as well that we might as well just do the query now. And to do the query, you can go a little bit more complicated than I would in here. But I'm just going to show you a super quick example here. So let me start from beginning. So we need to select everything from user and we need to say where, for example, let's let's use first name, for example, first underscore name. It's like question mark. And the reason I'm doing question mark is because I'm trying to prevent MySQL injection and you can read and you can read MySQL injection in more detail online and on the MySQL MPM package. They explain everything about it, but that's why I'm using the question mark. And now I have to pass the value outside this. So to do this, I can actually use something like percentage so we don't get any bad data plus search term, which we have here. So this is going to pass the value inside here. And we can do plus and another percentage, percentage, percentage. Where's percentage? OK, and so far, if we search something, it's only going to search it by first name because this is what we've done. Let's test it out first of all before we make it any more complicated. I'll refresh everything is working. And let's say we want to find Henry. So if I put H E H E and search data from user table. Query empty. OK, let's have a look what I've done. I don't know what I've done here, but let's have a look. Request, that's fine. Search term body. Connection query, select everything from. User where question mark. Oh, OK, I think I've missed the comma here. That's why I usually make silly spelling mistakes, but I think I've missed the comma here and that's why it's not working. So let's go back refresh. OK, this is working. Let's try to search for Henry one more time. So H E and then search. And as you can see, we're getting Henry. But if we look for another thing, another detail in this database, let's say Ferguson, maybe if we search for Ferguson right now. Bam, nothing appears. And this is because we are only using the first name. This is to do with the query, the query that we've created. Obviously, you can make this more complicated as you wish. But let me show you how we can add maybe the last name, for example. So we have first name like question mark and we can do all last underscore name, like question mark. And then we can pass exactly the same value inside here. Maybe we just copy it. Yeah, let's just copy it and paste it. Save this. This is going a very long way. And here I hope that you can see what a break if I go on the line. Yeah, probably. It's fine. I need to do the JavaScript way of doing another line. But that's fine. Let's save it and go back. Refresh and let's search for. So we have made this year. So let's search for this now. And as you can see, this comes up if we search for Nate, Nate comes up, but if we search for UFC, UFC doesn't come up. So this is because we're only using that to those two rows. And of course, you can make it the way you like. So if we do run the Rosie again, let's do Rosie or let's just do everything with R. So as you can see, this display is absolutely everything, even my name, even though that I'm not active. And this is why you can make your career a little bit more complicated. But if I do Ronda, we just get Ronda and this is where you can do the where status is active. You can put this into the query and that would also work and just show you the active users. All right, I think this is pretty good. We've wired up two things. I don't know, let's tidy up a little bit and continue. OK, I just checked and I think my computer is very a little bit slow because my C drive was almost full. So I just deleted a few files and we can go ahead and do the next bit. Shall we? I think that we should do the form next. So the form we will basically we need to create a button in here and we click on the button. We create a new page and then we can create new rackets. Let's do that. So again, on the home page, let's go to home and at the top of the table. I want to add I want to add a button and to make it a little bit nicer. I guess we can let's do another row. So we can do dot row. This is a class name and then we can do two columns. So maybe the first one can be actually both of them can be six. So let's do call that six and press enter. So this is the first column and this is the second column. So maybe in the first one, we can just say something like H1 uses. And in the second one is where we're going to be adding a button for the button. I'm going to have a link. So H ref equals and I don't know how to name this page. Should we just name it at user or add dash user? I don't know. Add user that would differ now. And then that's fine. Then we can set a button. And the class name would be BTN and then BTN primary, which is blue. And then we can self align self center. All right. And let's close this and maybe we can just do add new user or just add user will do. I think this would be OK. Let's see how this looks first of all. And let's refresh. Oh, and this is because I need to justify content to the end. So we can do it in here. I think this would work. And the reason I didn't align self center is because otherwise the button wraps the full width and it becomes a little bit ugly. Did I save or did I riff? OK. I don't know. I must have missed powder. So just if I content and come on. Nope, maybe. Oh, it's because I need to put. I mean, I need to do the flex. It's not a flex box. Here we go. Here we go. We are now so we have the user now, which is cool. And let's start making this page. So if I click on this, this is going to lead us to add user page, which at the moment doesn't work. Let's make it work. So first of all, we need to create this page. So just like here, just like the home, let's create a new page. And I'm going to call this one I don't know, new user or add user, add user, probably add. User dot each HBS and save. So this is where we're going to be adding the users. So so let's just do an H2 for now, which will be which will display something. User just for now, just to test it, let's just add something. Of course, we're not rendering this. So we're going to have to go to routes in inside here, we're going to have to create a new one. And this one and this one is going to be add user. And for add user, maybe we can just call it form, maybe. Yeah, cool. Whatever you like, I'm going to call my form. And then inside the controller, we need to create this. So let's go inside the controller, copy this copy. You can come to as well. We'll need your comments and then I don't know what to call this. Add new user, user and this will be form. And now we can render something, but let's render. Instead of the home, we can now render at dash user, at dash user. And of course, we're not going to pass any data just yet. I save this and I just want to see whether this is working. So if we refresh now, this isn't working. And let's have a look at why. So we have form here, restaurant, did I save it? Maybe maybe didn't save it. So controller, save user, save and refresh. Add user, no working. Add user, control form, form, add user. Oh, I think this is not working because I just copied. Yeah, OK, I just copied this one here, which is post. So we need to just put this as get, I assume. OK, let's save and refresh. OK, OK, not bad. So we have the new user, but to make this a little bit better. I'm thinking of using just background. So let's just copy. Let's go to bootstrap and do bread crumbs. I'm going to copy this one here is quite nice. This one is not yet. So let's copy this, go back to add user. And instead of new user, I'm going to do home will be a cool slash so we can go back and have a better user experience and then we can just do new user with capital U. Save this. Go back and this looks a lot better. OK, let's start creating the form now. And we can actually put the form in a partial in a second. But let's let's start by creating the form first. And then I will show you how to insert the form inside a partial. So if we go and search for forms, let's find one that we want. They're really nice. All right, you cannot use all sorts of forms, but let's maybe let's use this floating labels. I'm not sure if I like it. They're quite big, but let's use that anyway. We have email and password, but we can build on it. Yeah, let's use that. See how it looks. It might be OK. But yeah, the idea of what might look better, actually, I don't know. It doesn't it really doesn't matter anyway. So let's start creating a form. I'm thinking I'm overthinking it now. So to create a form, we need to start adding or input but also don't forget that this is a form. So we're going to have to wrap this in a form. So let's do form. And this form will be something like let's do class of row and then G3 needs. You can have validation as well needs validation, if you wish. And then the method, which is very important here, needs to be set to post. And we need the action in this point to be slash add user, just like the user, the euro that we created. And we could have kept the euro the same as this. But at least you will see, but at least you will see how things are working now that the name is different. So add user and let's just leave it as no validate. All right, this will work and let's wrap the form. And let's just start building the form now and like so. All right, the first input that we have, the first input that we have here is type of email, but I want to change this to type of text because this would be the I could have copied this, actually. It doesn't matter. Let's just have one. This will be a type of text because this will be the first name and the placeholder could be first name. Name, floating input, that's cool. Very important thing is that we need to give names to those inputs. So my order now, some name to this one will be first underscore name so we can grab the data from it. Again, this needs to be changed to first name now. Name and let's create one more. And then actually instead of having a free, let's have it as six. No, no, no, no, no, this is this is fine. We need to wrap this into another row so we can have two inputs on one line. OK, so we can do class of co dash and the six or whatever or just call six. You know what call six will do and have one input here. Let's grab one more. Here, this one will be the last name. So last, last, last name and we're good to go. Let's save this and see how this looks. As you can see, we have first name and last name. They animate quite nicely. I have some testing inputs here, which I haven't cleared, but it's so good. And let's continue by building the rest. Now that we know how to build this, we need the mobile phone number. So let's copy those two, I think, on another line. This would be phone on the phone. The name would be phone. As I said, I'm not going to do any validation here, but you can spend a little bit of time to do that. It would be nice. And let's do what is the next thing? Email. No, we have email first and then we have phone. Let me swap them to and that could be type of email. And this could be email at email.com. Oh, no, this is the name. So this could this is going to be email. Sorry. And the placeholder can be email at email.com, whatever. And email. Then we have the phone number in here, which is cool setup. And the last one that we have is the comments. So let's just grab this and paste it in here. And instead of column six, we're going to do 12 and have this as text. Then this would be comments. Then placeholder will be comments. Then name will be comments. Safe. I think this is a good. And the last thing that I almost forgot is we need a button in order to submit the form to this. Let's do another one so we can do co dash 12 and then D. That didn't create it for me. All right, let's do div class of co 12 with the grid. And I want to make sure that the button is for it. That's why I'm doing this. And we can do button with the class name of BTN, BTN primary. The type will be submit this time. This is important that the type is submit, so it submits the form. And in here, we can just say submit form or add new user, add submit. It will be fine. Let's have a look. All right, this is looking good, except the button is not looking very good. I'm not sure why it's not for it is because I've misspelled it, of course. So here we go. The button is now for it as well. And this is looking nice. The comments can be adjusted as well a little bit. And we can basically just say style height, 200 pixels, something like this. And this is making it so much nicer. So we can now go back and we can add a new user. And of course, we need to wire this up and insert the data to the database. Let's have a look at how we can do that next to wire up the form. It won't be much different than the search. So every single input has a name. And this allows us to grab the data and then do a query to insert the data. If you remember, we created another in user controller.js. We created this add new user as form. Maybe we should have been more descriptive, but we only have one form. So it's not too bad, I guess. Now, to create a new user, we could potentially grab all of this and just modify a little bit. So let's yeah, let's grab all of this and we'll modify as we go. So this is the page that we want to render as well. Maybe I'll just come with it so I know. All right. So first of all, let's now that we know that this is the page we are rendering, we might as well just change instead of home to be add user. And so inside here, we get the connection. And the important bit here is the query. So instead of selecting data, this time we're going to have to insert some data. So let's have a look at how we can do that. Oh, I broke a big time. All right. Anyways, so inside single quotes, I can start writing the query here. So we need to insert the data. But before that, we can actually we need to grab the data from the body and we can destructure the data by doing something like this. So let's destructure the data by doing const. And then we can grab the first name and then we can grab the last name. Then we can grab the email. Then we can grab the phone. Then we can grab the comments. And we know and we don't need an ID because we're not submitting an ID here. The ID is automatically added from the database. And all this will be called request body. So every time we get a request, we submit the form, we're going to get all of that data from the body. And we can now actually use those variable names to insert the data. So let's do the query quickly. So to do this, we can do insert into user set. And then we can start by first name underscore name equals question mark. If you remember earlier, I said about my score injections. This is how we can prevent it by name and question mark. And what I suggest to you right now right here is to keep this simple because you can easily get lost as your query gets super long. So what I'm going to do is just test it with maybe the first name and the last name. So let's say I'm I just started the first name. And what I would do is let's add the value of the first name here. So we're going to pick up this first name and with a comma, we can add more values. So first name and let's add the last name as well. So we can separate this with comma last underscore name. And I think it's equals question mark. Don't forget I almost did. And then space this and the last underscore name to grab the data. This should be good enough to test. Now, now we don't actually need this in here because we're not going to display any data, but we could create a custom message. And I will show you how to do that after. So let's see if this works. If we go back to the form, let's refresh just in case. See whether we get any errors. Column first name cannot be no. Oh, and this is again. This is because we're getting this error now again, because I keep making exactly the same mistake. And this is because in users we have a get, but we don't have a post. All right. OK, sorry about this. So we have we can get the form and just render it. But we need to post and we need to post and we need to post something else. Maybe create sorry about this. So what I'm going to do is this is all good. We can actually put this as create. And we can just create another one just to render the form. And this will be form. And then where is the rest? And OK, I think this will work. Oh, this makes sense. I keep making this mistake. All right. Now we should be good to go. Now we're rendering the form. Let me show you now we're rendering the form here. But if you want to create something, we need to post and then use this code here. OK, it's all good now. Refresh and let's have a look at whether we can insert a name. So let's say right and the name of right is going to be one, two, three. Last name is going to be one, two, three. And let's admit. OK, this submitted the form. If we go back to home, hopefully at the bottom, you should get right. And one, two, three. And this is pretty cool. So our form is working. And now we can build up on it and add the rest of the values. So we need to add email, phone, comments and so on. Let's do that. OK, so we have first name, last name. Then we have email equals question mark. Then we add email here. Then we add what is next phone equals question mark. Then we add phone. Then I don't know what's what is an easy way to put this in two lines. I think it's because of zoom so much as well. But let's leave it. So phone and then comments. But I did not have to come in here. So comments and equals question mark. I think maybe I need to just use the graph now and this will do the job. All right, I have pretty much added everything. So let's save this, go back and see whether we can insert a full user with all the details. So this one will be Bob and we can have Mali. Then we can have Bob at Mali if I spelled it correctly.com. And if you want to call Bob, we have this number. And then we can just put some comments here. That's weird that my it's weird that this happened back and fix a little bit later. It might be the height thing. I'll have a look in a second. Anyways, we can add a comment here. Bob modern day was a great great spelling, great spelling. OK. And submit. If we go back, you can see that we have Bob Mali at Bob Mali. His phone number is here. And if you go to the database. Which has time. Let's do password and if we look at more records, you will see that all of them are now inside here. So all form is now working. But the user experience on this form is not great because we submitted something and it just didn't give us a message. So I can just submit something right now. Obviously, you don't have the validation to save us time. But as you can see, it just refreshes and that's it. So what I'm thinking of doing is I'm thinking I'm thinking of adding a little dismissible message here from Bootstrap. So let's do dismissible alerts. And for this one, you will need to include the JavaScript, which we did earlier when we were setting up the HTML. And yeah, this is the one I want. Actually, this is the one with the dismissible X button. So I can click on it and dismisses it. So if I grab this and paste this under my ad user. Or is it here? Can I do? Yeah. OK. So maybe under the breadcrumbs, I need to tidy this form as well and fix the and fix the text area in a second. But don't worry. So inside here under the breadcrumbs, we can have the Holy Grail Molly message. And this one would be success. Or actually, you could have a warning one as well. But let's just do a success. And once we do the success, you probably know how to do the warning one. So we have alert warning and we can just change this to success. Alert dismissible fate show. That's fine. That's fine. That's all fine. And maybe we can put a custom message in here. So let me show you how we can do this back to user controller, back to creating a user. Inside here, you know how we passed the data earlier with an object. We can do it the same way. So what we can do is inside here, we can maybe do an alert. And this alert, we can just say user added successfully. Is this how we space successfully? This is now is for successfully user added successfully like so. And now, now that we have this, we can actually use it for all dismissible. If we go back to art dash user, we can use EJS to this. So we can literally just say if hashtag if alert. So if there is something in alert, then we want to display the message of alert and we can do it like this alert. And then we need to end the statement here like so. Technically speaking, this shouldn't show until we go to here, we've submitted the form and this alert now has the value of user added successfully. Let's have a look. We go back, refresh. Actually, don't refresh. She's going to post refresh. Let's add, let's add Emmett and then we can just submit. As you can see, we have user added successfully. And this is pretty nice. So we can dismiss it and add more and so on. And you can do the same thing for an error. If it errored out, you can just maybe do an error alert and so on. But I don't want to like overly complicated. And let's and I think that we're pretty much done with this. I just need to fix the comments box, which is like this because. Oh, OK, this is because this is a text area, isn't it? Sorry, it needs to be a text area. OK, this is what happens when you copy and paste. Yeah, yeah, yeah. So this is text area instead. I'm just going to save you some time and copy and paste one. All right. So instead of instead of input, just changes the text area and this should do the job. So let's have a look. Add user and yeah, this is working out perfect. All right, we know how to add data. We know how to search the next best step to do would be to edit the user. And this is because we're already working with the form. So we might as well finish everything to do with the forms. And let's make sure that this edit is working. And yeah, so we can just update the details. All right, first of all, let's create the edit user page. Let's go back and we can create a new page in here. So edit dash user dot HBS. And then in all routes, where is routes user in a route? We can do in a route we can do a get. So let's me copy this. And we can do the page as maybe edit user. And also we'll have to pass the ID of the user. So I'm going to do this now and explain it a little bit later. And also we need to have a custom thing for editing the user. So let's do edit instead and we should be good to go. Let's now create this edit function in user controller. So instead of create, we say edit. So now it will be edit user like so. And we can render the page that we want. So rest or render. And the page that I want to render would be edit user. Edit user. OK, I think this should work. Obviously, we need to put a little bit. Let's just do edit for now. And if this works, oh, I don't have a link to it now. But we have an error. OK, we have an error because I need to put this as edit. And we don't need to restart. We're good. We don't actually have we actually haven't linked the edit buttons to the edit page, but if I do edit is edit user. Let's look. Yeah, edit user and then some sort of ID. We need to render a edit user one. OK, this is working. So if you put edit user one, we get in the edit, which is good. Now to edit the user, it might be best to reuse the form that we created in here in adding user. And what I'm going to do is actually I'm going to do a partial, which means that we can reuse this form in multiple pages. And so if we ever need to update the form, we can update it once. So I'm going to show you how we can do a partial. So partials we can do inside the views, create a new folder called partials. And let's create a partial code user form dot HBS, of course. And now we can grab the form from where is it at user. So let's grab the whole form without without the start and the finish. Because different forms might have different actions. And so let's grab this quickly and I'll come back to this in a second. Put the form inside here and we now need to include the partial form inside here to do this with EJS. It's actually fairly easy so we can do curly brackets, curly brackets. And then the biggest sign and then user dash form, which is the name of our partial. So hopefully if we go back, excuse me, if you go back and add user, the form is still working. If we submit, it will be exactly the same. But now we can actually reuse it for the edit as well. So what I can do is grab this, go to the edit page and add this in here. OK, we have the form here. So if I go to random ID, so edit user number three, you will see that the form is here and let's just add the backgrounds as well. So we know where we are. I'm going to copy them from the add user to the edit user. So home and then this would be edit. So. All right, this looks a little bit better. This is exactly the same form now. Obviously, we'll need to change the actions, the action in a little bit. But we'll come back to this in a second. Now let's go back and let me explain something. First of all, we need to edit this button to go to the page, which I'm going to do in a second. But we also need to link the ID. Every single record has a unique idea at the moment. So Amanda is two, Alexander is three, Kabib is four. So let's go ahead and link this. So this was our homepage and here is the link to the edit so we can do slash edit. Yeah, edit user, we can use the ID from here so we can grab the ID from the database like so and just pass it in here. So if we save this and refresh, you will see that if I hover over every single here at the bottom left, which I can't show you, it's very small. But you can see that each link has an ID and technically speaking, if I click on Amanda now, this will go to edit user two. And what I have to do now is do a query for the user to and grab the details so we can update them. Let's do that next. All right, let's go back to user controller. And on edit, we can actually maybe reuse the first query here where was where we have a select from user where our status is active. And we can just modify this a little bit. In fact, it will be very, very similar. So let's copy this and go back to the edit. I'm going to keep this here and just replace it. Actually, let's just replace this. To edit. And now what's happening here is we want to get the ID from the URL here. So we know that when we click on the user, this gives us a unique ID. And we want to grab ID, the ID to do the query. To do this, we can actually to do this. It's actually fairly simple. We can just do it inside the query, actually, I believe. We could just do it. You might have to do some checks on this, but we could quickly do it by doing select everything from users where ID. Come on, you won't let me select ID is equals and then this will be a question mark. And then we can pass the ID by doing rack dot params, parameters and then the parameter that I want to add is the ID. All right, technically speaking, this is good. And now this is going to go ahead, fetch the record with this particular ID. We need a comma. Then the records will be saved in this variable rows and this object will be passed to the page which we can insert into each field. So hopefully if everything is working now, so far, this is all good. If you go back to the form, which is inside partials now, we can start populating the values, which not exist in the moment with the ones from the database. So let's say the first name value is value equals and then we can do curly brackets and then this dot, excuse me, this dot first name. And now let's just copy this multiple times and do it for the rest. Let's do it here so you can see. So last name, then we have do it doesn't need to be in the play store. No, probably value we find email. Then we have a phone. Then we have the comments. Actually, in the text area is going to be different. We can't. Oh, yeah, I've already added it here earlier. OK, this is how you are there in the text area. You have tied it in the text area, of course, not as a value. And this will do the job. Now, if we leave it like this, we're probably not going to get anything. And this is because we actually need to wrap or edit user form with EJS. So we need to say for each inside here age rows. And then we need to close it in here. Oh, excuse me, each and hopefully this should now grab the data debate and hopefully this should now work. All right, as you can see, we are now populating the data. Unfortunately, this user doesn't have any comments, but maybe let's find one that does. I think Bob Marley does. So let's edit Bob Marley. And as you can see, we have a little bit of space here for some reason, but that's fine. I think it might be something to do with the actual form here. So maybe we need to remove the space. The text area. OK, let's save this. Refresh. Yeah, OK, that fixed it. So as you can see, all the data is now populated. We can select anybody. The data gets populated, which is pretty cool. And now the next bit would be to update this. Updating this, you can probably guess that it will be fairly easy. We just need to grab this data and update the same record. So let me show you how to do the query. So let's close everything that we don't need. All right, let's push a little bit more and we'll be done soon. Update. Let's get let's get this one, actually, and we'll work from we'll work from this copy, paste. This won't be update user user. OK, so when we do the update user here, we're going to have to grab the data just like we did earlier inside here with the body. So let's do that. I mean, we can even grab the data when we make a connection. I guess I don't know if it makes much difference. So let's do it in here for now. And now that we've got all the data from the fields by using rec.request.body, we can do the update query. So the update query will be very similar to this, actually. So the parameters will go here, but we need to do one by one and just make sure that it works. So let's start from the beginning. The query will be update user set first name. So set first underscore name equals question mark comma. And then last name equals question mark. And now it's best to try this before we do anything else. So let's do first name and then let's do last name. But a very important bit here is that we need to finish our query. And we need to add where ID is equals question mark. And now to get the ID, we can do the same thing that we did earlier. We can actually just do rec.parameters.params.id and hopefully dash WordPress. But now there are a few things that we didn't set up. Now we didn't set up the form first of all. So if we go to edit user, we need to make sure that we change the action to be edit user and then slash. We can pass or OK, we need to pass the ID. But in this case, we're going to have to loop the form outside the form. So I think this should work. And now we can actually grab the ID that we've selected. So this ID. So now when we go on the page, this form will have the ID, but we're having a little problem here. Let's have a look. Oh, OK, we're all good. The only reason that we're getting multi-performances again, I made a mistake. This needs to be deep. We need to also change this to update, of course. I keep making this mistake. And now hopefully if we refresh, everything is good. So we have where ID and our form is now we have the loop now outside the form so we can grab the ID. And this means that the form itself will have if we inspect the element. Let's have a look. Bottom. Where's form? Form, form. All right, the form is here. And as you can see, action is edit user with the ID or five, which is brilliant. So if you press submit now, this should edit this user here. But let's go and select the user. First of all, let's say Alexander Vokhanovsky, let's edit him. And let's just put one, two, three, submit. And nothing happens. Let's have a look. Why this is because we haven't actually put our router yet. So if we go back to the uses and do edit user, we need to do router post, edit user ID, and this will be update. Safe and hopefully now we should be good to go. If you say Alexander one, two, three, update and we are getting. This with error. All right, this worked, but we also getting this error where OK, as you can see, the user is updating, but we have a little bit of a problem here. We're actually updating the user, which is absolutely fine. But we also need to go to the database and and grab the newly updated record. And this is why this is happening. Let's redo this a little bit. So we're good here. It's everything is working. The data is updating, but we just need to do one more query. So we could potentially, we could potentially go and copy this one here, actually, this would be exactly the same query that we need. So when we update it, we want the page to post again and get the record. So technically speaking, inside this if not error statement, we can do get connection and then we can go select from ID, where user ID is equals request user params. And then we just need to render the exact page with the exact same page, which is render edit user. And so I think this should work. Actually, this is a very easy fix to do. So save, let's go back and edit Alexander again. So let's remove the one, two, three, one, two, three, oops, and save. And as you can see, this saved the record. And if I go back, Alexander is now clean. It doesn't have one, two, three, one, two, three. But if I remove his last name, for example, save, it's saved. Obviously, we don't have one of them alert. But if I go back, as you can see, this is saved. So let me edit a page in here and say, all right, let's get one of them nice alerts here again. So I'm going to copy this one here again. In fact, we can copy it from the other page that we created it. So we created it on the user form. Now we created it on our user. This one here, if alert, let's copy this and go to edit user and above it here, we can add it. So if alert, we can say this is success. Yeah, that's good. So if we have an alert, let's go back and say something like we have the rose here and we can pass another object of alert. And this time let's do this with a template literal. So single slanted quotes and then we do something like we can get the first name from here. Let's grab first name. Sorry, this is not how you do it. So dollar sign, squiggly brackets and first name and then has been updated to be a little bit better. So now this alert, it's going to be displayed in here. All right, let's test this and see how it goes. It's getting complicated now. So let's let's update something. So Volkanowski, maybe we we say what a great great fighter and submit. As you can see, Alexander has been updated, which is awesome. So everything is working here as well. Let's delete his number so nobody can contact him. You contact me when you have a problem. All right, say this and go back. The phone number didn't seem to work. And this is because we never finished the query. So let's finish it. But this is all working and this is also this is what I was saying. Just make sure that you test with two values first and then just keep on adding because you can you can easily make a small mistake somewhere and it's just going to annoy you for hours. So we have first name, last name. Then we have email equals question mark. Then we have phone equals question mark. Then we have comments equals question mark and where that's fine. And then we have first name, last name. Then we have email. Then we have phone. Then we have comments. And then we have the ID that we are updating. All right, let's test this should work now. So we can refresh, remove his phone number. Say what a great what a great fighter. Submit this is working. All of the details are staying here the way this shit. So if I go back, you'll see that Alexander Vokonovsky is here. His phone is being removed. I can add another one, update it. Yeah, OK, this is good. We're almost there, people. Two more things to do before I pass out. And these are deleting and viewing. Deleting won't be too hard. Let's do the deleting first. All right, let's close this one here. And for the, yeah, for deleting for deleting records, we don't have to create a new page. We can just delete the record from the homepage here. So let's say so let's just pass the ID that we want to delete in the link. So this would be the delete button. We'll just grab slash and the ID of the user that we want to delete. So technically speaking, if I refresh now, every single delete button will have a unique ID. If we inspect it, if you can't see very well, see five. When we click on this, we need to do something about it. All right, this will be obviously a post function. So what we can do is go back to or routes. And let's create a new route. And this one will be similar to those two, I guess. So we could post with we could post with an ID. And maybe we can do this one to be the lead, the letter. All right, let's save this. Let's go back to use a controller and let's do a quick delete. All right, let's grab something small that we can modify, maybe the edit. And let's go back to the bottom and say update. Sorry, delete. Now, the two ways of deleting the user, you could just change the status to removed, banned or whatever you like. Or you can permanently delete the record. So let me show you first of all how we can delete the record. So everything in here is pretty much the same. And let's just change the query. So this obviously needs to be changed to delete now, which is good, then the render page needs to be home again. So when we delete something, we need to return to home. But this time I this this time, I don't want to do multiple queries. So what I can do is actually when we delete the user, we just want to redirect instead of render. So let's do that. Let's do rest dot redirect. And then we specify the page that we want to redirect. So basically slash and close it. Let's just fix the query now. So this will be the lead from user where user ID is equals question mark. We basically can get the parameters from the link, which is absolutely fine. So if we save this, I think this is more or less it. So we can definitely try this. All right, let's try. So this first record of this one here, so if we delete it, cannot get to. All right, this might be something to do with all righties. So let's go back post. All right, I've missed basically, but it looks where I just missed the two dots in order to get the ID. OK, my fault. Let's go back and click on the lead. And as you can see, this record was deleted. Let's delete this one as well. As you can see, they're going now, they're getting deleted. All right, now that we have all the lead user here, as you can see, every time I click on somebody, they get deleted. And that's pretty much them. Instead of deleting the users, you can feel free to update the record instead and have them as removed. And as you if you remember, we only display people that are active here in the view. So unless they are active, then it won't display them. So that's why you could do an update instead of the delete. And I've actually already done this. So I'm going to copy and paste it here for you. So just to save a little bit of time, we've done this multiple times now with the update. So this is how it should look with the this is how it should look when, for example, we delete this record here. So now, technically speaking, if we go and if you go and find that record, which I don't know which one it was, TTT, there is this one here. OK, it was this one here. As you can see, it's now removed and so on. But you still have the record in your database. So feel free to post the video and just copy the query. But as I said, we've done that a couple of times. So I wanted to speed up the process and just finish this video. And the last bit that we want to do is display a full view of the user. And we've kind of already done that on the home page. So we're just going to have to create another page here. So let's say we create another page called View User. So view dash user dot HBS. And for this, we're doing more or less exactly the same thing as before. So let's grab some breadcrumbs from somewhere from Edit User. So we're grabbing some breadcrumbs and then we have home. And this will be the user and then then we obviously need to prepare the rest of the stuff. OK, this is all good. So let's try to render this page first of all. So we can go to users. We can create another one here. What? Let's call it something else. This could be router dot get. And this can be the user. Then we pass the ID and for the view user, maybe we can just put review or whatever you like. Now, this is all router, but we need to create this view all and we can go back to user controller and create it right here at the bottom. So view all and let's copy one that actually the first one will work. I think so the user will work. So we just need to modify ever so slightly. Let's replace the view to view all. So view user and in here we need to do select everything from user where we can do, I mean, maybe we can just do where ID is equals question mark. And then we can pass the ID by doing rec dot params dot no. Dot ID and that would be it, I believe. And then we need to render the correct page, which will be a view dash user, user, and we need to render the rows. So if we save this, make sure you save everything. And let's focus on building the view user page now. Actually, if we go back, we need to change the link as well. So let's do that. So on the home page, we can go back down to the bottom. And we have to do is change this one here. So this will be view user and then we can do slash and then we can pass the ID of the user that we want to be. So if we do that and view the actual users, so let's see. Cabeeb, it looks like we are getting an error. Right. And let's have a look at what we're doing. So this is looking good. User, I think this is looking good as well. So it must be, I don't know if I saved this. Maybe I didn't say this. No, nothing is happening. So it must be in the controller. So we have an error. Oh, yeah, I always keep doing this. So we have an error here. Another comment I've missed. I'm getting tired of things. So that's fine. OK, so if we click on any of the user view, you will see that this page is not actually working. We just need to build this super quickly. It's not going to be anything fancy. So we can close pretty much everything now, except the view user. And let's start building this super quickly. So so let's create a quick diff with the class name of the user. And then we can give it padding top five inside here as well. We'll be adding the user details. And again, we can loop through the rows object so we can do each rows. And now we can start displaying data in here so I can do slash each to close this. And we can start displaying this data the way we want. So first of all, I'm going to speed up the process here and I'm going to put a row with a column with the first name and the last name. Then I'm going to have another row, another big row here. And this big row is basically going to contain first name is basically a table with first name, last name, email, phone. By the way, we have now we have first name and last name twice. It doesn't matter. And then at the end, we have the comments. I might fix this in a second. So if I form on this a little bit, save this. Let's have a look. All right, we do have the name repeating here. But yeah, that's pretty much here. And if we get a user which has some comments, can be directly click on him. He doesn't have any comments. OK, let's add some to the web or a champ. Save this can be this updated. We can go back and have a look at the full view. And as you can see, we have the comment. We have all the information here. And of course, you can do a little bit more on the design. OK, for the next part, where we display a message one with the letter record, we actually need to move this dot delete router anywhere below the user. And actually anywhere below these ones. And this is because we'll be messing around with the euro parameters, which you will see in a second. So let me cut this and paste it anywhere here at the bottom and save. And this should sort out the problem of being able to view the user page. Now, let's continue. One thing that I wanted to add before wrap up this video is that when we were deleting users, we were not getting any messages in here. And this is because we were doing it slightly different. We actually using redirect, which means that we can't pass an object in here. But let me show you what we can do. What we can do is in the redirect, we can actually set up a custom parameter that we can use to see when a user has been removed, and let's do that super quickly. So we can do let removed user just like so equals encoded URI component. And then inside here, we pass the message that we want to pass. So, for example, user successfully removed dot and then we can then pass the custom parameter in here. So, for example, question mark removed. And this could be equals this removed message here. So we can do plus removed user. And this is all good. When we remove a user now, let's have a look. I don't have many left. Sorry, Connor, you're going to have to go. So when I remove a user now and click the lead, you will see that this parameter just changed. And we can actually use this now on the route that renders this page to grab it and display another one of those nice messages. So first of all, let's find one of the messages I think we had in the lead. Yeah, in the edit user, we have one. So let's grab this one and add it on the homepage at the top. So we can just add it in here. Oh, no, that's breadcrumbs. Sorry, it's it's this one here, isn't it? The dismissible one. Yeah. OK, let's grab a dismissible and paste it. So this will need to change a little bit. But before we do, let's set up the other one. So where we render the homepage, which is right at the top, it's really nice we do those comments. So view page. So this is where we render the page. And this is where we could potentially grab that custom parameter that we just added by doing let, for example, removed user. User and that could be equals rec dot query dot removed. As this is the query that we done on the browser, if you remember here. So it's called removed. And if this is good enough, if we can get that removed for meter, then we can pass the removed user inside here just the way we pass the rows data from the database removed user like so. And now we can use this removed user to go here in handlebars and say if removed user, it exists, then we can either like display the message here or we can just like, actually, let's just write the money user has been removed dot. And this would be would this be a success? I guess so. Yeah, I guess this would be a success. And then if we save this, let's refresh now and see what we get. So because we have this on our URL, we're able to get user has been removed. Of course, you can dismiss it now if we now we can remove the ideas. Yeah, let's remove Alexander and see how it goes. So if you remove Alexander, you'll see user has been removed. And of course, you can pass multiple parameters and so on and make it nice. Well, this is everything from this tutorial. Congratulations. If you stick that to the end and you completed it. It is a long tutorial. Thank you very much for watching. Don't forget to smash the like on the video. You will help me a lot. Subscribe to my channel. And if you have any comments, comments below and I will see you in the next one. I hope that you enjoyed the video and that's it. Bye for me for now.