How to sort items in a HTML table using SQL?

Question

I am trying to use $_GET to take a parameter so that if a table heading it selected it will go through my if else statements and sort accordingly.

However, I'm not having much success. I have it set up to sort by ID in ascending order first and I'm just testing by clicking for ID in descending order. Any suggestions would be useful.

 if(!isset($_SESSION['userid']))
 {
     header("Location: login.php");
     exit();
 }

 try
 {

     $sql = 'SELECT * FROM members ORDER by ID';
     $result = $pdo->query($sql);


       if($x == 2)
       {
           $sql = 'SELECT * FROM members ORDER by ID DESC';
           $result = $pdo->query($sql);
       }



}
catch (PDOException $e)
{
     echo 'Error fetching results: ' . $e->getMessage();
     exit();
}

  while ($row = $result->fetch())
  {
       $registrations[] = array(
       'ID' => $row['ID'],
       'email' => $row['email'],
       'fname' => $row['fname'],
       'mi' => $row['mi'],
       'institution' => $row['institution'],
       'lname' => $row['lname'],
       'uname' => $row['uname']);

}



echo '<table>';
echo '<thead><tr><th>Options</th><th>ID<a href="userlist.php?x=1">
     &utrif;</a> <a href="userlist.php?x=2">&dtrif;</a></th><th>Name 
     <ahref="userlist.php?x=3">&utrif;</a><a href="userlist.php?x=4"> &dtrif;
     </a></th><th>Institution <a href="userlist.php?x=5">&utrif;</a> 
     <a href="userlist.php?x=6">&dtrif;</a></th><th>Username 
     <a href="userlist.php?x=7">&utrif;</a> 
     <a href="userlist.php?x=8">&dtrif;</a></th><th>Email 
     <a href="userlist.php?x=9">&utrif;</a> 
     <a href="userlist.php?x=10">&dtrif;</a></th></tr></thead>';
 if($_SESSION['status'] == 1)
 {
     foreach ($registrations as $user)
     {
         echo '<tbody><tr><td><a href="userdetails.php?x=' .$user['ID']    
         .'">VIEW</a> </td>
        <td>'.$user['ID'].'</td>
        <td>'.$user['lname'].", ". $user['fname']." ". $user['mi'].".".'</td>
        <td>'.$user['institution'].'</td>
        <td>'.$user['uname'].'</td>
        <td>'.$user['email'].'</td> </tr> </tbody>';

     }

 }

 else
 {

    echo  '><tr><td><a href="userdetails.php?x='       
   .$_SESSION['userid'].'">VIEW</a></td> <td>'. $_SESSION['userid'].' </td>   
   <td>'.$_SESSION['lname'].", ". $_SESSION['fname']." ". 
   $_SESSION['mi'].".".'</td><td>'.$_SESSION['institution'].'</td>
   <td>'.$_SESSION['uname'].'</td><td>'.$_SESSION['email'].'</td></tr>';


}
echo '</table> ';

$x = $_GET['x'];

?>

Answer 1

It appears it should work. You also can sort the $registrations
Add the ID to the $registrations key:

   $registrations[$row['ID']] = array(
   'ID' => $row['ID'],
   'email' => $row['email'],
   'fname' => $row['fname'],
   'mi' => $row['mi'],
   'institution' => $row['institution'],
   'lname' => $row['lname'],
   'uname' => $row['uname']);

Then sort:

if($x == 2){
  krsort($registrations); // reverse sort DESC
}
else{
  ksort($registrations);
}

You may want to try reverse logic on the $x sort:

   if($x != 2)
   {
     $sql = 'SELECT * FROM members ORDER by ID DESC';
   }
   else{
      $sql = 'SELECT * FROM members ORDER by ID ASC';
   }
   $result = $pdo->query($sql);

You may have a type problem:

if($x == '2')

or

if (intval($x) == 2)

Make the default sort zero and type the get:

$x = intval($_GET['x']);

If there is no x value in the GET this will make it zero and eliminate typing issues.

Then for your various sorts:

$sort[0] = 'ORDER BY `ID` ASC';
$sort[2] = 'ORDER BY `ID` DESC';
$sort[3] = ORDER BY `???`

$sql = "SELECT * FROM members $sort[$x]";

Answer 2

You said that you initialised $x=1. But unless you do $x = (int)$_GET["x"], this value will remain unchanged and so you're building the same page again....

Besides, I hope you won't be building a SELECT-Structure there with varying SQL-Statements - that would be a lot of unneccessary code-dusplication. I'd have an Array of keys for the various values of $x and then take the required key from there...

Answer 3

The simplest way to sort a HTML table would be to use something like jquery tablesorter. More information about it with example can be found @ http://tablesorter.com/docs/

You need to import these script files

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

Then add a class as follows with a table id

<table id="myTable" class="tablesorter">

And then trigger the table sort function in your script

$(document).ready(function() { $("#myTable").tablesorter(); } );