How to make table columns sortable

Question

How do I sort the columns in the following table after clicking on the column headers. I would like Product Name and Price to ASCEND and DESCEND. I'm still a beginner in the PHP so please don't judge :) any help appreciated

<table style="width:50%" id="table1" align="center">
  <tr>
    <th>Product Name</a></th>
    <th>Price</th> 
    <th>Image</th>
    <th>Quantity</th>
    <th>Buy</th>
  </tr>
<?php 
$connection = mysqli_connect('localhost','root','','part2');
$query="SELECT * FROM products";
$result=mysqli_query($connection, $query);
 while ($row=mysqli_fetch_assoc($result)){
  echo "<tr>";
  echo "<td>";
  echo $row['ProductName'];
  echo "</td>";
  echo "<td>";
  echo "$".$row['Price'];
  echo "</td>";
  echo "<td>";
  echo ' <img src="./images/'.$row['Image'].'" style="width:50px;height:50px"/><br />';
  echo "</td>";
  echo "<td>";
  echo "<form method='post' action='buy.php'>";
  echo "<fieldset>";
  echo "<input type='number' name='quantity' style='width:30px'/>";
  echo "</fieldset>";
  echo "</form>";
  echo "</td>";
  echo "<td>";
  echo '<a href="buy.php?id='. $row['ProductID'].'">Buy</a>';
  echo "</td>";
  echo "</tr>";
}
  ?>
</table>

yes javascript is a better solution, this avoids another request on the server. You ca use datatable a jquery plugin : datatables.net — scraaappy
– scraaappy, Commented Jan 5, 2015 at 21:11

Master Yoda · Accepted Answer · 2015-01-05 21:33:44Z

2

Have a look at Datatables here. Its a JQuery plugin that does exactly what you need and more.

The link provided explains everything you need to do to make them work and provides a working demo on the site to play with and decide if its right for you.

I'll give you a quick guide to how to use it.

HTML

Include a reference to the Datatables JS file hosted on the content delivery network (CDN) between your head tags

<head> 
  <script src="cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js"></script>
</head>

There is also an css file you can include used like this

<head> 
  <script src="cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js"></script>
  <link  href="cdn.datatables.net/1.10.4/css/jquery.dataTables.min.css">
</head>

One final task, include JQuery in your site, a Javascript framework which reduces the amount of code required to perform tasks and allows for the use of pre-made third party plugins that are for the most freely available. Again you can use a CDN hosted copy of the JQuery framework.

<head> 
  <script src="cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js"></script>       // datatable javascript
  <link  href="cdn.datatables.net/1.10.4/css/jquery.dataTables.min.css">              // datatables css
  <script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>  //jquery
</head>

Create a js file with the extension .js so for example myJSFile.js and include a reference in your header like you have done before, if placed inside a parent folder dont forget the path.

<head> 
  <script src="cdn.datatables.net/1.10.4/js/jquery.dataTables.min.js"></script>       // datatable javascript
  <link  href="cdn.datatables.net/1.10.4/css/jquery.dataTables.min.css">              // datatables css
  <script src="//ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>  //jquery
  <script type="text/javascript" src="myJSFile.js"></script>
</head>

JQuery

Here is the easiest part, to make your regular table a datatable, simply use its class/id as the selector for the javascript

$(document).ready(function()
{
    $('#Table1').DataTable();
});

The .ready() function determines when your script will execute, i.e. when DOM heirarchy has been created.

answered Jan 5, 2015 at 21:33

Master Yoda

4,42212 gold badges46 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Master Yoda Over a year ago

@Beda Hey, yes the line $(document).ready(function() { $('#Table1').DataTable(); }); goes in your myJSFile.js

Master Yoda Over a year ago

@Beda Its worth mentioning that if you placed your myJSFile.js file inside a containing folder called "Scripts" for example then your path will be something like src="Scripts/myJSFile.js". Hope this has answered your question and if not i would be more than happy to help you more. All you have to do is ask :)

Beda Over a year ago

You helped me a lot, it seems to work as it should, thank you :)

ZenStein · Accepted Answer · 2015-01-05 21:40:49Z

Here is one way to do that. First Get your PHP data. You can include "SORT BY in your $query string.

<?php 
$connection = mysqli_connect('localhost','root','','part2');
$query="SELECT * FROM products";
$result=mysqli_query($connection, $query);
while ($row[$x]=mysqli_fetch_assoc($result)){
$x++;
}
?>

Create your table.

<table id="myTable" class="tablesorter"> 
<thead> 
<tr> 
   <th>Product Name</a></th>
   <th>Price</th> 
   <th>Image</th>
   <th>Quantity</th>
   <th>Buy</th> 
