Tuesday, October 6, 2020

How to Create Pagination using PHP and MySQL

Hello dear readers! welcome back to another section of my tutorial on PHP. In this section of my PHP tutorial, we will be studying about how to create a Pagination using PHP.

It is quite possible that your SQL SELECT statement query might result into thousands of records. But its not a good idea to show all the results on a single page. So we can divide this result into many pages based on your own needs.

Paging simply means showing your query result in many pages instead of just putting them all into a single long page.

MySQL help to generate paging by using LIMIT clause which takes in two arguments. The first argument offset and second argument how many records should be returned from the database.

Following is a simple example to fetch records using LIMIT clause to create paging.


Try the following example below to display 10 records per page -

      <title>Paging Using PHP</title>
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $rec_limit = 10;
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         /* Get total number of records */
         $sql = "SELECT count(emp_id) FROM employee ";
         $retval = mysql_query( $sql, $conn );
         if(! $retval ) {
            die('Could not get data: ' . mysql_error());
         $row = mysql_fetch_array($retval, MYSQL_NUM );
         $rec_count = $row[0];
         if( isset($_GET{'page'} ) ) {
            $page = $_GET{'page'} + 1;
            $offset = $rec_limit * $page ;
         }else {
            $page = 0;
            $offset = 0;
         $left_rec = $rec_count - ($page * $rec_limit);
         $sql = "SELECT emp_id, emp_name, emp_salary ". 
            "FROM employee ".
            "LIMIT $offset, $rec_limit";
         $retval = mysql_query( $sql, $conn );
         if(! $retval ) {
            die('Could not get data: ' . mysql_error());
         while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
            echo "EMP ID :{$row['emp_id']}  <br> ".
               "EMP NAME : {$row['emp_name']} <br> ".
               EMP SALARY : {$row['emp_salary']} <br> ".
         if( $page > 0 ) {
            $last = $page - 2;
            echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Records</a> |";
            echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Records</a>";
         }else if( $page == 0 ) {
            echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Records</a>";
         }else if( $left_rec < $rec_limit ) {
            $last = $page - 2;
            echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Records</a>";

Alright guys! This is where we are rounding up for this tutorial post. In my next tutorial guide, we will be discussing about how to Update Data into MySQL Database using PHP.

Feel free to ask your questions where necessary and i will attend to them as soon as possible. If this tutorial was helpful to you, you can use the share button to share this tutorial.

Follow us on our various social media platforms to stay updated with our latest tutorials. You can also subscribe to our newsletter in order to get our tutorials delivered directly to your emails.

Thanks for reading and bye for now.


Post a Comment

Hello dear readers! Please kindly try your best to make sure your comments comply with our comment policy guidelines. You can visit our comment policy page to view these guidelines which are clearly stated. Thank you.