Amesey Amesey - 1 month ago 15
MySQL Question

Sort tablesaw table onload not just on click

I am using Tablesaw which sorts data when you click table headings, however the script doesn't allow you to make a specific column sortable when the page loads.

So my question how do I make the table sortable by whatever column I want?

Tablesaw Demo: http://filamentgroup.github.io/tablesaw/demo/sort.html

Tablesaw https://www.filamentgroup.com/lab/tablesaw.html

We're also calling in our data from a database... this is our code...

<?php

// Function to create the HTML table using the data stored in the database.
function displayTable($table) {
include "connection.php";
$sql = "SELECT * FROM $table";
$result = mysqli_query($conn, $sql)or die(mysql_error());

if (!mysqli_num_rows($result)==0) {
print "<table id='carTable' class='tablesaw paginated' data-tablesaw-sortable data-tablesaw-sortable-switch><thead>";
print "<tr><th scope='col' data-tablesaw-sortable-col>Model</th><th scope='col' data-tablesaw-sortable-col>Pack</th><th scope='col' data-tablesaw-sortable-col>Colour</th><th scope='col' data-tablesaw-sortable-col>Registration</th><th scope='col' data-tablesaw-sortable-col data-sortable-numeric data-tablesaw-sortable-default-col>Price When New</th><th scope='col' data-tablesaw-sortable-col data-sortable-numeric>New Price</th><th scope='col' data-tablesaw-sortable-col data-sortable-numeric><span class='red'>Saving</span></th><th scope='col' data-tablesaw-sortable-col>Retail Centre</th></tr></thead><tbody>";

while($row = mysqli_fetch_array($result)){
$variant = $row['Variant'];
$model = $row['Model'];
$pack = $row['Pack'];
$colour = $row['Colour'];
$regNo = $row['RegNo'];
$priceWhenNew = $row['PriceWhenNew'];
$nearlyNewPrice = $row['NearlyNewPrice'];
$saving = $row['Saving'];
$retailCentre = $row['RetailCentre'];


print "<tr><td>".$model."</td><td>".$pack."</td><td>".$colour."</td><td class='regno'>".$regNo."</td><td>&#163;".number_format($priceWhenNew)."</td><td>&#163;".number_format($nearlyNewPrice)."</td><td class='red'>&#163;".number_format($saving)."</td><td class='retailer'>".$retailCentre."</td><td><a href='#' class='wide-btn contact-btn'>CONTACT US FOR MORE INFO</a></td></tr>";

} // End while loop
print "</tbody></table>";
}
else {
print "<h1>No results found.</h1>";
}
}
?>

Answer

You could start by getting the results from the database in the correct order. Let's say you want the table to be sorted by "Model" by default. Your SQL-statement will need to be:

$sql = "SELECT * FROM $table ORDER BY Model ASC";

Furthermore, after outputting the results in the correct order to begin with, according to the documentation at https://github.com/filamentgroup/tablesaw you need to put data-tablesaw-sortable-default-col in the column you want to be sorted by default.

So, in your example this would become:

print "<tr>
<th scope='col' data-tablesaw-sortable-col data-tablesaw-sortable-default-col>Model</th>
<th scope='col' data-tablesaw-sortable-col>Pack</th>
<th scope='col' data-tablesaw-sortable-col>Colour</th>
<th scope='col' data-tablesaw-sortable-col>Registration</th>
<th scope='col' data-tablesaw-sortable-col data-sortable-numeric>Price When New</th>
<th scope='col' data-tablesaw-sortable-col data-sortable-numeric>New Price</th>
<th scope='col' data-tablesaw-sortable-col data-sortable-numeric><span class='red'>Saving</span></th>
<th scope='col' data-tablesaw-sortable-col>Retail Centre</th>
</tr></thead><tbody>";