</tr> 
</thead> 
<tbody> 
<tr> 
    <td>Smith</td> 
    <td>John</td> 
    <td>[email protected]</td> 
    <td>$50.00</td> 
    <td>http://www.jsmith.com</td> 
</tr> 
<tr> 
    <td>Bach</td> 
    <td>Frank</td> 
    <td>[email protected]</td> 
    <td>$50.00</td> 
    <td>http://www.frank.com</td> 
</tr>
</tbody> 
</table>

You can insert your data from php like so:

<td> <?php  echo $row[0]['price']; ?></td>
<td> <?php  echo $row[0]['price']; ?></td>
<td> <?php  echo $row[0]['price']; ?></td>

Now import a library. Insert this in your head tags:

<script type="text/javascript" src="/path/to/jquery-latest.js"></script> 
<script type="text/javascript" src="/path/to/jquery.tablesorter.js"></script>

Include this at bottom of your page:

<script>
$(document).ready(function() 
    { 
        $("#myTable").tablesorter( {sortList: [[0,0], [1,0]]} ); 
    } 
); 
</script>

Got all this from tablesorter!

ivg · Accepted Answer · 2015-01-05 22:43:38Z

0

Few days ago I needed that and I found Datatable https://datatables.net/ Load the library and just

$(document).ready(function(){
    $('#myHtmlTableName').DataTable();
});

as simple as this. It will work like a charm I promise. Plus it will look neat and lots of other functionalities.

answered Jan 5, 2015 at 22:43

ivg

4494 silver badges7 bronze badges

Comments

Dirk · Accepted Answer · 2019-12-28 21:59:43Z

The best solution I found, is here: Table Sort with JavaScript

So, the following code is not by me but by Will Bontrager! You'll find a demo table on his homepage. Really nice!

Table headers are sortable in both directions (descending, ascending) by clicking the header title. You can choose which kind of data, you intend to sort (T - Text, N - Numbers, D - Dates). The example is well documented. So, it should be easy to adapt it to your taste.

<div style="overflow-x:auto;"> <!-- Make the table responsive -->
<table id="indextable" border="1" cellpadding="10" cellspacing="0" style="border-collapse:collapse;">
<thead>
<tr>
<th><a href="javascript:SortTable(0,'T');">Author</a></th>
<th><a href="javascript:SortTable(1,'T');">Title</a></th>
<th><a href="javascript:SortTable(2,'N');">Rating</a></th>
<th><a href="javascript:SortTable(3,'D','mdy');">Review Date</a></th>
</tr>
</thead>

Next you'll find the contents of the table as an example:

<tbody>
<table id="indextable" border="1" cellpadding="10" cellspacing="0" style="border-collapse:collapse;">
<thead>
<tr>
<th><a href="javascript:SortTable(0,'T');">Author</a></th>
<th><a href="javascript:SortTable(1,'T');">Title</a></th>
<th><a href="javascript:SortTable(2,'N');">Rating</a></th>
<th><a href="javascript:SortTable(3,'D','mdy');">Review Date</a></th>
</tr>
</thead>
<tbody>
<tr>
<td>Orson Scott Card</td>
<td>The Memory Of Earth</td>
<td align="center">2</td>
<td>10/14/11</td>
</tr>
<tr>
<td>Sarah-Kate Lynch</td>
<td>Blessed Are The Cheesemakers</td>
<td align="center">9</td>
<td>1-12-2011</td>
</tr>
<tr>
<td>John Irving</td>
<td>The cider house rules</td>
<td align="center">6</td>
<td>January 31, 11</td>
</tr>
<tr>
<td>Kate Atkinson</td>
<td>When will there be good news?</td>
<td align="center">7</td>
<td>Nov. 31, 2001</td>
</tr>
<tr>
<td>Kathy Hogan Trocheck</td>
<td>Every Crooked Nanny</td>
<td align="center">2</td>
<td>10.21.21</td>
</tr>
<tr>
<td>Stieg Larsson</td>
<td>The Girl With The Dragon Tattoo</td>
<td align="center">2</td>
<td>August 3, 2022</td>
</tr>
</tbody>
</table>
</div>

And finally, you need the script to get it working. There is no need to modify the script. You can add as many columns as you like. The script will handle it automatically.

<script type="text/javascript">
/* 
   Willmaster Table Sort
   Version 1.1
   August 17, 2016
   Updated GetDateSortingKey() to correctly sort two-digit months and days numbers with leading 0.
   Version 1.0, July 3, 2011

   Will Bontrager
   https://www.willmaster.com/
   Copyright 2011,2016 Will Bontrager Software, LLC

   This software is provided "AS IS," without 
   any warranty of any kind, without even any 
   implied warranty such as merchantability 
   or fitness for a particular purpose.
   Will Bontrager Software, LLC grants 
   you a royalty free license to use or 
   modify this software provided this 
   notice appears on all copies. 
*/
//
// One placed to customize - The id value of the table tag.

var TableIDvalue = "indextable";

//
//////////////////////////////////////
var TableLastSortedColumn = -1;
function SortTable() {
var sortColumn = parseInt(arguments[0]);
var type = arguments.length > 1 ? arguments[1] : 'T';
var dateformat = arguments.length > 2 ? arguments[2] : '';
var table = document.getElementById(TableIDvalue);
var tbody = table.getElementsByTagName("tbody")[0];
var rows = tbody.getElementsByTagName("tr");
var arrayOfRows = new Array();
type = type.toUpperCase();
dateformat = dateformat.toLowerCase();
for(var i=0, len=rows.length; i<len; i++) {
    arrayOfRows[i] = new Object;
    arrayOfRows[i].oldIndex = i;
    var celltext = rows[i].getElementsByTagName("td")[sortColumn].innerHTML.replace(/<[^>]*>/g,"");
    if( type=='D' ) { arrayOfRows[i].value = GetDateSortingKey(dateformat,celltext); }
    else {
        var re = type=="N" ? /[^\.\-\+\d]/g : /[^a-zA-Z0-9]/g;
        arrayOfRows[i].value = celltext.replace(re,"").substr(0,25).toLowerCase();
        }
    }
if (sortColumn == TableLastSortedColumn) { arrayOfRows.reverse(); }
else {
    TableLastSortedColumn = sortColumn;
    switch(type) {
        case "N" : arrayOfRows.sort(CompareRowOfNumbers); break;
        case "D" : arrayOfRows.sort(CompareRowOfNumbers); break;
        default  : arrayOfRows.sort(CompareRowOfText);
        }
    }
var newTableBody = document.createElement("tbody");
for(var i=0, len=arrayOfRows.length; i<len; i++) {
    newTableBody.appendChild(rows[arrayOfRows[i].oldIndex].cloneNode(true));
    }
table.replaceChild(newTableBody,tbody);
} // function SortTable()

function CompareRowOfText(a,b) {
var aval = a.value;
var bval = b.value;
return( aval == bval ? 0 : (aval > bval ? 1 : -1) );
} // function CompareRowOfText()

function CompareRowOfNumbers(a,b) {
var aval = /\d/.test(a.value) ? parseFloat(a.value) : 0;
var bval = /\d/.test(b.value) ? parseFloat(b.value) : 0;
return( aval == bval ? 0 : (aval > bval ? 1 : -1) );
} // function CompareRowOfNumbers()

function GetDateSortingKey(format,text) {
if( format.length < 1 ) { return ""; }
format = format.toLowerCase();
text = text.toLowerCase();
text = text.replace(/^[^a-z0-9]*/,"");
text = text.replace(/[^a-z0-9]*$/,"");
if( text.length < 1 ) { return ""; }
text = text.replace(/[^a-z0-9]+/g,",");
var date = text.split(",");
if( date.length < 3 ) { return ""; }
var d=0, m=0, y=0;
for( var i=0; i<3; i++ ) {
    var ts = format.substr(i,1);
    if( ts == "d" ) { d = date[i]; }
    else if( ts == "m" ) { m = date[i]; }
    else if( ts == "y" ) { y = date[i]; }
    }
d = d.replace(/^0/,"");
if( d < 10 ) { d = "0" + d; }
if( /[a-z]/.test(m) ) {
    m = m.substr(0,3);
    switch(m) {
        case "jan" : m = String(1); break;
        case "feb" : m = String(2); break;
        case "mar" : m = String(3); break;
        case "apr" : m = String(4); break;
        case "may" : m = String(5); break;
        case "jun" : m = String(6); break;
        case "jul" : m = String(7); break;
        case "aug" : m = String(8); break;
        case "sep" : m = String(9); break;
        case "oct" : m = String(10); break;
        case "nov" : m = String(11); break;
        case "dec" : m = String(12); break;
        default    : m = String(0);
        }
    }
m = m.replace(/^0/,"");
if( m < 10 ) { m = "0" + m; }
y = parseInt(y);
if( y < 100 ) { y = parseInt(y) + 2000; }
return "" + String(y) + "" + String(m) + "" + String(d) + "";
} // function GetDateSortingKey()
</script>

Collectives™ on Stack Overflow

How to make table columns sortable

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related