Sortable table using column headers for sorting

Taanis

I know this can be done in ASP and with CSS, but is there anyway to do this using PHP?

I am a total newbie here so bear with me. I want to make a table with say 5 colums. I want to be able to have the user click on the column header or an icon in the header to sort by that column. if there is an icon it will be an up arrow for ASC and a down arrow for DESC sorts.

I am asking because I was not sure if I need to send this back to the server and get the data back sorted the new way from a script event from clicking on the arrow or can I just sort this with the data already sent over.

phlow

make links containing the column name and the sort-direction:

e.g. <a href=\"$PHP_SELF?sort=ASC&column=name\"><img src=\"sort_asc.gif\"></a>

and then build your mysql query containing these variables:

e.g. mysql_query("SELECT * FROM mytable ORDER BY $column $sort");

this should work.

BTW: you can't use the data you have already sent, because PHP is a SERVER-SIDE script language.

bbeban

Here's what I created using the suggestion above -- works fine:

$column = $_GET['column'];
$sort = $_GET['sort'];

mysql_query("SELECT * FROM mytable ORDER BY $column $sort");

<td align="center"><strong>Date Submitted</strong> <a href="<?php echo $_SERVER['PHP_SELF']; ?>?column=date&sort=DESC"><img src="/images/arrowdown.gif" border="0"></a> <a href="<?php echo $_SERVER['PHP_SELF']; ?>?column=date&sort=ASC"><img src="/images/arrowup.gif" border="0"></a></td>

etc. etc. for each column name and sort I wanted.

Enjoy.

kenle

But how r u going to remember the direction for each column?

So, for example if you have 4 columns: firstname,lastname,email,phone.

if you first sort firstname in ASC direction, and then do a sort email in ASC direction, how does it remember the firstname sort direction is still ASC direction?

In other word, how do i make each column remember which direction of sorting (ASC or DESC) does it last time sort?

laserlight

Add another variable.

bbeban

My example just sorts on that field, and does not retain the previous sort or allow you to sort at multiple levels.

So if you have firstname,lastname and the values are

firstname: Al, Bob, Cindy
lastname: Zebra, Franken, Gobi

Sorting by firstname gets you Al Zebra, Bob Franken, Cindy Gobi or Cindy Gobi, Bob Franken, Al Zebra
lastname gets you Bob Franken, Cindy Gobi, Al Zebra or Al Zebra, Cindy Gobi, Bob Franken

etc. etc....

It's not going to keep the first name sort and then the last name sort in that order. And why would you want it to?

kenle

laserlight,

can you explain to me more detail?

bbeban,
What i want is each column must have an independent sort direction and does not depend on each other. If the last time i sort firstname in ASC direction, the next time i sort it again, it suppose to be in DESC direction; does not matter if i sort lastname, email,phone in all direction before I go back to firstname.

bbeban

Oh, well what I posted doesn't do that. I'll defer to laserlight here since i don't know off the top of my head...

laserlight

We're working with a possible query of the form:
SELECT columns FROM tablename WHERE condition ORDER BY column1 ASC, column2 DESC, column3 DESC

A possible URI might then be:
filename.php?sort1=column1%20ASC&sort2=column2%20DESC&sort3=column3DESC

or even

filename.php?orderby=column1%20ASC%2C%20column2%20DESC%2C%20column3%20DESC

I'm not too sure of the security implications of that though.
Given the choice I would implement 1 name/value pair for each column and each associated ASC/DESC.

kenle

laserlight,

I am thinking about that method too 'cause it's the only way it can remember the state of each column direction. But when I look at some app like phpmyadmin for example, it only use 1 sort and 1 direction in the URL
For example: mydomain.com/test.php?sort=firstname&dir=ASC

If u guys have phpmyadmin running, try to look at the data table when select "Browse" option, you will see what i mean. It only show sort=<columnname>&dir=<dir> for each column name, and not use multiple sort1=<columnname1>&dir1=<dir1>&sort2=<column2>&dir2=<dir2>