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'];

?>

When I click on the down arrow for id it doesn't sort. The page doesn't seem to reload. — kebrown4
– kebrown4, Commented Mar 20, 2015 at 13:32
So when you click the arrow it doesnt take you to userlist.php?x=1? I also noticed you are creating the $x variable at the bottom of your script, which means any references to $x wont be read at the top. — chriz
– chriz, Commented Mar 20, 2015 at 13:35
I initialized it at the top for $x =1. When I click it the decending arrow it just doesn't do anything no page refresh or anything. — kebrown4
– kebrown4, Commented Mar 20, 2015 at 13:38
That is very strange that there is no refresh what-so-ever. Is there anything potentially blocking the href request? — chriz
– chriz, Commented Mar 20, 2015 at 13:41

MBaas · Accepted Answer · 2015-03-20 13:47:59Z

1

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...

answered Mar 20, 2015 at 13:47

MBaas

7,5318 gold badges50 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kebrown4 Over a year ago

I did make that change but the page still doesn't refresh. For the moment I was just trying to see if I could get it to work this way. I can make it more efficient once I having something working.

MBaas Over a year ago

Can you examine the HTML that the browser actually shows for one of these links? And just to be really sure: the PHP you posted is userlist.php, right? ;-)

kebrown4 Over a year ago

I am able to click on the down arrow for sorting ID descending in a new tab and it sorts. However I just can't get it to do so on the same page.

MBaas Over a year ago

Perhaps some sort of "browser-optimization"? Trying to avoid calling same page again? Can you try and manually add ?x=2 to the URL and then click on ID to get it sorted?

MBaas Over a year ago

Ok, and when you have that x=2-display, it does not respond to your clicking to change sort-order? That's very bizarr, especially because you confirmed it opened in a new tab. I'm afraid I'm running out of ideas :(( But I will observe the discussion to see what the issue was! If I were you, I would ignore that for the time being and just continue development with the other sorts - and maybe with all these changes you will "accidentally" touch the silly bit which caused all this pain and the issue will magically disappear ;-)

meteor · Accepted Answer · 2015-03-20 13:34:16Z

0

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(); } );

answered Mar 20, 2015 at 13:34

meteor

2,5784 gold badges40 silver badges59 bronze badges

2 Comments

kebrown4 Over a year ago

I know but I was told to do it this way first. I actually first tried to use jQuery and the way I have my table set up was squaring the number of items in the table everytime a header was selected.

kebrown4 Over a year ago

I had already tried to use that script and it did not work for me.

Misunderstood · Accepted Answer · 2015-03-20 13:52:36Z

0

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]";

edited Mar 20, 2015 at 13:52

answered Mar 20, 2015 at 13:40

Misunderstood

5,6791 gold badge20 silver badges25 bronze badges

2 Comments

kebrown4 Over a year ago

For some reason its not using my $_GET['x'] so its never going into my if/else statement. But I also need to sort the other columns too like name, username, email, ect.

Misunderstood Over a year ago

Use the value you want to sort by as the $registrations key. but I think you will get the $x working and the SQL will get it correct.

Collectives™ on Stack Overflow

How to sort items in a HTML table using SQL?

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related