Change number of rows show in the Pagination using PHP

In this tutorial, we will learn How to change the number of records show in Pagination.

What is Pagination?

Pagination is a way to displaying the number of records, where records are divided into multiple pages. All records are not visible to the user.

For example, you have more than 500 records, and you want to display all of it within the web page. For this simplest way is to display all records within the page, it is simple but its have a drawback when you try to show all records in the single page it increase your page load time and it is not very user-friendly.

The user needs to scroll up and down to see all records.

For solving this kind of problem you can divide records into multiple pages, by this, you don’t need to fetch all records and user easily navigate through records.

Adding?

With the Pagination we will be adding one more feature, by this feature user can change the number of records should visible on the screen. It gives more flexibility to the users.

What are we creating?

In the tutorial, demonstration we will create the Table structure and Next, Previous buttons by which we perform Pagination. And creating a Dropdown by which we can change the number of rows to display in the Table structure.

Change number of rows show in the Pagination using PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. jQuery
  5. Conclusion

 


 

1. Table structure

We are only using one Table in the tutorial example. employee table.

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 PHP file (config.php). This file contains database connection. In this tutorial, we are using a tutorial database.

Completed Code

<?php

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

$con = mysql_connect($host, $user, $password) or die("Unable to connect");

// selecting database
$db = mysql_select_db($dbname, $con) or die("Database not found");

 

3. HTML and PHP

Within our main file index.php we are creating our pagination structure and displaying the data from the employee Table.

Explanation – HTML structure

First, setting variables $row to 0 and $rowperpage to 5 we use it for data selection in SELECT query. We change both variables value when an event occurs on Buttons and Dropdown. I explain it in Event Handling Explanation.

Creating a Table structure with id=”emp_table”. Its header contains 3 rows with S.no, Name, and Salary text. After creating header row, we perform our data selection process.

Selecting the total number of rows and storing it in $allcount variable and preparing the SELECT query for selecting data from employee Table. In the SELECT query, using $row and $rowperpage variable for setting LIMIT.

Looping over, selected records and creating new rows from it.

After creating our Table structure, we need to create the form structure which contains Next and Previous Buttons, and a Dropdown by which will change a number of records would display on Table. Creating options for Dropdown by looping over the $numrows_arr array.

And also creating two hidden fields which hold total number of rows and current row position,

Explanation – Event Handling

  • Next Button

Storing $_POST['row'] and $_POST['allcount'] to 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

Storing only one $_POST value which is $_POST['row']. After initializing $row variable, we subtract it by $rowperpage and checking it is less than 0 or not if it is then initializing $row with 0.

  • Row selection From Dropdown

When option selected from the drop-down, then submit the form using jQuery. When the form submitted successfully, then changing $rowperpage value with Dropdown $_POST value.

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-1.12.0.min.js" type="text/javascript"></script>
        <script src="script.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 = mysql_query($sql);
            $fetchresult = mysql_fetch_array($result);
            $allcount = $fetchresult['cntrows'];

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

            while($fetch = mysql_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

Create a new script file name it script.js.

We are submitting the form using jQuery when the change event occurs in the dropdown with id=”num_rows”. For submitting the form we are using submit() function.

Completed Code

$(document).ready(function(){

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

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

    });
});

 

5. Conclusion

Created a Pagination structure using PHP  which also contain a Dropdown for changing the total number of rows display on the Table structure.

Options are created by looping over $numrows_arr Array, you can modify the values of it with your values.

Used jQuery for submitting the form using form() function when Dropdown option selected and changing $rowperpage variable value with $_POST value.

Spread the love

Related Post

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *