Column Sorting using PHP and MySQL

By Hardik Savani | August 22, 2018 | Category : PHP MySql


In this example, i will show you simple way to create table column sorting from database using php mysql. we will do it sorting column by clicking column header with php and mysql in table.

It is a very basic example for php starter to create column sort ascending descending order by click on column heading. It is very simple way to create without using ajax. so we can create this example in a single file.

in this example we will create "countries" with name and code column. Then after will create one index file and manage column sorting. You can simply see below preview of this example.

Preview:

Create countries table:

Here, we will create countries table by following sql query:

CREATE TABLE IF NOT EXISTS `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `code` varchar(244) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

Create index.php file:

Read Also: PHP - Jquery Chosen Ajax Autocomplete Example
<!DOCTYPE html>
<html>
<head>
  <title>Column Sorting using PHP and MySQL - ItSolutionStuff.com</title>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.3/css/bootstrap.min.css" />
</head>
<body>
 
<div class="container">
  <h1>Column Sorting using PHP and MySQL - ItSolutionStuff.com</h1>
<?php
     
    $hostName = "localhost";
    $username = "root";
    $password = "root";
    $dbname = "test";
 
    $mysqli = new mysqli($hostName, $username, $password, $dbname);
 
    $orderBy = !empty($_GET["orderby"]) ? $_GET["orderby"] : "name";
    $order = !empty($_GET["order"]) ? $_GET["order"] : "asc";
 
    $sql = "SELECT * FROM countries ORDER BY " . $orderBy . " " . $order;
  
    $result = $mysqli->query($sql);
  
    $nameOrder = "asc";
    $codeOrder = "asc";
  
    if($orderBy == "name" && $order == "asc") {
      $nameOrder = "desc";
    }
    if($orderBy == "code" && $order == "asc") {
      $codeOrder = "desc";
    }
?>
<table class="table table-bordered">
  <thead>
    <tr>
      <th><a href="?orderby=name&order=<?php echo $nameOrder; ?>">Name</a></th>
      <th><a href="?orderby=code&order=<?php echo $codeOrder; ?>">Code</a></th>
    </tr>
  </thead>
  <tbody>
  
    <?php
    while($row = mysqli_fetch_assoc($result)){
    ?>
      <tr>
        <td><?php echo $row['name']; ?></td>
        <td><?php echo $row['code']; ?></td>
      </tr>
    <?php
    }
    ?>
  
  </tbody>
</table>
  
</div>
  
</body>
</html>

Now you can simply run.

I hope it can help you....



Tags : MySql PHP Sort Sortable

Hardik Savani
My name is Hardik Savani. I'm a full-stack developer, entrepreneur and owner of Aatman Infotech. I live in India and I love to write tutorials and tips that can help to other artisan. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.
Follow Me: Github Twitter
***Do you want me hire for your Project Work? Then Contact US.
We are Recommending you: