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 :

We are Recommending you

Popular Posts

Categories

Latest Posts