?
Solved

how to sort column

Posted on 2009-05-15
13
Medium Priority
?
794 Views
Last Modified: 2012-05-07
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 .
0
Comment
Question by:tankergoblin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 54

Expert Comment

by:b0lsc0tt
ID: 24400965
tankergoblin,

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.

b0lsc0tt
0
 
LVL 7

Author Comment

by:tankergoblin
ID: 24401489
below file is the detail of the page where ineed to join 2 table




query.doc
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24402035
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 ";
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:tankergoblin
ID: 24406613
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?
0
 
LVL 7

Author Comment

by:tankergoblin
ID: 24406615
also how can i put a arrow sign beside the column header to show user that the table is sortable?
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24406677
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>

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

-----


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

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24408155
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
// DETERMINE THE ORDER FOR THE QUERY
$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'; }
 
// COLUMN HEADERS FOR THE LIST OF TOURNAMENTS AND RESULTS
ob_start();
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();
ob_end_clean();
 
// PULL ROWS
ob_start();
$tsql	= "SELECT * FROM TOURS ORDER BY $order";

Open in new window

0
 
LVL 7

Author Comment

by:tankergoblin
ID: 24408653
how to make it the arrow only appear when i click on the header column?
0
 
LVL 7

Author Comment

by:tankergoblin
ID: 24408690
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...
how?
0
 
LVL 6

Accepted Solution

by:
birwin earned 2000 total points
ID: 24409455
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24410176
"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

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

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'];
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24415793
"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";

0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 24419785
@Ray - Had to scroll down for that bit!!

;-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question