Server Side Pagination, Front End Implementation - NodeJS, MySQL & EJS

hey guys today we're going to be making this paginated list uh with node.js as the back end and they'll actually do the pagination so it's server-side pagination not front-end pagination and as you can see it's like a dummy search result well i mean the list is displayed like that anyway obviously you know this isn't coherent english so you scroll down where here's the pagination and it works like the google pagination in the sense where the first few ones it just progresses but then you go to something like nine and then all the numbers move back cause that nine's roughly in the middle now not quite and then obviously it just progresses like that and the way this actually the way the pages are progressed actually due to this query parameter this page query parameter in the url that means that we can go to a specific page if we just type in a value so for example 40 this will take us to page 40 as you can see by the red highlighting and if you go to a page which exceeds the maximum page so we just typed in 200 but it automatically redirected us to the last page and that's 114. as you can see there's an after button there because it wouldn't make sense because it's meant to be the last page but then there are there is the after button for all the previous pages and the database that i'll be using it's not mongodb even though we are using node.js it's actually my sql once any sql database will work really we'll be using my sql because we'll be using the mysql client which you can install using npm on our node.js end and this uh search result it suits to my secret or just a sql database more than mongodb because this isn't really one-to-many uh which mongodb suits quite a lot for example you know social media where there's you know videos images messages or all associated with one account that's one to many but this is just numerous rows that are just listed in order they don't even have relationships are just listed in order and there's you know thousands of them so it suits my sequel more for that so that's the reason why we're using it so if this interests you and oh yeah and also we won't be using a framework for this as well just as i said before we're just using standard ejs which is just html templating and the reason being is because nowadays you wouldn't really be paginating things like this if you were gonna use a fancy javascript framework like react because you probably more you would probably more likely to do um infinite scrolling which i've actually already covered on this channel in the youtube comments react video so yeah it's unlikely this is more old-fashioned pagination similar to php but we're not going to be using php we're using node.js so that's the reason for that and keep watching if you want to learn how to make this so here we are in a new project folder obviously it's completely empty and then we've also started on my c mysql database and this is the table that we'll be using again it was shown at the start and we're using xm to use these two things we started the patch sheet is that we have a gui to work to interface with the mysql database with so we will go to our project folder and then we'll type in cmd here to launch up a new terminal in that project folder and then we'll say npm init with the y flag to automatically say yes to everything and then with that we will if you haven't already we'll install nodemon like so using that command to install it globally so we don't have to ever do it do it again other than that we will also explore install express my c core and ejs because these are the dependencies that i'll be using for this project shouldn't take too long in fact already installed now and then we'll say code space for stop to launch up vs code in our project folder obviously you don't have to use vs code you can use whatever source code or detail that you want to use and then we will go yeah because we just created the server.js file actually we'll just zoom in a bit so you can see this um yeah that should be good and then we'll actually go to what we'll say we will say yep we'll say server.js as our main file and then we'll create start script and then that will just say no demand server.js remember our js file server.js file which literally just created inside we've installed express now so we'll require it and then we'll use that to import to initialize an app so like so express app and then we'll say listen if we are hosting it remotely then chances are we use that the port environment variable that's set by our remote server but in our case we're just hosting it locally so we use port 3000 and local server and then inside we'll say console.log server has started on port 3000 and then we will say we use that npm script say npm run start and it was going well yep server started and port 3000 so that's clearly working what we'll now do when i'll create a new file we'll call it connection.js and this will connect to our my sql database using that mysql client that we just installed so we'll first require it like so require and then what we'll do now actually we'll create some of the uh type in the username etc of our my sql database i'm gonna use that to with the my create connection method takes an object as an argument paste by default that's one two seven dot zero dot whoops.0.1 and then user again by default this is just assuming that you haven't changed the default username well yeah that creates automatically for you password i've actually used my own that's what it is there by default this will just be an empty string if you haven't set a password yourself and then the database database that i will be using is called php pagination this is meant to be a php project originally but i decided to make it my node.js tutorial instead because there's lots of those around in comparison to php paginations toils and then we'll actually connect to our our see my sql database so we'll say db using that you know that constant that we just created there db connect if there's an error then the argument will be initialized with something so we'll say if there is an error then we'll want to throw that error but if you haven't found that error then we'll just console log connected to my sql and this won't work at the moment because what we need to do we need to go back to our main file and then we will say actually before you can use this so yeah we'll need to export it so we say module export equals db once exported we could actually import that in this file here so we'll say cons db equals require and we will require that connection file like so and then now we should be able to say restart and then yeah as you can see connected to yc quartz we're seeing that so obviously you know it hasn't gone wrong and then what we'll do we will now declare static folder and then you know this will hold all of our styling we won't even have a javascript file for this project because everything will be done um in ejs so app.use express sorry.static and the folder we'll call it public and we'll also need to configure ejs so we'll say app.set view engine the view engine that we will be using is ejs what we now need to do is we now need to configure how many posts you want to show on each page so for this project again you could change this uh i'll just type this out here yeah again you can change this to your liking um but for this example i'll be using 30 because that seems to be very natural um and what we'll now do this app will only have one root and that's just like very simply the default route and then open the callback like so two arguments that we've always taken with express and very simply we'll create a query we'll query our um photos table which is actually the only table that we have in our database as you can see photos that's the only one in our php pagination database and we'll just say select all from photos so selecting all those from that photos uh table it's actually well we made the query but to actually make the query then we'll say db query and then pass in the query and then the second argument will be a callback uh if there is one and the result if there is an error then we want to log that error oh sorry not log or for it so it stops our entire application and we should now have a result so if we log we'll log this for now it will only display the first 100 but if we um oh yeah we need to go to so we'll just say localhost port 3000 like i said um yeah as you could see we're now getting a hundred look at all these all these rows now in the form of um json i'm sorry not json actually you know just javascript just javascript objects and that's how they're represented obviously it's way more than a hundred yeah it even says here ellipsis 3005 more items and what will now do we'll now actually um create the ejs file and the way that we do that ejs automatically locks in a views folder on our project folder for all of the views well our project's only going to actually have one view and that's index ejs and for now we will just create the boilerplate code using abbreviation which comes with vs code by default title i don't know we'll say node.js pagination and the google font you know actually use um why not we use this font here just get the regular one pop-ins it's pretty good fun let's copy it here and then we'll go just paste it there and then style sheets we haven't created it yet but we'll create it just a second and even though it will be in our public folder because remember that's our static folder we don't say so here we just say you know script. uh sorry not js css sorry i don't know how i made that mistake but anyway for now actually now we won't not yet anyway actually i don't know we'll say yeah fine so now that we should actually have we should be seeing something now hopefully oh wait no you shouldn't because we need to res.render and then because it's called index.hs we just say index like that and now hopefully it should work if we send the get request to that page as you can see we needed to refresh but once we refresh it's working now and that's all good but what we now need to do we now need to render out um yeah so what we'll do for now we will just render out we'll say data result so we will give all of our results to this ejs file so that we can render it out so what we'll say we'll get rid of this on ejs we could type a javascript but they need to be in between these percentage tags here so we call it data and then for each to render out to render through every single one and then we'll call each uh the iterator entry and then the following ejs is we actually need to close it so even though technically it's sandwiched in between for each line we still need to include those tags if we are writing javascript code inside and then we'll render out the title in a header and because it's a piece of data and not really javascript code um although it is data from javascript it's not actually that you know like a condition or even an expression it's just actually the piece of data here so that's the reason why we include that equals there if we didn't then it wouldn't work and then same for this and then yep so these are all i don't know if you can see this but yeah title and then id they're both fields uh and they're both columns as well you know that's where they're coming from and then now if you refresh obviously every single one literally every single one so we're gonna have to style yes every single one's listed here but we're gonna have to style this stuff so what we'll do we'll create that product folder which you required as a static folder earlier on using express and we've already required it here in our ejs file what we'll do we'll move at the default margin padding and include the margin and padding and our element width values this is just standard css practice really a lot of people start the css files with this because it just makes it more uniform across browsers and then we'll use that pop-ins font if it fails to load for some reason once you center which is installed for almost every single computer and then body i'll give it some padding so that things are moved towards the right of it and then because remember text starts off this is where text goes from right to left obviously well that's how it is in english anyway heading we'll give it some vertical uh margin the a tags we haven't got any a tags at the moment but we will soon again i'm just installing now to get out of the way margin right and then we'll set it to color blue and then we'll create a selected link tag and this class will be applied to the link that we are currently on so on page 15 then we'll give link 15 this class this break would say margin and this will simply separate out the links from the um it will separate separate out the links from the uh from the post and then what we'll do now we will remove the default styling that's applied that our browser applies to links and then we'll just say text decoration none so we'll just move the underlines we don't want our links to have an underline and that's all the styling that we need what we will now do is oh wait we'll check this out first and foremost okay well that's not working for some reason why isn't that working star.css yep so okay fine so i just typed in script.js for basically no reason i don't know why i did that so yeah this is looking better now well i mean it's easier on the eyes so what we'll now do is yep so this so if we don't get an error here we'll actually we'll say num of results and we'll store that we'll get it from result.length because remember all this data what it is is an array and we'll just get the each we'll count each individual object in the array and then what we'll now do we'll work out the number of links that we'll have in total so if remember at the start i showed you there was 114 pages well that's what this should calculate and we'll round it up so that you know the final page needs to be created even if it doesn't display you know the results per page it doesn't display all 30 results it still needs to be generated just to to display the final post which is the reason why we round it up and then we get that by the number of results divided by the results per page again math.cl to round it up and then what we now need to do is we now need to work out the current page that we're on and the way that we do that so we'll say let page equals rec.query dot page and what that does is uh hold on let's say yeah so you know i'll just show you i'll set it real quick log that so if we go to if we set a query parameter because we said rec.green.page so this query parameter needs to be page and then we say one five one two like that if we go back to our terminal we should see that number being logged one five one two oh my god where that where is our okay yeah sorry just refresh okay kind of gonna have to oh you know we're just okay i don't know what's going on here yes i don't know something's messing around here you know what we'll just start a new thing here as you can see we're getting one one five one two that's what that is essentially however if we just visit the default like home page like that and um [Music] yes that's not gonna work because if we just visit the default one like that then obviously we're not gonna if it does isn't a page query parameter set then we will use this tony operator so say number direct.crew.page and what this will do is it will turn that into a number so this is a question mark so if this is set then we will cast it into a number and we will assign it to page if not though then we'll just use one and so now i don't know why yeah it's working now because all that i don't know what i don't know why that was not working yes if we haven't set a query parameter then it will just use one by default for page and then we'll say we'll say a couple error cases here so it page is greater than the number of pages so obviously they want to access a page which is too large to exist then we'll simply redirect them we'll redirect them back to the default root and then we'll set a query parameter page equals and what will it equal well we'll say encode and this is how you set query parameters in node.js and it's simply a number of pages so that'll be it'll redirect them to the maximum page essentially but else if the alternate scenario has been met where they tried to access a page which is you know below the minimum page that we have well we'll redirect them to the same default route but this case will be one so we'll just send them to the very first page and then what we'll now do is we will now um determine uh where we started where we need to start counting uh when we send another query request as you'll see one second but the term and the sequel limit starting number and then we'll say cons starting limit equals page minus one times by result per page so what this will do is basically so if we're on page one obviously that's one minus one zero but then we times by thirty well that's still zero so we start at post zero and then we need 30 from that and then the page will just start at zero and then show the first thirty obviously at page two one times thirty is or you know just 30 obviously and then we'll show we'll start from the 30th post because remember page 1 showed the very first 30 and now that we're on page 2 i need to start from that location so and then that's essentially how that works it's like the first one that we start is the first post on this page basically how uh how far down it is in the uh sql uh table and what will now need to do we now need to make a request so we'll say get the relevant number of post for this starting page and then we'll say sql equals we use backticks for this select all from photos our table remember and then we'll limit it again we use backtick so we can use string interpolation starting limit so that's where we will start and then how long will we go for so how many more posts down we go for well that's determined by results per page so like that and then when i make another query using sql because now we assign that new command and then if there is an error then we need to throw that oh whoops yep so if there's an error we'll throw that over very simple what we now need to do is we now need to determine where we will start looping for you to get the links again this will make more sense later on so i'll say we'll say page minus five because we want to show um the first five links before the current page that we're on but that might not always be the case so if we're on page one then obviously you know we can't do page minus five because that would just be you know that would be b minus four it what it was show minus four anyway that'll be one of the links so if that is true then we will just say one we'll start from page one but if that isn't true and it's um greater than or equal to one then we will use we will be able to use it and then what we'll then do we'll say ending link and these will be iterator plus nine because the amount of links that we want to show is 10 in total and we start at iterator that's our first link and then obviously you know ending link will be nine more than that so there's ten in total if that's that's less than or equal to the number of pages then we can do that without getting in the arrows and showing a link which for a page which doesn't exist so if that is true then we can use it um but if that isn't true then we'll say page plus number of pages minus page it's the gap between uh the number of pages and the page that we're currently on we'll just finish up basically we'll just show that the final links uh to complete you know up 214 our final page essentially and then what we'll say is if ending link there is one error so now it's what happens if ending link is uh less than page plus four because what we want to do is there's so on page 114 the number of pages minus page will be zero so it would just be you know 114 because we're on page 114 so the problem with that is we won't have any um 114 will be our last link but we'll only have five links before 114 and that's not we want display 10 uh links in total not five not only five which is what happened currently when we're on the last page 114 so the way that we're going gonna fix this is we'll say iterator minus equals page plus four minus number of pages i spot that one so we're simply uh subtracting um [Music] the difference between the number of pages yeah this difference between the current page plus four minus number of pages and what that will do that will add uh four more links it will it will set our starting link for more links backwards essentially so we now show ten links in total and these are my video plus fours because nine minus five is four so therefore uh page is currently in terms of ending link it's you know page plus four will be ending link most of the time but just in this case it's not because we are not showing four more links after after um page and that's you know where's our scenario it will be met if finally we'll say it was not render oh wait sorry no you've already done that what we will do is actually we'll take it from that and then we'll move it into here and then we actually don't even need to include the values for these objects because the values will be just the values that these variables hold and we'll include all of that we'll send it to this egs file and now in this ejs file make sure if it's working yep looking good yes obviously you know only again i'm getting 30 pages now but what we need to do we actually need to render out these links so i'll say we'll say first of all include that break so there's a gap between the post and the actual links and then we'll say display the links to the pages and what we'll do now we will create a loop and we'll loop through the um where you'll see or say let i equals iterator that's our starting link number and then we'll say less than greater than ending link so that's the number contain ending link will be when our loop stops and then we'll increment it like so and then what i'll now do we'll say if i is equal to page so if um the link that's currently being rendered out is the page that we're currently on then we will have to render out an a tag like actually as we always do so um yeah i need to close that uh close it like so and then we'll give it that selected link class which we created beforehand and then we'll set the h href the hff will be go to whoops it will go to page i'll do a page query parameter and then we'll actually include scriptlets here and then we'll simply put i there so the page that we're currently on essentially it will just redirect us back to that and then we'll need to have some content to actually render out and that will be i and then obviously we'll need to close this uh if condition actually before closing it we will like continue so we'll just skip to the next uh loop iteration once this if condition has been met or once go sorry once the keyword the continue keyword has been met then we'll use can uh continue so then we'll inside we'll close off the if condition and then actually we'll just copy this now because obviously that cut that's only gonna be met once but normally we'll just render out the link like so and finally what we now need to do is we will do the before so it's actually said that i missed out yeah so we haven't yeah so what we now need to do now is yeah so we'll close the loop there so yeah this late this is for closing if statement which haven't even opened up actually yeah so this will close if condition this one will close the loop so below the loop what we will say will say if page is less than number of pages then we'll open up the link like so and then we'll just render out an a tag the href will be same as before almost the same anyway the only difference really is uh that we will be using page plus one but other than that oh yeah and also after as well and then obviously because it opened um the curly base wanted to close it like so and then before this lit these links as well we'll need to have one there this will be minus one because it's before and the condition will be if page is greater than one because obviously we're only gonna the only time we're not going to show that is on the very first page and then we go back now oh yeah so yeah we'll remove this that should only be on the that's what made it so special so as you can see we're on page one currently go to page two as you can see yeah i know this wasn't a great tutorial but you know it got the job done as you can see things are working as they should be and we're currently on page 12 which is why it's highlighted and nothing else is we'll go to what i was talking about see i mean so we're currently rendering every other time it's like sort of in the middle but the only time we don't want it to be in the middle is when this if condition here is met and when this if condition here is map we still want to display 10 links like we always do so 3 6 9 10 but obviously we this is that this is at the end now not in the middle and that's essentially what all that was about and obviously that same principle was applied at the very start as well where you know it's not at the middle because it's just page three at the very start and obviously you could check all this out all these links are different and yeah this is the pagination guys i know you know i made some mistakes here i wasn't as good as it could be but i hope you understood it if you didn't if there's if you want me to clarify anything then please post in the comments box below and i will do my best to answer any queries that you might have but i hope you have a great day and i hope you enjoyed this video and learned something from it and peace out

Channel: Qixotl LFC

Views: 1,776

Keywords: qixotl_lfc, server side pagination in nodejs, how to do pagination in nodejs, pagination with nodejs and SQL and EJS, nodejs pagination tutorial, SQL pagination tutorial with nodejs, SQL pagination tutorial with JavaScript, JavaScript pagination tutorial with SQL, mySQL pagination tutorial with Nodejs and EJS, EJS pagination tutorial, How to paginate pages with EJS, EJS paginated pages tutorial with MySQL and Nodejs, paginate pages server side, backend pagination, front-end display

Id: bRRhjs2Dqc4

Length: 34min 7sec (2047 seconds)

Published: Wed Jun 30 2021