Create Pagination in PHP and MySQL

hi welcome to this tutorial and I'm going to show you how to create a pagination system using PHP so the first thing we have here is a my sequel database with our alphabet from A to Z and data entries 1 to 26 so the first thing we want to do is we want to create a PHP page which I've done here and connect the first thing we want to do is connect to the database I'm just using my local machine here so it's going to be localhost username username is root and password is empty and our database name is pagination so the next thing we want to do is define how many results we want for a page so to do that we're going to first define a variable called results per page make that equal to 10 for now and we can change that later on as we wish the next thing we want to do is find out the number of results stored in our database so we want to create a sequel query and say select everything from our table name which is alphabet and then we want to return we want to run this query result is equal to my sequel I Korean connection and school and if we do a number of reloads are plenty on McVitie cool to my sequel i num rows and equi distant well you should get 27 or 26 so let's go to our page index 2 dot PHP call to define function this is going to ask their number of rows back refresh and be at 26 and if we want to display all of the results we want to do pay a while statement while row is equal to my sequel line fetch array result and echo out row ID space head Quatro off of that space and bracket tag we go back to our page we get results 1 to 26 a tizzy next we want to determine the number of total pages available to do that we're going to create a variable called number pages and have that equal to the number of total results so number of results divided by results per page so if you echo this out we should get two point six so two point six which is twenty six divided by ten now if we change the results per page to five and refresh this we get five point two and for now we're just going to leave that at ten so we'll have two point six so we want to round that up to the nearest integer so we're going to have a whole number of number of pages so we're going to use the PHP function called ceiling so wrap those variables around with a ceiling function if we go back you have three next we wanted to UM display the links for each page so to do that we're going to create a four function so for page and pages are variable for which page we are on so page for page is equal to one we're going to say while page number is less than or equal to the number of pages we want to increment one for the page result and echo out the link tag and here we want to echo out the page number to the link we want to create a space afterwards for for some margin and then we want to create aa for the link index 3 K HP plush mark page variable and that is equal to echo out page number so if we go back to our page refresh get undefined variable number of pages so number of pages here's the missing half right there so you go back save that and go back we have one two and three so we want to I'll be no longer need to equal the number of pages out so remove that we would remove the echo and we go back we have one two and three so currently we're on index two dot PHP if we click on one we go to page one click on two we go to page two and the click on three we go to page three next we want to determine which page number the visitor is currently on do that we're going to create a if statement so if the page variable is not set so exclamation mark is set and get page if the patient variable is not set we want to have the page variable equal to 1 if the page variable is set however we want the page variable is equal to get page next we want to determine the sequel limit starting number for the results on the displaying page so to make sense of that we want to retrieve a selected a specified set of results for each page so if we want to go on if we want to go on page 1 we want results h1 we want results 1 to 10 1 through 10 h2 we want results 1 shot 11 through 20 page 3 you want results from 21 to 30 and so on and so forth so we have to create a sequel limit limit query to do this and to show you how it works we will go back to our sequel query from above that we created earlier and we want we're going to make a limit of let's say 0 and 5 so that's going to start from result number 1 and show you 5 results so a b c d e so we refresh the page we have 1 3 results 1 2 5 so if we go back and we say let's say we start from ah f or the sixth letter 5 and we want to show up 10 results we go back refresh the page we start from result number 6 and show up to number 50 or F through oh so that's how the limit function works and if we go back to our data entry here let's say we have page 1 and currently we have 10 results per page which is our variable here results per page is equal to 10 so 10 results per page and the limit must be a 0 and 10 for page 210 results per page you have to limit 10 and for page 3 you have 10 results per page limit 20 and 10 page for tenure salts the page limit 30 and 10 page 510 results per page limit 40 and 10 and so on and so forth so we have the page number determining the starting limit number so we have let's say we have the starting limit number is equal to an algorithm of page 1 so let's say page 1 minus 1 multiplied by 10 so we have page 1 which is 1 minus 1 which then becomes 0 multiplied by 10 is equal to 0 0 multiplied by 10 is equal to 0 then if we have page number 2 we have 2 minus 1 multiplied by 10 is equal to 10 so 1 times 10 is equal to 10 and patient over 3 is 3 minus 1 is 2 2 multiplied by 10 is equal to 20 so what we have here is page number minus 1 x number of results per page and that's how we determine the starting limit number so if we take this concept and bring it back we want to create a starting limit number so I'm going to create a variable called this page first result which is the starting limit number and we want to have the page number minus 1 x results per page so if we echo this out on page 1 we have page 1 we have 0 which is starting limit number page 2 we should have 10 and page 3 we have 20/20 right here so that's correct so now we want to create a new sequel to our query to retrieve the Select results from the database so it's SQL query is equal to select everything from the table alphabet put the limit of this page first result coma results per page now we want to run this query result is equal to my sequel line tour a connection sequel and we want to display the results from this query so we're going to create a while statement again while role is equal to my sequel I fetch results sorry fetch array results going to echo row ID multiply space call pyro alphabet vibrate type and we want to UM we don't we no longer need the echo from above so we want to comment that out if we go back here we have a my sequel fetch array expects parameter okay guys I found out error and I have the table name is incorrect here so that's that should be one P in alphabet and if we go back refresh the page we have 21 to 26 through Z which is page number three if we go to page number one we have a through J or one to ten and page number two we have 11 to 20 or k2t now we cannot go back to the file and change the number of results for page 2 for example 6 now if we go back refresh the page we have 5 pages so 1 2 3 4 5 so that's how to create a pagination system in PHP uh and I hope you found this tutorial helpful thank you for watching bye

Channel: Simon Suh

Views: 129,427

Keywords: php, pagination, mysql, tutorial

Id: gdEpUPMh63s

Length: 15min 22sec (922 seconds)

Published: Sun Nov 20 2016