[RESOLVED] How to Sort By ?

n0uhad

Hello there, I'm having some problem thinking of a way to sort my tables by their respective fields when clicked on.

At the moment I have my code set up as so :

echo ' <tr>
    <th>ID</th>
    <th>Name</th>
    <th>Director</th>
	<th>Actors</th>
	<th>Release Date</th>
	<th>Plot</th>  

	<th><a href="my_test.php?sortby=rating"> Rating</a></th>			
  </tr>';

This is outside the while loop that prints out the fields from a SELECT * FROM statement :

a) How do I code my program so that when the Rating table header is clicked, the table sorts itself by Rating (Ascendingly or Descendingly) ?

I had this in mind :

if(isset($_GET['sortby']))
{
		$sortByThis = $_GET['sortby'];
		$querystring = "SELECT * FROM moviedb ORDER BY $sortByThis";
..[I]display fields in a while loop[/I]

The only problem with that was, when the Rating header was clicked, It displayed the sorted Fields at the bottom of the already displayed 'SELECT * FROM moviedb' Table, duplicating the data.

Any ideas/suggestions?

sneakyimp

you are on the right track, but it sounds like your php isn't quite right. you should set up your php file sort of like this:

// 1 look at the GET vars to see what the sort param is
// NOTE - you should make sure the sort param is only one of the permitted values
// or some hacker might inject stuff into your sql
if (empty($_GET['sort'])) {
  $sortby = 'id';
} else {
  $ok_sorts = array('name', 'director', 'rank');
  if (in_array($_GET['sort'], $ok_sorts)) {
    $sortby = $_GET['sort'];
  } else {
    die('invalid sort parameter.  stop hacking me!');
  }
}

// 2 - create your query:
$sql = "SELECT foo, bar FROM tblFooBar WHERE ladi='da' ORDER BY " . $sortby;
$result = mysql_query($sql)
  or die('query failed');

// 3 - ouput the results

n0uhad

Thanks for the reply, It gave me a better idea on how to go about sorting this. 🙂
One more question -- How can i further change this coding so that If clicked for the second time, the table is sorted ascendingly or descendingly?

E.g. by default, table is sorted by ID ascendingly. If ID is clicked, table is sorted descendingly. Clicked again, ID is reverted to ascending order

sneakyimp

NOTE that the more sorting parameters you have to maintain, the more complicated your code is going to be.

the basic idea again is that you check the GET vars to see if there is any sort parameter. in this case, you'll need 'sort' but also 'direction' or whatever you want to call it. when the page gets loaded, take a look at both sort parameters.

then for each sortable column you are going to have to dynamically create the URL depending on what the current sorting key is. for instance if the url is:

http://mydomain.com/script.php?sort=rank&d=asc

then your 'rank' link will link to this:
http://mydomain.com/script.php?sort=rank&d=desc

in other words, your sort links must change depending on how the current results are sorted.

n0uhad

Ahh i get it 🙂 Thanks again

Thread resolved