Table sort on header click in the pagination using PHP

Add Comment , Edit

If you want to add records sorting with header click on your existing pagination code then you need to write some extra code for it.
This improves the user experience while traversing on the long list of paginated records.
The user can easily sort a column and view the list e.g. sorting employee list by its salary, age, etc.

1. Table structure

In this tutorial, I am using employee table in the example.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  `salary` varchar(20) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `city` varchar(80) NOT NULL,
  `email` varchar(100) 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 establishing the 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

Using order_by in SQL query to sort data, the value of order_by is gets changed when column header gets clicked.

Create a sortorder() function which takes fieldname as a parameter, it initializes order_by and sort and returns it.

Completed Code
 

 

<!doctype html>
<html>
<head>
    <?php
    include("config.php");

    $rowperpage = 5;
    $row = 0;

    // 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;
        }
    }

    // generating orderby and sort url for table header
    function sortorder($fieldname){
        $sorturl = "?order_by=".$fieldname."&sort=";
        $sorttype = "asc";
        if(isset($_GET['order_by']) && $_GET['order_by'] == $fieldname){
            if(isset($_GET['sort']) && $_GET['sort'] == "asc"){
                $sorttype = "asc";
            }
        }
        $sorturl .= $sorttype;
        return $sorturl;
    }
    ?>
</head>
<body>
<div id="content">
    <table width="100%" id="emp_table" border="0">
        <tr class="tr_header">
            <th>S.no</th>
            <th ><a href="<?php echo sortorder('emp_name'); ?>" class="sort">Name</a></th>
            <th ><a href="<?php echo sortorder('salary'); ?>" class="sort">Salary</a></th>
            <th ><a href="<?php echo sortorder('gender'); ?>" class="sort">Gender</a></th>
            <th ><a href="<?php echo sortorder('city'); ?>" class="sort">City</a></th>
            <th ><a href="<?php echo sortorder('email'); ?>" class="sort">Email</a></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
        $orderby = " ORDER BY id desc ";
        if(isset($_GET['order_by']) && isset($_GET['sort'])){
            $orderby = ' order by '.$_GET['order_by'].' '.$_GET['sort'];
        }
        
        // fetch rows
        $sql = "SELECT * FROM employee ".$orderby." limit $row,".$rowperpage;
        $result = mysqli_query($con,$sql);
        $sno = $row + 1;
        while($fetch = mysqli_fetch_array($result)){
            $name = $fetch['emp_name'];
            $salary = $fetch['salary'];
            $gender = $fetch['gender'];
            $city = $fetch['city'];
            $email = $fetch['email'];
            ?>
            <tr>
                <td align='center'><?php echo $sno; ?></td>
                <td align='center'><?php echo $name; ?></td>
                <td align='center'><?php echo $salary; ?></td>
                <td align='center'><?php echo $gender; ?></td>
                <td align='center'><?php echo $city; ?></td>
                <td align='center'><?php echo $email; ?></td>
            </tr>
            <?php
            $sno ++;
        }
        ?>
    </table>
    <form method="post" action="">
        <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">
        </div>
    </form>
</div>
</body>
</html>
 

4. CSS

#content{
    border:1px solid darkgrey;
    border-radius:3px;
    padding:5px;
    width: 75%;
    margin: 0 auto;
}

/* Table */
#emp_table {
    border:3px solid lavender;
    border-radius:3px;
}

/* Table header */

.tr_header th a{
    color: white;
    text-decoration: none;
}

.tr_header{
    background-color:dodgerblue;
}

.tr_header th{
    color:white;
    padding:10px 0px;
    letter-spacing: 1px;
}

/* Table rows and columns */
#emp_table td{
    padding:10px;
}
#emp_table tr:nth-child(even){
    background-color:lavender;
    color:black;
}

/* */
#div_pagination{
    width:100%;
    margin-top:5px;
    text-align:center;
}

.button{
    border-radius:3px;
    border:0px;
    background-color:mediumpurple;
    color:white;
    padding:10px 20px;
    letter-spacing: 1px;
}
 

5. Conclusion

In the demonstration, I used anchor element to enable header click sorting on the pagination. Where use column name and sorting order for creating the link.

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

 

 
 
 
 

CONVERSATION

0 Comments:

Post a Comment

Subscribe to: Post Comments ( Atom )
Back
to top