Amesey Amesey - 1 year ago 66
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:


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


// 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 Source

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 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>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download