Solved

how to sort column

Posted on 2009-05-15
13
784 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
  • 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 108

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 500 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 108

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now