Table sort on header click in the pagination using PHP

In this tutorial, we learn How you can sort table data when table header gets clicked in Pagination using PHP.

If you don’t now What is pagination? and How to create it,  I recommend you to check out Pagination in PHP  tutorial. I explain it in short What pagination is ?.

What is pagination?

Pagination means where records are divided into multiple pages because of that all records not available at once to the user. The user sees records one by one and easily navigate to backward and forward directions. It is best when you have a long list of data.

It reduces page load time and provides the better user-friendly interface.

What we create?

In the demonstration, we have a pagination structure using PHP, which is sort when its header gets clicked.

Table sort on header click in the pagination using PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML and PHP
  4. CSS

 


 

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,
  `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 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

Using order_by in SQL query to sort data, the value of order_by is gets changed when column header gets clicked. Created a sortorder() function which takes fieldname as a parameter, it initializes order_by and sort and returns it.

Completed Code

<!doctype html>
<html>
<head>
    <link href="style.css" type="text/css" rel="stylesheet">
    <?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 = mysql_query($sql);
        $fetchresult = mysql_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 = mysql_query($sql);
        $sno = $row + 1;
        while($fetch = mysql_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

Adding some CSS for designing Pagination layout.

Completed Code

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

Related Post

Spread the love

Be First to Comment

Leave a Reply

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