Change number of rows show in the Pagination using PHP

While creating the pagination it is required to set how many records to fetch at a time from the Database Table. It helps to paginate all records.

You can allow the user to change the defined number of rows visible at a time on the page by providing them an extra option.

Demo Download

Table structure
Configuration
HTML and PHP
jQuery
Conclusion

1. Table structure

I am using employee table in the demonstration.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  `salary` varchar(20) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a new config.php file for database connection.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

3. HTML and PHP

Create two variable $row and $rowperpage.

$row – Current row position. Set value to 0.
$rowperpage – Number of rows to fetch from the MySQL table. Set value to 5.

Count the total number of rows in employee table and store it in a $allcount variable. Fetch records from the employee using $row and $rowperpage value.

Loop on the result and create <table> rows.

Create <form> element which has pagination control –

Two hidden elements to store $row and $allcount values.
Previous and Next button for navigation.
A <select> element to change number of row fetch at a time by changing $rowperpage value on <form> submit.
Create an array to make <option> list.

Event Handling

Next Button

Storing $_POST['row'] and $_POST['allcount'] in the variables and creating another variable $val which store the sum of $row and $rowperpage.

When $val value is less than $allcount then initializing $row with $val.

Previous Button

Subtract $_POST['row'] with $rowperpage and check if it is less than 0 or not. If it is then initialized $row with 0.

Row selection From Dropdown

Change value of $rowperpage with $_POST['num_rows'] when the form submitted.

Completed Code

<!doctype html>
<html>
    <head>
        <title>Change number of records displayed in Pagination using PHP</title>
        <link href="style.css" type="text/css" rel="stylesheet">
        <script src="jquery-3.3.1.min.js" type="text/javascript"></script>
        <?php
            include("config.php");

            $row = 0;

            // number of rows per page
            $rowperpage = 5;
            if(isset($_POST['num_rows'])){
                $rowperpage = $_POST['num_rows'];
            }

            // Previous Button
            if(isset($_POST['but_prev'])){
                $row = $_POST['row'];
                $row -= $rowperpage;
                if( $row < 0 ){
                    $row = 0;
                }
            }

            // Next Button
            if(isset($_POST['but_next'])){
                $row = $_POST['row'];
                $allcount = $_POST['allcount'];

                $val = $row + $rowperpage;
                if( $val < $allcount ){
                    $row = $val;
                }
            }
        ?>
    </head>
    <body>
    <div class="container">

        <table width="100%" id="emp_table" border="0">
            <tr class="tr_header">
                <th>S.no</th>
                <th>Name</th>
                <th>Salary</th>
            </tr>
            <?php
            // count total number of rows
            $sql = "SELECT COUNT(*) AS cntrows FROM employee";
            $result = mysqli_query($con,$sql);
            $fetchresult = mysqli_fetch_array($result);
            $allcount = $fetchresult['cntrows'];

            // selecting rows
            $sql = "SELECT * FROM employee  ORDER BY ID ASC limit $row,".$rowperpage;
            $result = mysqli_query($con,$sql);
            $sno = $row + 1;

            while($fetch = mysqli_fetch_array($result)){
                $name = $fetch['emp_name'];
                $salary = $fetch['salary'];
                ?>
                <tr>
                    <td align='center'><?php echo $sno; ?></td>
                    <td align='center'><?php echo $name; ?></td>
                    <td align='center'><?php echo $salary; ?></td>
                </tr>
            <?php
                $sno ++;
            }
            ?>
        </table>

        <!-- Pagination control -->
        <form method="post" action="" id="form">
            <div id="div_pagination">
                <input type="hidden" name="row" value="<?php echo $row; ?>">
                <input type="hidden" name="allcount" value="<?php echo $allcount; ?>">
                <input type="submit" class="button" name="but_prev" value="Previous">
                <input type="submit" class="button" name="but_next" value="Next">

                <!-- Number of rows -->
                <div class="divnum_rows">
                <span class="paginationtextfield">Number of rows:</span>&nbsp;
                <select id="num_rows" name="num_rows">
                    <?php
                    $numrows_arr = array("5","10","25","50","100","250");
                    foreach($numrows_arr as $nrow){
                        if(isset($_POST['num_rows']) && $_POST['num_rows'] == $nrow){
                            echo '<option value="'.$nrow.'" selected="selected">'.$nrow.'</option>';
                        }else{
                            echo '<option value="'.$nrow.'">'.$nrow.'</option>';
                        }
                    }
                    ?>
                </select>
                </div>
            </div>
        </form>

    </div>
    </body>
</html>

4. jQuery

Submit the <form> with submit() function when value changes in the <select id='num_rows'> element.

Completed Code

$(document).ready(function(){

    // Number of rows selection
    $("#num_rows").change(function(){

        // Submitting form
        $("#form").submit();

    });
});

5. Conclusion

It gives flexibility to the users to change the number of rows visible at a time on the page.

In the demonstration, I created a pagination layout where also create a dropdown element for modifying the value of the total number of rows to fetch.

If you found this tutorial helpful then don't forget to share.

Contents

1. Table structure

2. Configuration

3. HTML and PHP

Event Handling

4. jQuery

5. Conclusion