Html Table Php Mysql Toggle Mysql Sorting Order Asc Desc On Column Header Click

EDIT: added solution from the accepted answer The hint I'm asking here is the smartest method for. Need to be said first: I kindly ask you to avoid suggesting databales and similar

Solution 1:

If you are already sending the sortby via GET, why wouldn't you send the ASC,DESC Option too, I mean, you could use 1 and 0 instead of the actual ASC/DESC and toggle it inside your code,for example:

$ascdesc = ($_GET['ad'])? '0' : '1';

And just add the var to the link

echo"<th>".'<a href="showdbtable.php?sortby=negozio&ad='.$ascdesc.'">' . "Negozio</th>";

And in you query, something like

$ascdesc = ($_GET['ad'])? 'asc' : 'desc';

Something very important here is that if you are accepting user input via GET, you have to sanitize the var to avoid SQL injections, don't forget this.

UPDATE:

Possible implementation with your own code:

$sortby = (isset($_GET['sortby']))? $_GET['sortby'] : "id_ord";
$ascdesc = ($_GET['ad']=='asc')? 'ASC' : 'DESC';

The Query:

$query = "SELECT id_ord, fornitore, negozio, data_insord, data_prevcons 
          FROM tesord 
          ORDER BY ".$sortby." ".$ascdesc." 
          LIMIT :from_record_num, :records_per_page";

The link:

echo "<th>".'<ahref="showdbtable.php?sortby=fornitore&ad=<?=(($_GET['ad']=='asc')? 'desc' : 'asc';)?>">' . "Fornitore</th>";

And if you need to add the page, just add the current page and change sort, you can add a var also to the link

echo "<th>".'<ahref="showdbtable.php?sortby=fornitore&ad=<?phpecho (($_GET['ad']=='asc')? 'desc' : 'asc';)?>&page=<?phpecho$_GET['page]?>">' . "Fornitore</th>";

There are many other ways to handle the pagination and sorting, but I think that, without getting into a lot of trouble, this could be a way, however, about the security, you can use mysql_real_escape

You could also leave everything to javascript/jQuery by implementing something like this

Hope this can give you a better understanding, happy coding.

Solution 2:

Before seeing these postings, I came up with a simple ASC/DESC sorter that added only two lines of code:

$SortType = (isset($_GET['SortType']) && $_GET['SortType'] === "DESC") ? "ASC" : "DESC";
$SortOrder = "ORDER BY `StorageArea` $SortType";

then I appended this to the query (the dots are indicating the earlier part of the query that is not shown here):

...$SortOrder";

and the link has:

<ahref="/index.php?SortOrder=StorageArea&SortType=<?=$SortType;?>"id="Sorter_StorageArea">Storage</a>