PHP Pagination Tutorial MySQLi Google Style Paged Results Programming

pidgin asian asian asian asian asian asian asian presentation presentation presentation presentation hello let's start with the bare bones of an html5 web document mine is named pagination example dot PHP the first thing I'm going to do is go up above my doctype and I'm gonna put in my opening PHP tag and then I'm gonna go down a couple of lines and put in my closing PHP tag now since we're gonna be querying a MySQL database for all of our data that's going to be paginated we're gonna need a MySQL I based connection to the database but you have to establish a MySQL eye connection and you do that right here at the top of your script or somewhere near the top you just have to make sure that that is in place before you try to query anything from the database and if you need to see what this MySQL I connection that PHP file looks like you can look at the one of the first few tutorials in the social network building series that we did not too long ago shows all of the inner workings of that MySQL I connection file but I'm just going to assume from past tutorials that you guys have watched of mine that you know how to make a MySQL eye connection to your database so basically you're just going to include that connection file right there so I'm gonna place it in my SQL syntax and you're gonna see that I'm tapping into my testimonials table in my database and I'm gonna use that as a real live example so towards the end of the video I'll show you the script working at develop PHP calm to render out my testimonials and paginated results instead of one giant list now we run the query against the database and this is a MySQL I based query so we put in the connection variable first and the second parameter that we put in is our SQL syntax which you could take all of this SQL syntax right here and just put it right there if you want but I like separating mine out like that now we're going to access the row for that query result set because all this query doing is getting a count of how many rows are in the testimonials table where approved field is equal to one and many times you wouldn't have any conditions in there you might just be listing out or trying to get the total count from your testimonials table or your products table or your whatever table but since I have a little condition in there I just want to make sure that the testimonial is approved before everybody can see it in the list so in my database I have an enum field called approved and it's set to 0 at first and then when I approve the testimonial that people write in then it becomes a 1 so I just want to make sure that I'm counting only the approved testimonials now in the very next line we're gonna type ourselves a comment in that says here we have the total row count by specifying the row variable here and then accessing its first and only index because we're only selecting one item from the testimonials table which is the count so you're really only going to have one array item in that row index so you've accessed the first one and that gives you the total count of rows from this testimonials table so here we have the total row count and on the very next line we're gonna type in another comment that says this is the number of results we want displayed per page so let's create that variable now we're gonna name that variable page rows and we're gonna make it equal to 10 because I want 10 items to be listed on each page basically I want 10 testimonials to come out on each page so I make this page rows variable equal to 10 if you want only 5 items to be on each page just make that a 5 and you can play with that number all you want now we're gonna place in another variable well first let's place in a comment to tell us what this variable represents the comment reads this tells us the page number of our last page so let's put that variable into place now because we have to know what is the very last page of the result set according to how many rows are going to be laid out on each page so we name the variable last and we run the seal function with is which is a math function in PHP and we say Rose which is the full rows count from the database divided by 10 basically or whatever you made this number and that gives you that little calculation result gives you the last page variable so if your result set from your database is page it out in 288 different pages then this last variable will be 88 and seal is just a way to round the number up because when you divide this number or when you divide rows by page rows don't really want a floating-point number or anything you want a number without any decimals that's the only reason we're running seal on this little equation here now since some of the variables are going to be in the URL that means people can manipulate the variables and so we're gonna put in another comment that says this makes sure last can not be less than 1 so let's put a little if condition right here just to make sure that last can never be less than 1 before our script process is that variable so this if condition reads if last is less than 1 then we make last equal to 1 now let's write it in another comment that says establish the page number RIA below page num is going to be equal to 1 by default but it's gonna be changed down here in the script when we gather the URL variable for the page number so let's write in another comment to ourselves that says get the page number you are l variables if it is present if it's not present we're gonna make it equal to 1 by establishing it right here as 1 so basically you establish page and I'm very well as one here and then we're gonna see if there's a get variable set of pn because that's what we're gonna name our variable when we send it through the URL so we say if is set the get variable of pn then we're gonna gather up that get variable of pn and it's gonna represent a number whichever page the user wants to go to and we got to make sure that we run preg replace over that and filter it to where it's only numbers that can be in that URL variable so only if that variable is set if that variable is not set in the URL the pn variable then page number is going to be equal to 1 every time unless it's set in the pn variable but if it is set in the URL then page number you'll be comes whatever this pn URL variable is in the the address bar after you go through the tutorial you can go ahead and remove all of these comment lines and that'll make your script a lot slimmer and make it look a lot smaller but just keep the comments there till you fully understand each line or each segment of code okay so right under all of that we're going to place in another comment that says this makes sure the page number isn't below one or more than our last page variable we don't want page num to ever be below a 1 or more than the last page and this little condition will handle that we say if the page number is less than one then the page number is equal to one so basically somebody can take the URL address bar and change it from one or whatever page they're on to zero and what you want to do is make sure that you change that 0 to a 1 so if it happens to be less than a 1 then we change page number to 1 else if the page number is greater than the last page number then page number is going to be equal to last so basically if you have 88 pages of results and somebody changes that 88 the last page in the URL address bar maybe they change it to a 92 you just want to make sure that that doesn't process in your script and you change page number 92 that they input to 88 so this if condition or this if an else if condition just make sure that the page number isn't below of one or more than your last page now let's put in another comment that says this sets the range of rows to query for the chosen page number and that looks like this we make a limit variable and this limit variable is going to be sunk right into our next query remember this query up top this one was just to find the full count of how many rows there are so we're gonna need another query that actually runs a while loop and gets results out but we have to limit that query because we only want a certain amount of items out of the database but only according to what page they represents you understand so if you're on page three you don't want to gather all of the first rows in the database you want to make sure you gather a result set that corresponds to that page 3 so you really have to understand the limit syntax for SQL if you don't understand the limit and what limit does in an SQL query didn't you're not gonna understand this tutorial so what you want to do is make sure you look into and make sure that you understand what limit does in SQL queries it just limits how many you're grabbing out of the database now let me put in the next comment and the query so this next comment says this is your query again it is for grabbing just one page worth of rows by applying the limit variable so I say select ID first name last name the date made from my testimonials table where approved equals one and I'm gonna order all those results by ID descending and then my limit syntax goes on the very end of the query you can see what my limit syntax is made up of limit then space and it's going to be two numbers and the first number is made by taking page num minus 1 and multiplying it by page rows basically 10 because we made page rows 10 up here then the next number for your limit syntax is going to be 10 or whatever page rows whatever value you made the page rows variable alright so that is all of that explained I can't explain it any clearer than that and I'm telling you if you don't understand what limit does in SQL syntax then you're never gonna understand this tutorial now finally let's run that query and the query reads MySQL I query your database connection and the second parameter again is your SQL syntax which is right here now the next line we're going to place in another comment that says this shows the user what page they are on and the total number of pages so I name the variables text line 1 and text line 2 and we're going to be echoing these out to the page in just a moment so the first text line one says testimonials and then you're gonna put the number of rows the full amount of rows from the database so if your database has a thousand testimonials in it that number will be right here and then that would mean since we're making 10 rows come out per page means you'll have a hundred pages so this last will be a hundred and this page number will represent whatever page they're on so text line one says testimonials the full amount of testimonials and text line two will show the user what page they're on out of how many pages there are total in the very next line we're gonna put in the comment that says establish the pagination controls variable and that's going to be equal to nothing when it's established so we have a variable ready called pagination controls and what we're gonna do is add to that page nation controls variable to make all of the clickable numbers and the previous and next buttons and all that stuff now what I'm gonna do is just make everything simple links pretty much how you would see in Google style results when you go to Google and you search you can look at their pagination at the bottom and we're gonna make that style of pagination or very similar to that now under that we're gonna put in another comment that says if there's more than one page worth of results let's put in an if condition here if open close parentheses opening curly brace go down a couple of lines and put in your closing curly brace and we want to check to see if the last variable which is represents the last page is not equal to one that means there's more than one page worth of results because your database might only have five rows in it and that means you would only have one page which is the first page so you really don't have to give them next in previous buttons or little links for numbers of pages to click because there's only one page so within this if condition is where we're going to build up this pagination controls variable because only if there's more than one page worth of results are we gonna render those little things at the bottom okay now I'm gonna put in a little multi-line comment so this says first we check if we're on page one if we are then we don't need a link to the previous page because we're on page one so there are no previous pages and we don't need a link to the first page which is page one so we do nothing because we're already on page one so you don't want to have a little link that says one to go to page one so if we aren't on page one then we generate some links to the first page and to the previous page well really the number one will be paged out in our group so the if condition we need to check for all of that is if page num is greater than one and in that condition the first thing we'll do is we'll say previous variable is equal to page numbness one and previous is a new variable that we haven't established yet in our script it's just being established now so the previous variables equals a page number minus one so if you're on page seven previous is going to represent page six and also the first little bit of rendering that you want in your pagination controls is going to be a link that is set to the server self so whichever page you're on it doesn't matter you don't have to put the page name there but you want to make sure you append to the URL the page number variable that's the get variable the URL variable and you want to make sure that that is equal to the previous variable remember like I said if you're on page seven you want to make sure that there's a link that says previous so that's the label in the link it's going to be the word previous and it's going to be going to whatever the previous pages now we're going to place ourselves a comment under that that says render clickable number links that should appear on the left of the target page number so here we're going to use a for loop for that so with a for loop you're gonna feed your for loop three parameters the first parameter is what you want to start the index at so I named the variable I like we usually do in for loops and that's going to be equal to page number minus four so if you're on page number seven this I variable is going to equal seven minus four which is what three so in that case if the user happens to be on page seven this will represent a three then the second parameter is our evaluation for the for loop which is while I is less than the page number able so let's use that example of being on page seven again so if this would be equal to a 3 so that means this is saying while 3 is less than 7 then this loop is going to keep running in the way that I gets incremented is your last parameter within the for loop which is I plus plus so within the for loop we just want a small condition that says if I is greater than 0 because we don't ever want a 0 to render in those little clickable links so if I is greater than 0 our pagination controls is going to get more little links popped into it and the pn variable is going to be equal to that i variable and then you just put it in the I variable there as the link label and that's what gives you the little number to the person can click on so basically this code right here gives you everything you need that is on the left side of your target number so if you happen to be on page 7 we want to have 4 little links that go back to page 6 5 4 & 3 on the left side and we also want a little word that says previous so the person can use that as well now on the right side of the target page number which lets say for example the user is on page 7 we want to have four more links that go to page 8 9 10 and 11 and then after that we want the word next so just remember that this code here renders everything on the left side of the target numbers so we're gonna go outside of this if condition right here so down under that if condition where that curly brace ends for that if condition we're gonna place in another comment that says render the target page number but without it being a link because that's the page that the person is going to be on so you really don't want to have that be a link and it can be styled any way you want but we just won't really don't want to make it a link because they're already on that page so there's no reason to make it a clickable link so pagination controls were appending to that variable the value of page num and then a little space now after you show down in the pagination controls the actual page number that the user is on then you're going to put in another comment that says render the clickable number links that should appear on the right of the target page number use a for-loop for that as well now let's pop that loop into place and I'll explain it real quick and you pretty much have to understand how for loops work to really grasp the whole script and once you understand how four loops work which is a very simple in our case they might look a little complicated here because we have a lot of different code at play but if you look at a basic for loop it's very simple to comprehend so I would recommend if you don't understand how four loops run to research four loops in PHP that way you can see it at its most basic level operating for loops always get three parameters here so the parameters for this for loop is going to be establishing the I variable again or the index if you will of this for loop and we're gonna make this one equal to page num plus 1 the first pass of the loop so if we're on page 7 this is going to be an 8 so I is going to be equal to 8 then we say while 8 is less than or equal to whatever the last page is then we're going to I plus plus and run the loop some more and that's how we're going to get the four numbers on the right side of the target page in the pagination controls while this loop is running we just add little links to the pagination controls variable and each link is going to have this I for loop index as its label so if you happen to be on page 7 the first time this loop runs it's gonna render out an 8 the second time this loop runs it's gonna render out a 9 the third a 10 and the fourth time it runs it's gonna render out and 11 now this loop might have a larger ceiling than 4 so what I did is I just said if I is greater than or equal to page num plus 4 then we're gonna break the loop because really I only want this loop to run 4 times so what I did was I just put an if condition here that breaks it will stop this for loop from running after it runs 4 times and I also put this if condition because this loop might only run twice because if you only have 10 pages let's say you only have 9 pages worth of data and user is on page 7 you don't want to render out page eight nine ten and eleven because there's only ten pages or nine pages so you definitely don't want the ten and eleven numbers there which is what would happen so basically in just in certain cases this little if condition helps you break the for loop from running any further to render numbers out there that don't correspond to any pages that exist in your pagination now remember in this condition we said if the page number is greater than one then and only then we're going to put a previous link and little numbers going back to previous pages we're going to do the same thing down here in the last part of this if condition that started way up top here so this if condition is gonna have one last little bit of cold popped into it let's pop that in right now this does the same as above this does the same as this condition right here but for showing the next the little next word which is a feature that you might want in your pagination is to have the word next after all those little numbers just the same way you have the word previous before all of the little numbers so if the page that they're on is not equal to the last page then you definitely want to have the next button but if they are on the last page you don't render anything so this won't this code won't even run if they're on the last page only if they're not on the last page will this code run and you'll get more stuff added to your page ination controls variable and the way you make the next link is you just say page num one so if they happen to be on page seven next equals page eight and that's what gets put into your pn variable here now after all of that you go down one line and make sure you're outside of your if condition that you said if last is not equal one you see where that ends right here you go outside of that and here's where you're gonna render your while loop like you normally do for a MySQL result set what we're gonna do is first establish a variable called list because that variable is going to be echoed out to the page so we're gonna compound that variable with all the data that we need for each little testimonial within this while loop so basically you run your normal while loop here for this last query that was made right here so this while loop will grab all of the rows that you need for that specific page that the user is requesting to see those ten items so in the while loop I'm gonna grab the ID out of the database row I'm gonna grab the first name the person's last name the date that the testimonial was made and then I'm just going to put the date made into a more human readable format and then I'm going to make my little list which each item in the list is going to be in a P tag because I want them separated a little bit down the page and each one is gonna have a link to the proper testimonial which will render on testimonial dot PHP and the ID URL variable will be sunk in right there and I'm gonna show you guys all of this working in just a moment so the label for that link is gonna be the person's first name last name and the word testimonial then the link closes right there and then I'll have some text that just says click the link to view the testimonial or I could actually put some of the testimonial that they wrote in here I could put a few lines in here if I want you can put whatever you want in here from the database results and then I put a break tag and I put written on the date made and then I finally close my little paragraph tag for each one that renders and then the very last thing underneath your while loop outside of it you're gonna write yourself a comment that says close your database connection so we close our MySQL eye database connection here because we don't need to be to connect it to the database any further past that point now let's go down into our HTML where you can see we just have the bare-bones still there's nothing in our body tag there's nothing in our style tag so within the body tag I'm gonna place the following code which is a div element and in that div element I'm going to have an h2 heading and the state of pagination top row I got to change those to be text line 1 there's text line 1 there and text line 2 is going to be placed here first we're going to have an h2 that shows the total amount of results from the database and then the word paged then text line 2 is gonna render right here and I already explained what text line 1 and text line 2 are going to contain then in the next paragraph element we're gonna put the whole list which these could be div elements or whatever you want I just decided to put them in paragraph elements so they have a little margin space on the top and bottom so here is your full list that's coming out of your while loop then finally underneath all of that we're going to put another div with an ID of pagination controls and then inside of that we're going to echo out the pagination controls variable and that's what's going to put out the little previous all the little numbers and next now I'm gonna put in some CSS it's going to affect the body element on the page just to make the font-family what I want it to be then we're going to target the div down here called pagination controls and in the pagination controls are going to make the font 21 pixels and then any a elements that get rendered to the pagination controls box those are going to be colored light blue and we want to make sure that we give the pseudo selector of visited also a color of light blue or else you might get different colors to your little numbers down there and you might not want that you might want them to stick to one uniform color so what I'm gonna do is run this exact script on my live server on develop PHP and list out my testimonials with pagination and you guys can see this exact script running so you won't have any doubts to its speed efficiency or it's working condition so make sure I save that I'm gonna load it up to my server and run it all right here I am a develop PHP calm and let me pop in the URL to my test pagination example dot PHP and you can see what I have it says testimonials this is the text line one testimonials 105 total paged and then text line two says page one of eleven pages then you can see I have ten different testimonials that I can click on and go read if I click on this one you can see that I have gone to that testimonial Paige is testimonial dot php' with an ID of 210 if I click on the next one down I go to a different testimonial with an ID of 206 now let's check down at the bottom of our page and look at the pagination controls now since we're on page number one we don't need to have a clickable link for page one and we don't need to even see the word previous on the left side but what if I click page two let's try that you can see the previous appears now I have all new different testimonials for page two here and it says page 2 of 11 in text line 1 or I mean text line 2 so the text line 2 variable is gonna render out page 2 of 11 because we're on page 2 now down here you can see that our dead link is actually the two now which as it should be so let's click page 3 page 4 page 5 page 6 page 7 so you see how that works the further you go your your dead link is going to be in the very center and you're gonna have four links on either side now let's go to page 10 and make sure we don't have 4 more links that are showing up here because there's only 11 pages so you don't want to have 10 11 12 13 and 14 to show up you only want 11 to show up so if I click on page 11 and if I go to the last page I don't want the next link to show up and I want my dead link to be the last page which is 11 now so if I go to back to page 7 you can see that I have everything there showing that should be showing now if I click 3 1 everything works perfect see and you can see how fast that runs it runs pretty freakin fast and you guys have to remember that you have to clean your variables because people can manipulate what's in the URL and Google's the same way you can change the URL variables and go to whatever page you want sometimes but if I want to go to page 6 I can just put it right there press ENTER and now I'm on page says page 6 of 11 you see down here we're on page 6 so just keep in mind that people can change these URL variables very easily in this press ENTER and your page is gonna process whatever they type in so if I type in poopoo right there I just want to make sure it goes to page 1 and our script is set up to filter all this stupid crap people might put in up top here so your script to set up smart if you use this script because it's going to filter out any stupid crap people try to put in a prop to mess with your system oh hey buddy all right ok so I hope this tutorial has helped you wrap your head around pagination and all the little aspects that you might need to work into your scripts for PHP and whatever kind of database that you're pulling data out of and you can also use this same logic that we used in this script if you want to use Ajax based pagination so you'll see sometimes like Facebook and Twitter they'll have like an ajax based pagination to where the page never refreshes and you can make just one little box show your pagination results on the page according to what people are clicking at the bottom and if you guys want I can maybe put together a script that shows ajax-based pagination without using any frameworks it's pretty easy to script all of this stuff without using any frameworks because PHP and JavaScript in their raw form are simple enough for children to use and the only reason we use frameworks is when we don't know how to program something and we need to accomplish that task but Ajax based pagination like Facebook and Twitter use is not very hard to wrap your head around and I can show you how to do it without any frameworks you won't have to use jQuery you won't have to use any PHP frameworks like CodeIgniter or whatever people are using it can all be raw code that you can fully wrap your head around and script yourself from scratch it's and it doesn't take too much time so my point is if you want to see Ajax pagination from me when you see it from other people it's probably going to have jQuery and maybe other frameworks involved if you want to see Ajax pagination from me make another tutorial for you guys you just got to let me know

Channel: Adam Khoury

Views: 107,482

Keywords: pagination tutorial, php pagination tutorial, php tutorial, mysqli tutorial, google style pagination, mysql tutorial, php, mysql pagination, php pagination, paged results tutorial, learn web development, learn web design, learn php programming, learn mysql, mysql, learn, web, development, design, online, free, lesson, teacher, student, education, school, video, class, classroom, adam khoury

Id: T2QFNu_mivw

Length: 31min 13sec (1873 seconds)

Published: Wed Jul 31 2013