• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 802
  • Last Modified:

how to sort column

I have a table where i call record from database.
after i get the record i want all column can be sortable.
that mean when you click on the table column that particular column will be sorted.

How to do .
  • 5
  • 4
  • 2
  • +2
1 Solution
b0lsc0ttIT ManagerCommented:

I like to do this by reloading the page.  You have the column you want to sort on call the same php script that made the table in the first place.  The column headings though would call the php page with a query string value.  This would make it so the PHP script could sort based on what column was clicked (i.e. which link reloaded the page).  Your PHP that makes the SQL would need to look at the query string and either use a default sort (if no value is set) or use the field set by the value.

If you want specifics please provide the PHP for the select query, the column headings, the field values in the form and other details for this part of the page.

Let me know if you have any questions or need more information.

tankergoblinAuthor Commented:
below file is the detail of the page where ineed to join 2 table

Beverley PortlockCommented:
On each table column you need a link like so (using FirstName as an example)

<a href='http://www.mydomain.com/page.php?sort=Firstname'>First name</a>

Then when it is clicked on the browser will reload the page and you can pick up the "sort" parameter like so

$sort = "";
if ( isset($_GET['sort']) )
     $sort = "ORDER BY " . mysql_real_escape_string( $_GET['sort'] );

and then alter the query that loads the table to read like this

$qry = "Select  Concat(a.Firstname,' ',a.Lastname) As Name, a.Address,a.Contact,a.Age,b.Marks
          From StudentDetail a
          Join StudentResult b ON a.StudentID= b.StudentID
          Where b.Marks = 50
          $sort ";
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

tankergoblinAuthor Commented:
you mean it will reload the whole page? Is it possible to use ajax to do it? where it only reload the table and the whole page? which way is better?
tankergoblinAuthor Commented:
also how can i put a arrow sign beside the column header to show user that the table is sortable?
Beverley PortlockCommented:
It can be done with Ajax, but is it worth the trouble? It takes a fraction of a second for the server to regenerate the page and because it is displaying the same content most people never notice the redisplay.

To put an arrow on the header, create two graphics (sort ascending and sort descending) and then enclose them in A tags. Add an extra parameter to indicate ascending or descending sort and modify the ORDER BY clause accordingly.

Something like this...

<a href='http://mydomain.com/page.php?sort=Firstname&seq=asc'>
<img src='ascSort.gif' />

<a href='http://mydomain.com/page.php?sort=Firstname&seq=dsc'>
<img src='descendSort.gif' />


$sort = "";
if ( isset($_GET['sort']) )
     if ( $_GET['seq'] == 'dsc' )
          $sort = "ORDER BY " . mysql_real_escape_string( $_GET['sort'] ) . " desc";
          $sort = "ORDER BY " . mysql_real_escape_string( $_GET['sort'] );

Ray PaseurCommented:
If you have phpMyAdmin available, you can find this sort of code inside their displays when you browse a table of data.  IIRC they sort either ascending or descending on the columns when you click the headers.  I think they do not reload the whole page - just the frame.  I agree with Brian - the effort to do this in AJAX may be too much relative to the payoff - but that is an application design choice that you will have to make.

Here are a couple of code segments from one of my sites.  It's hardly complete, but it will give you an idea of how I control the ordering of data on the page.

