Sort Table column using PHP MySQL

Hi Friends in this lesson we are going to learn how to sort mysql data using php and html table..
when user click on table header column mysql data will displayed order by asc or desc according to column name.
just follow the below example : how to sort mysql column using php

Database:
Database Name: codenair
Table Name: product
Table structure for table `product`

CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price` int(10) NOT NULL,
  `code` varchar(20) NOT NULL,
  PRIMARY KEY(ID)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

Dumping data for table `product`

INSERT INTO `product` (`id`, `name`, `price`) VALUES
(1, 'ASUS VivoBook', 500),
(2, 'HP Power Gamming', 1230),
(3, 'Nokia 1020', 460),
(4, 'Sony XA Ultra', 340),
(5, 'Intel Sky Lake 4.3GHz', 340),
(6, 'ASUS BM-86 6 Generation', 543);

PHP Codes to Sorting MySQL Column:

<?php
  $conn=new mysqli('localhost','root','','codenair');
  $orderby='ID';
  $order='ASC';
if(!empty($_GET['orderby'])){
   $orderby=$_GET['orderby'];
}
if(!empty($_GET['order'])){
   $order=$_GET['order'];
}
  $name='asc';
  $price='asc';
  $id='desc';
if($orderby=='name' and $order=='asc'){
  $name='desc';
}
if($orderby=='price' and $order=='asc'){
 $price='desc';
}
if($orderby=='id' and $order=='desc'){
      $id='asc';
}
$result=mysqli_query($conn,"SELECT*FROM product ORDER BY $orderby $order");
 
?>

PHP MySQL Data Table Full Codes: index.php

<?php
  $conn=new mysqli('localhost','root','','codenair');
  $orderby='ID';
  $order='ASC';
if(!empty($_GET['orderby'])){
   $orderby=$_GET['orderby'];
}
if(!empty($_GET['order'])){
   $order=$_GET['order'];
}
  $name='asc';
  $price='asc';
  $id='desc';
if($orderby=='name' and $order=='asc'){
  $name='desc';
}
if($orderby=='price' and $order=='asc'){
 $price='desc';
}
if($orderby=='id' and $order=='desc'){
      $id='asc';
}
$result=mysqli_query($conn,"SELECT*FROM product ORDER BY $orderby $order");
 
?>
 
<html>
<head>
<title>PHP MySQL Data table Sorting --CodeNair.com</title>
<link rel="stylesheet" type="text/css" href="style.css"/>
</head>
<table border="1" cellspacing="0" cellpadding="2px">
  <tr>
    <th><a href="?orderby=id&order=<?php echo $id;?>">ID</a></th>
 <th><a href="?orderby=name&order=<?php echo $name;?>">Name</a></th>
 <th><a href="?orderby=price&order=<?php echo $price;?>">Price</a></th>
  </tr>
  <?php
  while($row=$result->fetch_array()){?>
  <tr>
    <td><?php echo $row['id'];?></td>
 <td><?php echo $row['name'];?></td>
 <td>$ <?php echo number_format($row['price'],2);?></td>
  </tr>
  <?php }?>
</table>
</html>

style.css to style MySQL Data Sort Table

table {
    border-collapse: collapse;
    width: 35%;
font-family:verdana;
}
 
th, td {
 
    padding: 8px;
}
 
tr:nth-child(even){background-color: #f2f2f2}
 
th {
    background-color: #4CAF50;
    color: white;
}

That's it Friends How to sort mysql table data using php..
Thank you for reading...

Latest

Sunday, February 25, 2018

Sort Table column using PHP MySQL