how to sort column

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

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




query.doc

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 ";

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?

also how can i put a arrow sign beside the column header to show user that the table is sortable?

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'] );

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";

Select allOpen in new window

how to make it the arrow only appear when i click on the header column?

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?

"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'];

"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";

@Ray - Had to scroll down for that bit!!

;-)