User Management System – Nodejs, Express, MySQL & Express-Handlebars

what is going on everybody my name is radi and you're watching my channel rally the brand today i'm going to show you how to create a full crit application using node.js and mysql we will be using a few dependencies such as express express handlebars the body parser dot env and mysql to run my database i will be using xampp 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 [Music] 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 xampp xampp is free for mac linux and windows if you wish to follow along you can pause the video go ahead install xampp 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 my scale 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 your mac or linux you might have to just cd manually which is not a big deal once you do that cd to your project folder let's do npm you need and create a new project the only thing i'm going to fill here is the package name so 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 the all of the dependencies at once doing npm install and we can start listing them the first dependency that we need this tool is express and this is for all express server that we're going to create so let's do express then we need dot emv and dot env is basically going to keep all database credentials we have express handlebars we have the body parser and the body parts is basically a middleware that is going to help us pass data for example from some of the forms when we submit them we can grab some of the data and maybe insert it into the mass scale which leads me to the last dependency that we need to add which is mysql okay press enter and this should take a couple of seconds to install the next thing that we need to do is install nodemon nodemon is a development dependency and that's why i'm installing it separately so let's do npm install dash dash save 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 project we can finally open our project in our favorite editor for me this is video studio code but just open it in the one that you usually use so i can do code dot and this will open the project for me and if you use another editor obviously just go to file and open your new project once you're in editor just open package.json so we can have a look at it super quickly and as you can see we have the name version description and so on the important bit here is the dependencies that we just installed and the development dependency which is null month 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 normal 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 add then listen on that port number let's do that so let's do conf express equals require and then we require express like so and close we now need to initialize express and to do this we can to do this we can do const up equals express like so and close and then we need to specify a port number for or express server to listen on so we can create this we can create another const port equals and here if you wish to publish your app somewhere we could attempt to use the environment port number just by then process dot environment env dot port or we can use the full 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.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 five thousand so we can do console.log the log and then with the template literal we can do single slanted quote and do you listening on boards 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 powershell i've actually cleared this just now but if we go back to the powershell and we do npm start you will see that hopefully you will see that nodemon is starting the nodeapp.js and it's listening on port 5000 which means that all express server is now working and we can continue by adding all 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 dependency that we need is the express handlebars let's do comms e x p h b s 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 our rendering engine let's now add the body passes so const body parser like so require and then we require the body parser excel and the um we also need to require mysql so let's do cons mysql equals require and then mysql the last thing that we need to require is the emv but this one we can do slightly different we can do it by doing require and then inside here we just say dot env 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 parts in here and the body parser is basically a passive middleware as i mentioned earlier this will help us to pass json data through all forms so let me show you how to do this and i'm going to keep everything clean with a few comments so everybody uh knows what the what it is and you can reference it later in the article that i will be creating so let's do passing middleware and this one will be plus x w form and url encoded all right to do this we can do app.use bodyparser 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 on the 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 euro encoded returns middleware that only passes your 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 the documentation it's pretty good usually so yeah let's continue let's go back one thing that i didn't show you is that our server is running but if we go to the website and if you refresh you should get this message cannot get and this is because we actually haven't set up any routes and we're not rendering anything just yet but we'll do this in a second let's go back to the application we've done the url encoded but we also need to make sure that the body parser is using adjacent so pass application slash json and we can do app.use and then body pass dot json close this and we should be good to go we're done with the body passer we can now use it and i will show you how to do that later on once we created some of the forms another thing that i want to do early is set up my static files now static files are the files that you might want to add on your front-end such as you might want to add a custom css file you might want to add some custom javascript files or you might want to have an easy access to your images and to do this we need to basically set up this comes from express by the way we need to set up or static files just by using app dot 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 mine 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.css write your styles in there obviously when i compile my scss this will create the compiled main.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 templating 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 handabash 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 templating engine so and or and handlebars it's very similar to ejs it uses views and layouts folder as a default i will show you in a second but one thing about handlebars is that the default file extension is actually dot handlebars which is fairly long so in order for us to change this we can actually do app dot engine and we can do hbs comma exp hbs which we have here at the top 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 e dot h b s 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 hanovers so app.set and inside here we do exactly the same as ejs we do view engine and we set the v engine to be hbs this is it now i just said this uses by default uses views folder and layout now layout 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'll explain as i go and in this folder we're gonna have to create another one called layouts and of course this is the default but you can change it if you wish i do like the default option here so i'm going to keep it as it is and inside here the default layout file it's called main so let's do main and h b s 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.hps it doesn't really matter that much as long as you know which one is home it's fine and this needs to be inside views okay this is all looking good uh we have all out setup we have we have a home page and i will show you how the layouts work in a second but the last thing that we need to do in uh 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 specify 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 uh this will be a narrow function and inside here we can use the response dot render and we can render the home page so home and we don't need to specify the hbs extension here it's smart enough to know what to render the engine and we can actually close this yeah i think we're done and let's close this as well and we're done hopefully if we go back to the uh npm here sorry the powershell uh we shouldn't get any errors which is good and if we go back to the browser and under localhost portal 5000 if we refresh you will see that we no longer get that cannot get this means that all 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 gonna be our main layout of our website which we're gonna 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 or 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 is can will be consistent throughout all of the pages and we can just continue working on this now that we're here i think it's a good time to start doing the layout of our page and for this tutorial i'm going to use bootstrap because it's the most familiar framework out there and most people know how to use it and we can make a fairly decent looking website quick so if we go to getbootstrap.com i will be using the current version which is version 5.0.0-beta1 and if we click on get started we can go to css here grab the css go back to main dot hbs and just after the title we can paste the css let's go to view and toggle world wrap so we can see a little bit better i think i've zoomed in quite a lot and that's why and also i want to add the javascript of bootstrap which we can find here i'm just going to grab the bundle so let's grab this and this we need to paste just above or ending body tag here so let's paste it like so and we should be good to go and the last thing that i wanted to do is add the bootstrap icons which are separate so we can go back to grab the icons i think icons okay yeah go under icons and the url is icons.getbootshop.com and then under install we should be able to grab the cdn so let's grab that and we can paste it in here save now we should have everything that we need from bootstrap and we can start building all out as you can see the icons are pretty cool you can just find the icons that you like select on one and this will give you the code i already know which ones i'm going to use so i'm going to save you the time and close this we can also close this one as well but i will need the documentation so on the 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 that 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 uh you can see there is a lot of this because i've zoomed in quite a lot but if we write what 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 and because this is the homepage 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 it a little bit instead of having the navbar as light i'm gonna 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 you can con containers and they give you loads of options in here but the one i'm going to be using is just the default one here and copy this and for the container i'm actually going to wrap the body itself because i want the 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 uh for now so maybe padding top five padding bottom five i'm sure that would look nice yeah that's looking good before you start building or database let's actually build a little bit more on our front page and on the home page i want to display all of the users and if you're displaying a lot of data we might as well just use the table from bootstrap so let's do table grab the first table that we see this one here maybe we can modify it to make it slightly better looking so let's grab this we can now close this because we're done with it and let's open the home hvs page which is our home page and let's start building our table so let's paste this this is our table and we're gonna need to display a few things so for example this is fine then we can display the first name name then we can display the last name then we can display the email of the user and let's create another row in here so alt shift and down arrow will copy this line so we can do phone and the last thing that i want to do is action action will be things such as viewing the record deleting the record and edit it so let's do action and we need to i mean yeah to keep it simple let's remove those rows they're gonna be uh 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 uh 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 dot mdo.com and then we have a phone number so for the phone number we can just use like this it doesn't really matter and for the action we're gonna have for the action we're gonna have three buttons so we might as well create them now for the buttons i'm just going to use three links and we'll link them as we go along so let's create a link well a button link so href equals i'm going to keep this empty for now uh but of course we'll come back to it very soon so this will be href and inside here we can say type button and then class name will be btn btn btn light then we let's make it small so btn small and last but not least we need to put something in this button so this will be our view button and i also want to add an icon and to add an icon as i showed you earlier we can do i and then class name will be bootstrap icons and then bootstrap icons dash i4 view i think that would be a good one and let's just make a little bit of space this is our first button so let's duplicate this two more times by holding alt shift and down arrow twice and this would be or edit button and this would be or delete button okay i think this is good enough um 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 x which is like a delete record icon if we save this go back to the page let's have a look at what we got all right this is not too bad at all i could do a little bit of i mean it would be nice if the text was centered and it would be nice if this was right so let's modify ever so slightly so it doesn't look as bad as it is so to modify the table we can do it with borders so we can do table dash boarded then we can make it dark at the top and class dark i actually want to right align this action and the buttons so let's do so let's do a class of text and and let's copy this class so we can use it on the buttons here like so um let's tidy this up save this go back refresh much better i think i don't know if the dark bit worked yeah i'm pretty happy with this obviously we can spend a little more time to make a 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 xampp and i've already started my apache and mysql which means that i can go to localhost slash phpmyadmin and visit the php landing page so localhost slash phpmyadmin and as you can see i'm at the php madman admin page and my username is set to root which we'll need later on and my password is set to password so let's press ok and login here on the left side are all databases these are some other projects that i've worked on but today we're going to create a new database and let's to do this we can go here on the left side at the top create new and we need to give it a name so i'm thinking of just calling this one user management underscore tutorial and well underscore tooth as 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 ha and we need to give it a few columns the amount of columns that i want are gonna be seven so let me change this and let's press go and by the way you can do this with sql if you're familiar with sql it might be faster for you and also as i am creating this i will post the sql file for you on my article if you wish to save a little time but it's fairly easy to create in both ways so i will show you so first of all our 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 varchar and i'm going to keep it simple for now maybe like 45 characters but of course depending on your application uh you have to make the choice of uh how much length you want for that and so on so let's keep it simple for now last name varchar 45 email varchar45 phone voucher 45 then comments can be actually currents can be text because that could be like a big text and the text it takes a maximum length of 65 535 as you can see here so let's select this obviously probably won't have that many coins 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 i don't know 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 varchar and we just keep it with words so so i want to make sure that the status of the user originally when we inserted a record is actually set to active to do this we can change the food 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 uh schema this is how you type schema.skl 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 under sql and insert the command manually so let me save this first of all please enter valid length valid length for comment or valid length for this oh okay let's say 10 and i'm gonna 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 or user table everything is in here and as i said if you wanted to insert the code you can go to sql paste it in here and just go and this will create exactly the same structure as i have inside here we have the key which is good this is our primary key and we're good to go one thing that we could do is create a record just for now but later on i have prepared a little sql file that you can insert as well and use instead of creating records manually but also will create a form as well so that would be easy so one thing let's let's do one thing and create a record as the first one um id we don't have to specify this will be unique and it will be automatically generated so first name i'm gonna have ready last name i'm gonna have the brand email i'm gonna 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 okay 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 up and if you remember the top we included mysql so let me show you how we can actually use it maybe here under the templating 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 cons bull equals mysql and this will be dot create full this is how we create a pool and inside here is where we can specify the connection limit the host that we want to use the user of the user the password and the database now if you remember earlier we included we required this dot emv file the reason i'm using the emv file is because we can have like a central place for or uh credentials for database credentials so let's create dot let's create this file and enter all database details before we start before we create the pool so to do this let's create a new file dot env and inside the dot env file we're gonna have db we're gonna have db underscore host and the host name for me is good localhost because i'm using xampp here locally the database name will be equals i actually forgot what it was called so let's go back to the database and copy this this is our database name here then let's go and create the database user and for me this was you and root as default and database password so let's say this database pass and this would be password for me all right i'm done with the dot emv file and now i can get those variables and pass them inside here so we can reuse them and we can reuse this on many pages and we have one central place where all details are saved let's start by doing the connection limit first of all we can specify this by doing connection limit and you can read a little bit more about this in their documentation it's all really well explained and this would be let's say something like hundreds 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 dot e n v dot db host is the host then we can do comma oh excuse me come on i hate this pop up here because you can't see very well so then we have user and for the user we can do exactly the same as here so we can do process dot e and v dot database user uh 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 up and the last one that we need to set is the database name so database like so and oh okay i miss better so database at least the process env and then i think it's called db name we don't need comma here as is the last value let's save this and now we need to connect tool 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 pool 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 arrow 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 okay and this would mean that something is wrong and we did not connect so not not connected and then if we connect we can cancel log something like connected and we can get the connection thread id if you wish to so to do this we can do console.log and let's just console look something like connected as id and then space and then we can add the id here by using connection dot thread id add close technically speaking if we have entered the database credentials correctly or date and or database is running if we save and refresh all browser actually if we only save if we go to powershell or terminal you should see this again we are listening on port 5000 and you should see this connected as id 247 and this is because i've probably used it quite a lot uh and this is all good we didn't get an error which is good but if i do make a mistake let's say in my dot emv file i mistakenly put the wrong database name maybe to one or twelve save this and go back refresh actually here did this doesn't refresh no maybe it's not refreshing why are you not refreshing maybe i need to save something else okay let's save here otherwise i'm refreshing but let's restart it okay as you can see now we're getting a error and this is saying mysql message unknown database user management to 12 and so if you get this maybe your credentials are wrong so let me go back and fix this by changing it to back save this and let me just do i have to restart come on do your job okay it's so 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 your code grows uh this could get really messy and it's best to be organized and this is why we're gonna create another folder now called server and inside this server we're gonna separate the route from their logic so we will have route 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 gonna have controllers and controllers as i said earlier this will be basically where our logic goes and let's create another folder called route and the routes are basically kind of like the pages that we'll be creating inside the route let's create a new file called uses sorry inside routes let's create a new file called user and this would 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 gonna 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 uh user capital c key 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 let's actually cut this i might end up using it in a second and we need to first of all tell all up where our routes are so all wraps are obviously inside server and then right to do this we can create a cons 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 now we can just put slash here to use the first router the home page 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 use this.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 cons express equals require and then express and now we need to create the routers or const router equals express dot 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 we refresh we're getting an error this is most likely because we need to explore this router and this is because we actually need to explore export 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 it should be working and yeah everything is working as it used to before so we all 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 uh and then we need to select user controller we don't need to specify jis and we can just close this and now in order to use this user controller we can actually do router dot get and because we want to hit the home page we can just do 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 um which function that we want to use for example we might have a couple of functions for example we could have a create one we can have find we can have updates we can have delete and so on so we need to create that as well and for the first one let's just call this one a view because when i hit the home page i just want to go to the database and query all the usernames so let's just click view for now and we can actually grab this now save and this is going to be our first route here using all or user controller and now inside user controllers we we're gonna actually have to create this view to do this we can simply do exports so let's do view view users and let's do exports view equals and then this will be exactly the same as we did earlier so request and response so let's do rack and rest and this will be i think i'm doing it wrong am i doing wrong now this is correct yeah that's correct and this will be like a so i believe okay yeah this is looking good and now if you wanted to rent the page we can simply just do exactly the same thing as as we've done before rest dot render and we render the page that we want so all of our logic is going to be here now and to test whether this is working obviously let's start it up a little bit save go back refresh and as you can see everything is working now all routes are separate to or controllers which will make or 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 current control here so it will be like client controller you change this to client controller and then you can obviously copy this in here and have different functions for client controller or you can even spare or you can even do this for every single user controller as well you can the different ones but uh oauth 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 mice comes mysql equals require and then we require mysql and if you remember in app.js where we created this uh poll and we created the connection we can actually copy this now and reuse it so this is the great thing about doing the about doing the process dot emv is that we have the database uh 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 userview so obviously we're gonna 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 views on all of our exports here so let's do that sorry okay we get the connection pool here it's so 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're getting 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 familiarity it's exactly the same as you would do in well it's my score 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 a 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 gonna 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 why we can do some of the 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 comma here when done to do this we can do connection dot release like so close it and now let's just add a very simple if statement so if we get uh some data we can then render the page but if we don't maybe we just display a console maybe we just can't lock 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 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 can't log it log i guess you could render like a page saying i don't know like uh there has been a mysql right you know that's totally up to you so let's just do error if we want to save this and also one thing that i forgot which is probably the most important thing is that we need to pass the data that we're getting from the database using rows so to do this we can inside here inside the render we can pass objects by doing comma and with the curly brackets we can just pass the object the object of rows and also if we if you wish we can display this inside the console by doing console.log log and then we can do a simple message here like the data from user the table then we can do slash n rows okay this should display the data in the console as well okay this is all good if we don't have any errors let's say go back to the page refresh nowhere 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 hanavas 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 of 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 clearly brackets close with quickly bracket and we can now use this dot and then the the table id that we want to select so for example so for example i can do a 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 uh 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 uh somehow and i don't know let's say i removed i don't know ready one two three and save this record and if i refreshed you'll see that this is actually coming from the database and it's actually console logged 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'll be nice to have a little bit more data is this zoomed in yeah this is zoomed in so much but of course it'll 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 records i basically added some random uses 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 and we go back to the users as you can see we have quite a few users and this is looking good so if i go back to my page and refresh you will see that we are getting all the users listed which is pretty cool my pc is being a little bit slow so i'm going to reset this let me restart it all right this is all looking good of course by doing different mysql queries you can order your database um 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 i had to press go obviously at the moment or query just displays everything we can make our query very specific just to display first name last name email and phone number but at the moment i'm just using oh this is not really a mysql tutorial so i'm not gonna 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 uh status of active so let's say we have select 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 you refresh i don't know why my pc is being so slime the data from table undefined oh and i have a spare mistake so status so if you go back okay if we go back and refresh you'll see that i'm no longer appearing but my record is still in the database here and this is how you could potentially keep the user data in your database just in case something happens like maybe you delete one by mistake and so on you can also just delete them if you wish and obviously 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 in my we might as well just uh 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 um like so for the search let's create another export just like this so let's copy this and let's paste in here and let's do i don't know find i think find is okay and close of course we need to wire this up and let's comment as well find user by search something like this and we need to wire this up inside the routers so user and we can simply just copy this here so router this time we need to post because we'll be posting from the search box so let's change this to post again we're posting on the home page but we need to change this to find so uses the new block of code that we just created save this and we should be good to go here if we go back to user controller this is where we're going to be adding the query but before we do this we actually need to modify the form now the form was under views layout main and inside the header here so we need to 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 home page 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're inserting anything into a database you should validate uh your data and i've actually done a full-on tutorial on this which you can watch i will 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 bootstrap makes that super easy to do just have a look at the forms with wired or form but one more thing we need to find what the input name is so at the moment we don't have a name let's add the name here let's call him john now i'm joking it's gonna be called search okay this is what we're gonna 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 graph the value from the search box which we named search very good name we need to graph we need to grab the value and to grab the value we can use the rec dot body let's do let and maybe we can call it search term and this will be rec dot request.body and search search is the name of the actual input 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 the text that i've vented we know that this is going to work so let's move on as well that we might as well just do the query now and to do the query you can go a little bit more complicated than i would in here but i'm just going to show you a super quick example here so let me start from beginning so we need to select everything from user and we need to say where for example let's let's use first name for example first underscore name is like question mark and the reason i'm doing question mark is because um i'm trying to prevent masculine injection and you can read in 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 where's 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 h e h e and search data from user table query empty okay let's have a look what i've done all right i don't know what i've done here but let's have a look uh request that's fine search term body connection query select everything from [Music] user where question mark oh okay i think i've missed the comma here and that's why all right i usually make silly spelling mistakes but i think of mr kama 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 e h e and then search and as you can see we're getting henry but if we look for another thing another detail in this database let's say uh 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 uh 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 um would it break if i go under the line yeah probably it's fine i need to do the javascript way of doing another line but that's fine let's save it and go back refresh and let's search for so we have made this here so let's search for this 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're only using the two uh those two rows and of course you can make it the way you like so if we do run the rousey again let's do rousey or let's just do everything with r so as you can see this displays absolutely everything even my name even though that i'm not active and this is where you can make your query a little bit more complicated but if i do render we just get ronda and this is where you can do the uh where status is active you can put this into the query and that would also work and just show you the active users all right i think this is pretty good we've wired up two things i don't know let's tidy it up a little bit and continue okay i just checked and i think my computer is bringing a little bit slower because my c drive was almost full so i just deleted a few files and we can go ahead and do the next bit should we i think that we should do the form next so the form will basically we need to create a button here and we click on the button we create new page and then we can create new records let's do that so again on the home page let's go to home and at the top oops at the top of the table i want to add i want to add a button and to make it a little bit nicer i guess we can let's do another row so we can do dot row this is a class name and then we can do two columns so maybe the first one can be actually both of them can be six so let's do code that's six and press enter so this is the first column and this is the second column so maybe in the first one we can just say something like h1 uses and in the second one is where we're going to be adding a button for the button i'm going to have a link so href equals and i don't know how to name this page should we just name it add user or add dash user i don't know add user that would do for now and then that's fine then we can set is that button and the class name would be btn and then btn primary which is blue and then we can self align self center all right and let's close this and maybe we can just do add new user or just add other user would do i think this would be okay 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 uh the full width and it becomes a little bit ugly did i save or did i riff okay i don't know i must have missed powder so justify content and come on nope maybe oh it's because i need to put i mean i need to do the blacks it's not a flex box here we go 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 news or ad user add user probably add user dot h b s and save so this is where we're going to be adding the users so so let's just do an hd for now which will be which will display something uh let me use it just for now just to test it let's just add something of course we're not rendering this so we're gonna have to go to routes uh in inside here we're gonna have to create a new one and this one and this one is gonna be at user and for add user maybe we can just call it i don't know four maybe yeah color whatever you like i'm going to call my form and then inside the controller we need to create this so let's go inside the controller copy this copy the comment as well we'll need your comments and then i don't know what to call this add new user user and this will be form and now we can render something but let's render instead of the home we can now render at dash user user and of course we're not gonna pass any data just yet okay save this and i just wanna see whether this is working so if i refresh now this isn't working and let's have a look at why so we have form here rest render did they save it maybe maybe it didn't save it so controller save user save and refresh i'd use not working um add user user control form form add user oh i think this is not working because i just copied yeah okay 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 bread crumbs i'm going to copy this one here is quite nice uh this one here isn't it yeah so let's copy this go back to add user and instead of new user i'm going to do homework so we can go back and have a better user experience and then we can just do new user with capital u say 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 let's start by creating the form first and then i will show you how to insert the form inside a partial so if we go and search for forms let's find one that we want they're really nice all right you can add all sorts of phones but let's maybe let's use this floating label so i'm not sure if i like it they're quite big but let's use that anyway we have email and passwords but we can build on it yeah let's use that see how it looks he might be okay but yeah i did i want my look better actually i don't know it doesn't it really doesn't matter anyway so let's start creating a form i'm thinking i'm overthinking it now so to create a form we need to start adding or input but also don't forget that this is a form so we're 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 uh you can have validation as well needs validation if you wish and then the method which is very important here needs to be set to post and we need the action in this point to be slash add user just like the user the euro that we created and we could have kept the the url the same as this but at least you will see uh 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 placeholder could be first name floating input that's cool very important thing is that we need to give names to those inputs so mines are there now so 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 it three let's have it as six no no no this is this is fine we need to wrap this into another row so we can have two inputs on one line okay so we can do class of co dash nd 6 or whatever or just call 6. you know what code 6 would do and have one input here let's grab one more here uh this one will be the last name so last last last name and we're good to go let's save this and see how this looks as you can see we have first name and last name the animate quite nicely i have some testing inputs here which i haven't cleared but it's so good and let's continue by building the rest now that we know how to build this we need the email phone number so let's copy those two i think on another line uh this would be phone on the phone the name will be phone [Music] as i said i'm not gonna do any validation here but you can spend a little bit of time to do that it would be nice and let's do what is the next thing email no we have email first and then we have phone all right we swap them too and that could be type of email and this could be email at email.com oh no this is the name so this could this is going to be email sorry and this the placeholder can be emailed email.com whatever and email then we have the phone number in here which is cool setup and the last one that we have is the comment so let's just grab this and paste it in here and instead of column six we're going to do 12 and have this as text then this would be comments then placeholder will be comment 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 co dash 12 and then d uh that didn't create it for me all right let's do div class of call 12 with d grid and i want to make sure that the button is forward that's why i'm doing this and we can do it button with the class name of btn btn primary the type will be submit this time this is important that the type is submit so it submits the form and in here we can just say and i submit form or add new user adds submit it will be fine let's have a look all right this is looking good except the button is not looking very good i'm not sure why it's not for it it's because i've missed building of course so here we go the button is now for it as well and this is looking nice the comments can be adjusted as well a little bit and we can basically just say style height 200 pixels something like this and this is making it so much nicer so we can now go back and we can add a new user and of course we need to wire this up and insert the data to the database let's have a look at how we can do that next so i wrap 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 usercontroller.js we created this add new user us form maybe we should have maybe should be more descriptive but we only have one 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 add so this is the page that we want to render as well uh maybe i'll just comment this so i know all right so first of all let's now that we know that this is the page we're rendering we might as well just change instead of home to be add user and so inside here we get the connection and the important bit here is the query so instead of selecting data this time we're gonna have to insert some data so let's have a look at how we can do that oh i broke it big time all right anyways so inside single quotes i can start writing the uh query here so we need to insert the data but before that we can actually we need to grab the data from the body and we can destructure the data by doing something like this so let's destructure the data by doing const and then we can grab the first name and then we can grab the last name then we can grab the email then we can grab the phone then we can grab the comments and we know and we don't need an id because we're not submitting an id here the id is automatically added from the database and all this will be equals request body so every time we get a request we submit the form we're gonna get all of that data from regbody and we can now actually use them use those 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 learning a question mark and what i suggest you right now right here is to keep this simple because you can easily get lost as your query gets super long so what i'm going to do is just test it with maybe the first name and the last name so let's say i decided the first name and what i would do is let's add the value of the first name here so we're going to pick up this first name and with a comma we can add more values so first name and let's add the last name as well so we can separate this with comma last and it's called name and i think it's 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 now we don't actually need this in here because we're not going to display any data but we could create a custom message and i will show you how to do that after so let's see if this works if we go back to the form let's refresh just in case see whether we get any errors the column first name cannot be no oh and this is again this is because um we're getting this error now again because i keep making exactly the same mistake and this is because in users we have a get but we don't have a post all right okay sorry about this so we have we can get the form and just render it but we need to post and the 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 so good we can actually put this as create and we can just create another one just to render the form and this will be form and then where is there is where is it rest render okay i think this would work i hope this makes sense i keep making this mistake all right now we should be good to go now we're rendering the form let me show you now we're rendering the form here but if you want to create something we need to post and then use this code here okay it's so good now refresh and let's have a look at whether we can insert the name so let's say right add the name of write is gonna be one two three last name is gonna be one two three and let's submit okay this submitted the form if we go back to home hopefully at the bottom you should get a rad and one two three and this is pretty cool so our form is working and now we can build up on it and add the rest of the values so we need to add email phone comments and so on let's do that okay so we have first name last name then we have email equals question mark then we add email here then we add what is next phone equals question mark then we add phone then i don't know what's uh what is an easy way to put this in two lines i think it's because of zoom so much as well but uh let's leave it so phone and then comment but i didn't add comments here so comments and equals question mark i think maybe i need to just use toggle wrap for now and this will do the job all right i have pretty much added everything so let's save this go back and see whether we can search a full user with all the details so this one will be bob and we can have mali then we can have both at malay if i spell it correctly.com and if you want to call bob we have this number and then we can just put some currency oh that's weird that my it's weird that this happened back and 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 marley was a great great spelling great spelling okay and submit if we go back you can see that we have bob marley at bob manning his phone number is here and if you go to the database which has stamped out let's do password and if we look at more records you will see that all of them are now inside here so all form is now working but the user experience on this form is not great because we submitted something and it just didn't give us a message so i can just submit something right now obviously don't have the validation to save this time but as you can see it just refreshes and that's it so what i'm thinking of doing is i'm thinking i'm thinking of adding a little dismissible message here from bootstrap so let's do dismissable 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 this misses it so if i grab this and paste this under my ad user all right here can i do yeah okay so maybe under the bread crumbs 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 dismissable 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 create new 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 hobby space successfully all right this is not how you suppose successfully these are added successfully like so and now now that we have this we can actually use it for all dismissible if we go back to art dash user we can use ejs to do this so we can literally just say if hashtag if alert so if there is something in alert then we want to display the message of alert and we can do it like this alert and then we need to the end the if statement here like so 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 you user added successfully let's have a look if we go back refresh actually not done refresh is going to post refresh let's add let's add emmet and then we can just submit as you can see we have used alex successfully and this is pretty nice so we can dismiss it and add more and so on and you can do the same thing for an error if it errored out you can just maybe do an error alert and so on but i don't want to like overly complicated and let's and i think that we're pretty much done with this i just need to fix the comments box which is like this because oh 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 paste one all right so instead of instead of input just changes the text area and this should do the job so let's have a look um add user and yeah this is working now perfect all right we know how to add data we know how to search the next best step to do would be to edit the user and this is because we are already working with the form so we might as well finish everything to do with the forms and let's make sure that this edit is working and yeah so we can just update the details all right first of all let's create the edit user page let's go back and we can create a new page in here so edit dash user dot hbs and then in all routes where is routes user in a route we can do in a route we can do a get so let me copy this and we can do the page as maybe edit user and also we'll have to pass the id of the user so i'm going to do this now and explain it a little bit later and also we need to have a custom thing for editing the user so let's do edit instead and we should be good to go let's now create this edit function in user controller so instead of create we say edit so add no it will be edit user like so and we can render the page that we want so residual render and the page that i want to render would be edit user maybe user okay i think this should work uh obviously we need to put a little bit let's just do edits for now and take if 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 haven't linked the edit buttons to the edit page but if i do edit is edit user what topic yeah edit user and then some sort of id we need to render it uh edit user one okay this is working so we put that it is a one we're getting the edit which is good now to edit the user it might be best to reuse the form that we created uh in here in adding user and what i'm going to do is actually i'm going to do a partial uh 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 userform dot 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 bigger sign and then use a dash form which is the name of all 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 like so all right this looks a little bit better this is exactly the same form now obviously we'll need to change the actions the action in a little bit but we'll come back to this in a second now let's go back and let me explain something first of all we need to edit this button to go to the page which i'm gonna do this in a second but we also need to link the id every single record has a unique id at the moment so amanda is two alexander is free khabib is four 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 um 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 all right let's go back to user controller and on edit we can actually maybe reuse the first query here wherewith where we have select from user where our status is active and we can just modify this a little bit in fact it will be very very similar so let's copy this and go back to the edit i'm going to keep this here and just replace it actually let's just replace this to edit and now what's happening here is we want to get the id from the url here so we know that when we click on a user this gives us a unique id and we want to grab id that id to do the query to do this we can actually to do this it's actually fairly simple we can just do it inside the query actually i believe we could just do it you might have to do some checks on this but we could quickly do it by doing select everything from users where id come on you want let me select id ec calls and then this would be a question mark and then we can pass the id by doing rec dot params parameters and then the parameter that i want to add is the id all right technically speaking this is good and now this is gonna 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 there is 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 excuse me this dot first name and now let's just copy this multiple times and do it for the the rest let's do here so you can see so last name then we have uh do it does it need to be in the place order no probably value will be fine uh email then we have a phone then we have the comments actually in the text area is going to be different we can't oh yeah i've already added it here earlier okay this is how you are there in the text server you have started 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 forage inside here age rows and then we need to close it in here oh excuse me slash each and hopefully this should now grab the date debate and hopefully this should now work all right as you can see we are now populating the data unfortunately this user doesn't have any comments uh 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 of space here for some reason but that's fine i think it might be something to do with uh 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 will be fairly easy we just need to grab this data and update the same record so let me show you how to do the query so let's close everything that we don't need all right let's push a little bit more and we'll be done soon update let's get let's get this one actually and we'll work from will work from this copy paste this will be update user 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 sorry set first underscore name equals question mark comma 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 we can do the same thing that we did earlier we can actually just do rec dot parameters params dot id and hopefully dash wordpress but now there are a few things that we didn't set up now we didn't set up the form first of all so if we go to edit user we need to make sure that we change the action to be edit user and then slash we can pass oh okay we need to pass the id but in this case we're gonna have to lube the form outside the form like so all right i think this should work and now we can actually grab the 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 oh okay um we all good the only reason that we are getting multi performances 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 last form form form all right the form is here and as you can see action is edit uh user with the id of five which is brilliant so if you press submit now this should edit this user here but let's go and select a user first of all let's say alexander volkonovsky let's edit him and let's just put one two three submit and nothing happens let's have a look why this is because we haven't actually put our writer yet so if we go back to the users and do edit user we need to do router post edit user id and this would be update save and hopefully now we should be good to go if you say alexander123 update and we're getting this weird error all right this worked but we're also getting this error where okay as you can see the user is updating but we have a little bit of a problem here we're actually updating the user which is absolutely fine but we also need to go to the database and and grab the newly updated record and this is why this is happening let's redo this a little bit so we're good here it's everything is working the data is updating but we just need to do one more query so we could potentially we could potentially go and copy this one here actually this would be exactly the same query that we need so when we update it we want the page to post again and get the record so technically speaking inside this if not error statement we can do get connection and then we can go select from id where user id echos request user params and then we just need to render the exact page with the exact same page which is render edit user and so on i think this should work actually this is a very easy fix to do so save uh let's go back and edit alexander again so let's remove the one two three one two three oops and save and as you can see this saved the record and if i go back alexander is now clean he doesn't have one two three one two three but if i remove his last name for example save it 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 it paste in here and save all right let's get one of them nice alerts here again so i'm going to copy this one here again in fact we can copy it from the other page that we created 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 uh 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 and 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 did so dollar sign squiggly bracket and first name uh and then has been updated to be a little bit better so now this alert it's gonna be displayed in here all right let's test this and see how it goes it's getting complicated now so let's uh let's update something so volkanovsky maybe we we say what a grey 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 donate his number so nobody can contact him you contact me when you have a problem all right save this and go back the phone number didn't seem to work and this is because we never finished the query so let's finish it but this is all working and this is also this is what i was saying just make sure that you test with two values first and then just keep on adding because you can you can easily make a small mistake somewhere and it's just gonna 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're updating all right let's test this should work now so we can refresh remove his phone number say what a great what a great fighter submit this is working all of the details are staying here the way this so if i go back you'll see that alexander vokanovsky is here his phone is being removed uh 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 all right let's close this one here and for the yeah for deleting for deleting records we don't have to create a new page we can just delete the record from the home page here so let's say so let's just pass the id that we want to delete in the link so this would be the delete button we'll just grab 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 uh five when we click on this we need to do something about it all right this will be obviously a post function so what we can do is go back to our routes and let's create a new route and this one will be similar to those two i guess so we could post with we could post with an id and maybe we can do this one to be delete the letter all right let's save this let's go back to user controller and let's do a quick delete all right let's grab something small that we can modify maybe the edit and let's go back to the bottom and say update sorry delete now the two ways of deleting the user you could just change the status to removed bundt or whatever you like or you can permanently delete the record so let me show you first of all how we can delete the record so everything in here is pretty much the same and let's just change the query so this obviously needs to be changed to delete now which is good then the render page needs to be home again so when we delete something we need to return to home but this time i this this time i don't want to do multiple queries so what i can do is actually when we delete the user we just want to redirect instead of render so let's do that let's do rest.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 or less it so we can definitely try this all right let's try so this first record of this one here so if you delete it cannot get to all right this might be something to do with all writers so let's go back post all right i've missed basically but it looks where i just missed the two dots uh in order to get the id so okay my vote uh 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 it won't display them so that's why you could do an update instead of the delete and i've actually already done this so i'm going to copy and paste it here for you so just to save a little time we've done this multiple times now with the update so this is how it should look with the this is how it should look when for example we delete this record here so now technically speaking if we go and if we go and find our record which i don't know which one it was ddd there is a this one here okay 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 gonna have to create another page here so let's say we create another page called view user so v dash 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 anna from edit user so we're grabbing some crepe crumbs and then we have home and this would be i don't know the user and then then we obviously need to prepare the rest of the stuff uh okay this is so 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 uh warrants let's call it something else um this could be router dot get and this can be view user then we pass the id and for the view user maybe we can just put a view all or whatever you like now this is all router but we need to create this view ooh and we can go back to user controller and create it right here at the bottom so view 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 id is equals question mark and then we can pass the id by doing rec dot params dot no dot id and that would be it i believe and then we need to render the correct page which will be view dash user user and we need to render the rows so if we save this make sure you save everything and let's focus on building the view user page now actually if we go back we need to change the link as well so let's do that so on the home page we can go back down to the bottom and we have to do is change this one here so this will be view user and then we can do slash and then we can pass the id of the user that we want to view so if we do that and view the actual user so let's see khabib it looks like we're getting an error all right and let's have a look at what we're doing so this is looking good user i think this is looking good as well so it must be i don't know if i saved this maybe i didn't say this no nothing is happening so it must be in the controller so we have an error oh yeah i always keep doing this so we have an error here another comma that i've missed i'm getting tired i think so that's why okay so if we click on any of the user view you will see that uh 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 div div with the class name of view user and then we can give it padding top five and inside here is where we'll be adding the user details and again we can loop through the rows objects 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 gonna have another row another big row here this big row is basically to contain first name it's basically a table with first name last name even a phone oh what do we have 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 all right we do have the name repeating here but yeah that's pretty much it and if we get a user which has some comments did i click on him he doesn't have any comments okay let's add some to grip what a champ save this can be 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 when we delete the record 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'll be messing around with the euro parameters which you will see in a second so let me cut this and paste it anywhere here at the bottom and save and this should sort out the problem of being able to view the add user page now let's continue one thing that i wanted to add before i 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 right direct 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 remove 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 a 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 when i remove the using 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 delete 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 just add it in here oh no that's breadcrumbs uh sorry it's it's this one here isn't it the dismissable one yeah okay let's grab the dismissable and paste it so this will need to change a little but before we do let's set up the other one so where we render the homepage which is right at the top it's really messy we do those comments uh 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 remove user user and that could be equals rec dot query dot removed as this is the query that we've 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 hanover 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 money user has been removed dot and this would be would this be a success i guess so yeah i guess this would be a success and then if we save this let's refresh now and see what we get so because we have this on our url uh we're able to get user has been removed of course you can dismiss it now if we now we can remove the ideas yeah let's remove alexander and see how it goes so if you remove alexander you'll see user has been removed and of course you can pass multiple parameters and so on and make it nice well this is everything from this tutorial congratulations if you stick that to the end and you completed it it is a long tutorial thank you very much for watching don't forget to smash the like on the video you'll 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 you

Channel: RaddyTheBrand

Views: 24,817

Rating: 4.9855771 out of 5

Keywords: nodejs, node, javascript, user management, express, express-handlebars, mysql, body-parser, nodejs for begginers, nodejs mysql, nodejs database, node js tutorial, nodejs event loop, node js, node js mysql, node js mysql tutorial, rednering, npm, handlebars, tutorial, js, bootstrap tutorial for beginners, bootstrap tutorial, css, scss, raddy, raddy the brand

Id: 1aXZQcG2Y6I

Length: 134min 5sec (8045 seconds)

Published: Fri Feb 12 2021