HTH, ~Ray
$seq	= strtolower(substr(trim(get_clean_alphanum_string($_GET[s])),0,1));
$sfq	= strtolower(substr(trim(get_clean_alphanum_string($_GET[o])),0,1));
$order = "series DESC, date DESC"; // DEFAULT
if (!ereg('[ad]{1}', $sfq))			{ $sfq = 'd'; }
if (!ereg('[cdapnfwm]{1}', $seq))	{ $seq = 'c'; }
if ( ($seq == 'c') && ($sfq == 'd') ) { $order = "series DESC, date DESC"; }
if ( ($seq == 'd') && ($sfq == 'd') ) { $order = "date DESC, series DESC"; }
if ( ($seq == 'a') && ($sfq == 'd') ) { $order = "a1_collation DESC, a2_collation DESC, series DESC, date DESC"; }
if ( ($seq == 'p') && ($sfq == 'd') ) { $order = "a2_collation DESC, a1_collation DESC, series DESC, date DESC"; }
if ( ($seq == 'n') && ($sfq == 'd') ) { $order = "tplace DESC, series DESC, date DESC"; }
if ( ($seq == 'f') && ($sfq == 'd') ) { $order = "t_nfish DESC, series DESC, date DESC"; }
if ( ($seq == 'w') && ($sfq == 'd') ) { $order = "t_weight DESC, series DESC, date DESC"; }
if ( ($seq == 'm') && ($sfq == 'd') ) { $order = "t_payout DESC, series DESC, date DESC"; }
if ( ($seq == 'c') && ($sfq == 'a') ) { $order = "series ASC, date ASC"; }
if ( ($seq == 'd') && ($sfq == 'a') ) { $order = "date ASC, series ASC"; }
if ( ($seq == 'a') && ($sfq == 'a') ) { $order = "a1_collation ASC, a2_collation ASC, series DESC, date DESC"; }
if ( ($seq == 'p') && ($sfq == 'a') ) { $order = "a2_collation ASC, a1_collation ASC, series DESC, date DESC"; }
if ( ($seq == 'n') && ($sfq == 'a') ) { $order = "tplace ASC, series DESC, date DESC"; }
if ( ($seq == 'f') && ($sfq == 'a') ) { $order = "t_nfish ASC, series DESC, date DESC"; }
if ( ($seq == 'w') && ($sfq == 'a') ) { $order = "t_weight ASC, series DESC, date DESC"; }
if ( ($seq == 'm') && ($sfq == 'a') ) { $order = "t_payout ASC, series DESC, date DESC"; }
if ($sfq == 'a') { $sfq = 'd'; } else { $sfq = 'a'; }
echo "<table cellpadding=\"1\" cellspacing=\"1\" border=\"1\" width=\"98%\">\n";
echo "<tr>\n";
echo "<th> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=c&o=$sfq\">Bass Series</a> &nbsp; </th>\n";
echo "<th> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=d&o=$sfq\">Tournament</a> &nbsp; </th>\n";
echo "<th> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=a&o=$sfq\">Angler</a> &nbsp; </th>\n";
echo "<th> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=p&o=$sfq\">Co-Angler</a> &nbsp; </th>\n";
echo "<th align=\"right\"> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=n&o=$sfq\">Place</a> &nbsp;</th>\n";
echo "<th align=\"right\"> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=f&o=$sfq\">Fish</a> &nbsp;</th>\n";
echo "<th align=\"right\"> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=w&o=$sfq\">Weight</a> &nbsp;</th>\n";
echo "<th align=\"right\"> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=m&o=$sfq\">Payout</a> &nbsp;</th>\n";
echo "<th align=\"right\"> &nbsp;</th>\n";
echo "</tr>\n";
$page_column_headers = ob_get_contents();
$tsql	= "SELECT * FROM TOURS ORDER BY $order";

Open in new window

tankergoblinAuthor Commented:
how to make it the arrow only appear when i click on the header column?
tankergoblinAuthor Commented:
also First name
i cannot put domain in fixed cause let say i change my domain name then everything will be useless

also is there anyway it will call back itself.

cause my not pointing the page to other page but to the same page...
Beverley PortlockCommented:
"cannot put domain in fixed cause let say i change my domain name then everything will be useless"

You can use $_SERVER['HTTP_HOST'] to pick up the domain name and then it will always be right no matter where you move the code

$thisPage = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'];
echo "<a href='$thisPage?sort=Firstname&seq=asc'>";
<img src='ascSort.gif' />

Depending on how your server is configured you may need to add an extra '/' to this statement

$thisPage = "http://" . $_SERVER['HTTP_HOST'] . $_SERVER['PHP_SELF'];

like so

$thisPage = "http://" . $_SERVER['HTTP_HOST'] . "/" . $_SERVER['PHP_SELF'];
Ray PaseurCommented:
"cause my not pointing the page to other page but to the same page..."

Yes, you can use $_SERVER["PHP_SELF"] to do that.  That is how I do it in my code snippet above, like this:

echo "<th> <a href=\"{$_SERVER[PHP_SELF]}?a=$a_key&s=c&o=$sfq\">Bass Series</a>   </th>\n";

Beverley PortlockCommented:
@Ray - Had to scroll down for that bit!!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now