PHP MySQL Column Sorting Example Tutorial
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:
<!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....
