Sort columns by clicking headers in HTML/PHP/SQL

Question

I have my code working to sort by a specific column once, but I can't get it to cycle through asc, desc, and default. Right now I can click the header once and it sorts it desc, each click after that doesn't do anything though. I want the first click to sort desc, then the next click to sort asc, then the next click to go back to the default (order by ID asc) and just cycle through those. Here's what I have so far, can anyone help improve my sorting? I don't fully understand how this works, but it seems I'm unable to set $sort to desc. I don't have any idea how to reset it to the default on the third click though.

$field='ID';
$sort='ASC';
$sortOrder='';
$sortISBN='ISBN';
$sortAuthor='Author';
$sortTitle='Title';
if(isset($_GET['sorting']))
{
    if($_GET['sorting']=='ASC')
    {
        $sort='DESC';
        $sortOrder=' &#8595';
    }
    else 
    {
        $sort='ASC';
        $sortOrder=' &#8593';
    }
}
if(isset($_GET['field']))
{
    if($_GET['field']=='ISBN')
    {
        $field='ISBN';
        $sortISBN='ISBN ' . $sortOrder;
    }
    elseif($_GET['field']=='Author')
    {
        $field='Author';
        $sortAuthor='Author ' . $sortOrder;
    }
    elseif($_GET['field']=='Title')
    {
        $field='Title';
        $sortTitle='Title ' . $sortOrder;
    }
}

Here's the other relevant part of the code:

<?php
$query=mysql_query("select * from Books order by $field $sort")  or die(mysql_error());
echo'<table border="1">';
?>
<tr><th colspan="5">Your book list</th>
    <td>
        <form name="New" action="new.php" method="POST">
            <input type="submit" name="New" value="New" title="Add a new entry"/>
            </form>
    </td>
</tr>
<tr>
    <th></th>
    <th><a href="index.php?sorting='.$sort.&field=ISBN"><?php echo $sortISBN; ?></th>
    <th><a href="index.php?sorting='.$sort.&field=Author"><?php echo $sortAuthor; ?></th>
    <th><a href="index.php?sorting='.$sort.&field=Title"><?php echo $sortTitle; ?></th>
    <th></th><th></th>
</tr>
<?php

Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jan 15 '16 at 18:41
I know about mysql being deprecated, I just haven't gotten around to replacing them. That's the next step I guess. Is it really a big deal for this project that won't go anywhere? — Deej, Jan 15 '16 at 18:58
I'm trying to avoid javascript as well to keep this simpler for me. Trying to learn more about PHP and SQL is hard enough without throwing in another language or two. — Deej, Jan 15 '16 at 18:59
Have you echo'd out the query each time you have clicked? You'll likely find your issue by observing what happens to the query itself. — Jay Blanchard, Jan 15 '16 at 19:02
The hint on datatables is a good one. You need barely any code to make it work. And the sort and Filter-functions aswell as pagination are built in out of the box. You should consider taking a look at it. — bloodstix, Jan 15 '16 at 19:55

score 1 · Accepted Answer · edited May 23 '17 at 12:04

The problem is because of the following three lines,

<th><a href="index.php?sorting='.$sort.&field=ISBN"><?php echo $sortISBN; ?></th>
<th><a href="index.php?sorting='.$sort.&field=Author"><?php echo $sortAuthor; ?></th>
<th><a href="index.php?sorting='.$sort.&field=Title"><?php echo $sortTitle; ?></th>

That's not how you should use a PHP variable inside HTML. The above lines should be like this:

// your code

<th><a href="index.php?sorting=<?php echo $sort ?>&field=ISBN"><?php echo $sortISBN; ?></th>
<th><a href="index.php?sorting=<?php echo $sort ?>&field=Author"><?php echo $sortAuthor; ?></th>
<th><a href="index.php?sorting=<?php echo $sort ?>&field=Title"><?php echo $sortTitle; ?></th>

// your code

Sidenote: Please don't use mysql_ database extensions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or PDO extension instead. And this is why you shouldn't use mysql_* functions.

That's perfect. I thought the code looked weird, but I just copied it from someone else's example to try it out. (and yes, getting rid of the mysql_* is my next step. Thanks for that link) — Deej, Jan 18 '16 at 15:43

Sort columns by clicking headers in HTML/PHP/SQL

1 Answers1