 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 BodyParser, .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 smash the likes, consider subscribing to my channel and if you have any questions comment below. Now, let's jump into 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 MPM unit 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 keep pressing 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 MPM 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 masquale, which leads me to the last dependency that we need to add, which is masquale. 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 MPM 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. 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. As you can see, we have 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. So 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 and v 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 log something like listening on port number of 5000. So we can do console log the log and then with the template literal, we can do single slanted quotes and do you 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 we 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 comes to EXP HBS 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 middle where as I mentioned earlier, this will help us to pass JSON data through our forms. So let me show you how to do this. And 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 middle and this one will be pass x www form and URL encoded. All right. To do this, we can do app.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.npmjs.com, search for body parser, go to body parser and inside here you will see somewhere in here, here we go body parser URL encoded returns middle where that only passes URL encoded bodies and only looks at requests 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 all 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. 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 JSON 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, 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 by the way, 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 stuff 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 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.engine and we can do HBS comma EXP HBS, which we have here at the top and this needs to be outside like 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 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.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 our 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.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 call 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. Okay. This is all looking good. We have our layout setup. 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 navigation, maybe, I don't know, it's probably route is probably the right way. Okay. 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 will 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 and 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. 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 login website quick. So if we go to get bootstrap.com, I will be using the current version, which is version of 5.0.0-beta1 and if we click on get started, we can go to CSS here, grab the CSS, go back to main.hbs and just after the title, we can paste the CSS, let's go to view and toggle WorldWrap 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, icons, okay, 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 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. Okay, let's remove all this. I'm just going to keep home for now. 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 management 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 navbar 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. Okay, I'm happy with this. This would be okay. 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, 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, save, 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 homepage. 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 tab it in 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 all the 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 loop 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. So for the phone number, we can just use this, it doesn't really matter. And for the action, we're going to have three buttons. So we might as well create them now. All 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, and 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, oh, 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 would be bootstrap icons. And then bootstrap icons dash a I for 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. This would be or delete button. Okay. 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. 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 board it. Then we can make it dark at the top and class dark. I actually want to write a line 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 study 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. I have a little bit 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 Modmin and visit the PHP Modmin page. So localhost slash PHP Modmin and as you can see, I'm at the PHP Modmin 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 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 calling this one user 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 have 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'll 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. And 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. First name, voucher 45, email, voucher 45, phone, voucher 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 here, 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 two 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 voucher 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 we'll create a form as well, so that would be easy. So 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 hello, it's 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, the password, and the database. Now, if you remember earlier, we required this dot EMV file. The reason I'm using dot EMV file is because we can have like a central place for or credentials, for database credentials. So 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 underscore host. And the host name for me is good, localhost 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 root as default and database password. So let's say just database pass. And this will 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, let's say, I don't know, 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.emv.emv.dbhost is the host. Then we can do comma. Oh, excuse me, comma. I hate this pop up here because you can't see it very well. So then we have user. And for the user, we can do exactly the same as here. So we can do process.emv.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 oh, OK, I missed better. So database and list the process.emv. And then I think it's called dbname. We don't need comma here, as is 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 in high inside here, we can get two values. We can get an error or we can get the connection. And this will be an error 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 connected. And then if we connect, we can console lock something like connected and we can get the connection thread ID if you wish to. So to do this, we can do console lock and let's just console lock 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're 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 .env file, I mistakenly put the wrong database name, maybe to one or 12, save this and go back. Refresh, actually here. This isn't refresh. Maybe it's not refreshing. Why are you not refreshing? Maybe I need to save something else. Okay, let's save here. Otherwise refreshing. Let's restart it. Okay, as you can see, now we're getting an 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. Okay, 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. 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're only working with a single table name called 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 controller.js and save. This should be good. Okay. 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 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.use. And we can just put slash here to use the first route to the homepage. And then we can just pass 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.js and let me close everything else so we don't get confused. Technically speaking, if I do this, you might think that this would 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 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. Well, 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. Okay, 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 homepage, we can just a slash 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 homepage, I just want to go to the database and query all the user names. So let's just kill a view for now. And we can actually grab this now. Safe. And this is going to be our first route 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 rec and res. 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. Okay. Yeah, this is looking good. And now if you wanted to render the page, we can simply just do exactly the same thing as we done before. Res.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, obviously, let's start 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 you'll 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 MySQL here as well. So let's do that const MySQL equals require and then we require MySQL. 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 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, 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 our view, all of our exports here. So let's do that. Sorry. Okay. 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 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 rows to get the data back. And this will be a arrow function like so. And, okay, close. And inside here is where we can do some of our logic. This is also spread out. But, okay, this is where we can do some of our 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. 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 res 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 color 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 n rows. Okay. This should display the data in the console as well. Okay. 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. Okay. 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 curly brackets, close with curly brackets, and we can now use this dot and then 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. Okay, save this. Hopefully if we refresh the page now, something has happened. Do we get an error? Okay. 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 locked 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. All right. So I found the record. 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. 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, 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 removed 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 to space everything. We can make a query very specific just to display first name, last name, email and phone number. But the more I'm just using all this is not really a master 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 user, 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 save this technically speaking, I shouldn't be popping up anymore. So I'm here now. But if we refresh, I don't know why my PC is being so slow. The data from the phone. And I have a spelling mistake. So status. So if you go back, okay, 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 dating 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 find. I think find is okay. 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 homepage, 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, layout, main and inside the header here. So we need to make sure that all 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 homepage. And for 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 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. Bushwrap 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 the name here. Let's call him John. Now I'm jacket. It's going to be called search. 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 better what's happening. Of course, we're going to have to change the query to grab 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 wreck body. Let's do let and maybe we can call a search term. And this will be because wreck.request.body and search. Search is the name of the actual input that we just changed and technically speaking, I can just cancel like 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 use first name for example, first underscore name is like question mark and the reason I'm doing question mark is because I'm trying to prevent MySQL injection and you can read MySQL injection in more detail online and on the MySQL NPM 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 was percentage okay 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 HE HE and search data from user table query empty okay 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 find search term body connection query select everything from user where? oh okay I think I've missed the comma here that's why. Alright 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 okay this is working let's try to search for Henry one more time so HE 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 and 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 Nate DS here so let's search for DS now and as you can see DS 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 are only using the two 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 query a little bit more complicated 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 alright I think this is pretty good we've wired up two things I don't know let's tidy it up a little bit and continue okay I just checked and I think my computer is 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 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 new page and then we can create new rackets let's do that so again on the home page let's go to home at the top 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 .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-6 and press enter so this is the first column 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 href equals and I don't know how to name this page should we just name it add user or add-user I don't know add user that would differ now and then that's fine then we can set button and the class name would be btn and then btn primary which is blue and then we can align self-center alright and let's close this and maybe we can just do add new user or just add user 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 rift OK I don't know I must have misspelled it so justify content and come on nope maybe because I need to put I need to do the flex it's not a flexbox here we go we're in now so we have the add 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 let's just do an h2 for now which will display something new 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 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 I don't know form maybe yeah cool or whatever you like and then inside the controller we need to create this so let's go inside the controller copy this we'll need your comments and then I don't know what to call this add new user add new user and this will be form and now we can render something but let's render instead of the home we can now render add user add user and of course we're not going to pass any data just yet 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 did I save it maybe maybe it didn't save it so controller save user save and refresh add user not working add user user controller form add user oh I think this is not working because I just copied yeah I just copied this one here which is post so we need to just put this as get I assume okay let's save and refresh okay okay not bad so we have the new user but to make this a little bit better I'm thinking of using just breadcrumbs so let's just copy let's go to bootstrap and do breadcrumbs I'm going to copy this one here is quite nice this one here isn't it yep so let's copy this go back to add user and instead of new user I'm going to do home will be slash so we can go back and have a better user experience do new user with capital U save this go back and this looks a lot better okay let's start creating the form now and we can actually put the form in a partial in a second but 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 really nice you can add all sorts of forms but maybe let's use this floating layer so 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 okay but yeah the idea of it might look better actually I don't know it doesn't really doesn't matter anyway 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 gonna 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 URL that we created and we could have kept the the URL the same as this but at least you will see but at least you will see how things are working now that the naming 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 place order could be first name name float in input that's cool very important thing is that we need to give names to those inputs so my 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 this is fine we need to wrap this into another row so we can have two inputs on one line so we can do class of co-nd6 whatever or just co-6 you know what co-6 will do and have one input here let's grab one more here this one will be the last name 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 created but it's so good and let's continue by building the rest how do we know how to build this we need the email and phone number so let's copy this to I think on another line this will be phone phone the name will be phone as I said I'm not gonna do any validation here but you can spend a little bit of time to do that it will be nice and let's do what is the next thing email we swapped them too and that could be type of email this could be email at email.com this is the name so this is gonna be email the placeholder can be email at email.com email then we have the phone number in here which is cool set up 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 6 we're gonna do 12 and have this as text then this will be comments then placeholder will be comments then name will be comments save. I think this is so good and the last thing that I almost forgot is we need a button in order to submit the form to do this let's do another one so we can do code-12 and then d that didn't create it for me let's do div class of code 12 with d grid and I want to make sure that the button is full with that's why I'm doing this and we can do button with the class name or 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 alright this is looking good except the button is not looking very good I'm not sure why it's not full with is because I've misspelled it of course so here we go the button is now full with 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 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 be 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 comment this so I know alright 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 are going to have to insert some data so let's have a look at how we can do that a broken big time alright 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 we don't need an ID because we are 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 are going to get all of that data from the body and we can now actually use them 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 MySQL 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 am going to do is just test it with maybe the first name and the last name so let's say I just added the first name and what I would do is let's add the value of the first name here so we are 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 equals question mark don't forget I almost did and then space this and do last underscore name to grab the data this should be good enough to test now we don't actually need this in here because we are 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 the column first name cannot be known oh and this is again this is because we are 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 alright 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 would be form and then where is the rest render I think this will work oh this makes sense I keep making this mistake alright now we should be good to go now we are rendering the form let me show you now we are 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 rat and the name of rat is going to be 123 last name is going to be 123 and let's admit ok this submitted the form if we go back to home hopefully at the bottom you should get rat and 123 and this is pretty cool so our form is working and now we can build up 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 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 didn't add comments here so comments and equals question mark and I think maybe I need to just use toggle wrap for now and this will do the job I have pretty much added everything so let's save this, go back and see whether we can serve 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 spell 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 something bar can fix it 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 Mali was a great great spelling great spelling great spelling 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 timed out 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 boostrap 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 dismiss it so if I grab this and paste this under my add user right here can I do yeah okay 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'll 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 you pass successfully this is how you pass successfully user added successfully like so and now that we have this we can actually use it for all dismissible if we go back to art-user we can use ejs to do this so we can literally just say if hashtag if alert 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 if 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 if we go back refresh refresh let's add let's add emits 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 the 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 okay this is because this is a text area isn't it sorry it needs to be a text area okay this is what happens when you copy and paste okay yeah yeah yeah so this is text area instead I'm just going to save you some time and copy and paste one alright so instead of just changes the text area and this should do the job so let's have a look add user and yeah this is working now perfect alright 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 alright 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-user.hbs and then in all routes where is routes user in all routes we can do in all routes 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 gonna 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 add no it will be edit user 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 okay I think this should work obviously we need to put a little bit let's just do edit for now and say this works oh I don't have a link to it now but and we have an error okay we have an error because I need to put this as edit and do I need to restart okay we're good we don't actually have we actually have a link the edit buttons to the edit page but if I do edit is edit user what's our look yeah edit user and then some sort of ID we need to render edit user one okay 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 let me 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 called user form .hbs of course and now we can grab the form from where is it add 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 and to do this with EJS it's actually fairly easy so we can do curly brackets curly brackets and then the biggest sign 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 okay 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 break rooms 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 alright 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 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 ID at the moment so Amanda is 2, Alexander is 3 Khabib is 4 so let's go ahead and link this so this was our home page 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 2 and what I have to do now is do a query for the user 2 and grab the details so we can update them let's do that next let's go back to user controller and on edit we can actually maybe reuse the first query here where we have select from user where status is active and we can just modify this a little bit in fact it would 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 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 let me select ID is equals and then this would be a question mark and then we can pass the ID by doing rec.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 okay this is all good if you go back to the form which is inside partials now we can start populating the values which are not existing in the moment with the ones from the database so let's say the first name value is a value equals and then we can do curly brackets and then this dot 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 probably value will be fine email then we have a phone then we have the comments actually in the text area it's going to be different we can't oh yeah I've already added it here earlier okay this is how you are there in the text area you have to add 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 excuse me each and hopefully this should now grab the data and hopefully this should now work alright 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 this I think Bob Marley does so let's edit Bob Marley and as you can see we have a little bit 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 okay let's save this refresh yeah okay 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 would 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 alright 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 this copy paste this won't be update user okay so when we do the update user here we're gonna 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 it one by one and just make sure that it works so let's start from beginning the query will be update user set first name set first underscore name equals question mark and then last name 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 the same thing that we did earlier we can actually just do rec.parameters params.id and hopefully dash WordPress but now there are 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 okay we need to pass the id but in this case we're gonna have to loop the form outside the form like so alright 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 bit of a problem here let's have a look okay the only reason that we're getting multiple forms is again I made a mistake this needs to be 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 alright the form is here and as you can see action is edit user with the id of 5 which is brilliant so if you press submit now this should edit this user here but let's go and select user first of all let's say Alexander Vokanovsky let's edit him and let's just put 1,2,3 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 users and do edit user we need to do router post edit user id and this will be update save and hopefully now we should be good to go if you say Alexander 1,2,3 update and we're getting this weird error alright this worked but we're 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 grab the newly updated record and this is why this is happening let's redo this a little bit so we're all good here 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 per ramps 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 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 list in here and save alright let's get one of them nice alert 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 add 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 rows here and we can pass another object of alert this time let's do this with a template literal so single slanted quote 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 bracket 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 alright let's test this and here it goes it's getting complicated now so let's let's update something so Vulkanovsky maybe we say what a great what a 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 alright save this and go back the phone number didn't seem to work and this is because we never finish the query so let's finish it but this is all working and this is awesome 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 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 dating alright 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 Vokanovsky is here his phone is being removed I can add another one update it yeah okay 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 alright let's close this one here and for the for deleting for deleting records we don't have to create a new page we can just delete the record from the home page here so let's say 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 if you can't see very well see 5 when we click on this we need to do something about it alright 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 an ID and maybe we can do this one to be delete the letter alright let's save this let's go back to use a controller and let's do a quick delete alright 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, 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 delete 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 let's see 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 see all right this might be something to do with all right is 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 so okay my fault let's go back and click on delete and as you can see this record was deleted let's delete this one as well and as you can see they're going now they're getting deleted all right now that we have all delete user here as you can see every time I click on somebody they get deleted and that's pretty much done 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're active then you 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 but 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 we go and find that record which I don't know which one it was 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-user.hbs and for this we're doing more or less exactly the same thing as before so let's grab some breadcrumbs from somewhere I don't know from edit user so we're grabbing some breadcrumbs and then we have home and this will be I don't know the view user and then we obviously need to prepare the rest of the stuff ok this is all good but yeah so let's try to render this page first of all so we can go to users we can create another one here let's call it something else this could be router.get and this can be view 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 view user will work so we just need to modify ever so slightly and 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 the is equals question mark and then we can pass the id by doing rec.params. id and that would be it a belief and then we need to render the correct page which will be view-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 that and view the actual user so let's see it looks like we are getting an error 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 save this maybe I didn't save this no nothing is happening so it must be in the controller so we have an error oh yeah we always keep doing this so we have an error here another comment I've missed I'm getting tired I think 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 with the class name of the user and then we can give it padding top five inside here is where 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 this big row is basically going to contain first name is basically a table with first name and last name in my phone oh 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 format this a little bit save this let's have a look how it looks alright we do have the name repeating here but yeah that's pretty much here and if we get each has some comments Kebib did I click on him he doesn't have any comments okay let's add some to Kebib what a champ save this Kebib is 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 okay for the next part where we display a message we actually need to move this dot delete router anywhere below the add user and actually yeah anywhere below these ones and this is because we will 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 add 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 gonna have to go so whenever a user now and click delete 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 home page at the top so we can add it in here oh no that's breadcrumbs sorry it's it's this one here isn't it the dismissible one yeah okay 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 home page 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.query.remove 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 parameter 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 manually user has been removed . 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 able to get user has been removed of course you can dismiss it now if we remove the ideas 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 it 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 from me for now