We now have a youtube channel. Subscribe!

How to Create Pagination using PHP and MySQL

Create Simple Pagination Using PHP and MySQL

Hello folks! welcome back to a new section of our tutorial on PHP. In this section of our PHP tutorial, we will be studying about how to create a Pagination using PHP.

It is always possible that your SQL SELECT statement query may result into thousands of records. But it is 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 displaying your query result in many pages instead of just putting them all into a single long page.

MySQL helps to create paging using LIMIT clause which takes in two arguments. First argument as offset and second argument how many records should be returned from the database.

The following below 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 our next tutorial guide, we are going to be discussing about how to Update Data into MySQL Database using PHP.

Feel free to ask your questions where necessary and we 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.
© 2023 ‧ WebDesignTutorialz. All rights reserved. Developed by Jago Desain