Header Links (sort by)

n00b needs help!

[Viduus]

Hello, as I'm sure you see a lot, I'm pretty new to php, and I've built a simple roster that works.
Now, I want to add the functionality of sorting my the column headers of the table.

I've tried a few things but I'm just too ignorant to make it work - any help is appreciated.

Here's the *working* page, which is a php include on another page, which is why the table tags & other things are not there:

<?
/*connect - working */
 $path = __FILE__;
 $path = str_replace('members.php', '', $path);
 include_once($path . 'settings.php');
 $dbcon = mysql_connect($db_server, $db_user, $db_passwd) or
 die("Could not connect: " . mysql_error());
 mysql_select_db($db_name);
 
/*to be used to change the display of the text data in the DB for 'join_date'
 from 2004-05-19, but I can't make that work either, so I left it for now */
 setlocale(LC_TIME,'C');
/*all this works fine, but I want to insert links so that the $result's 
 "ORDER BY mem_id" can be changed to a variable, depending on column link clicked */
 
 echo '<tr>';
echo '<th colspan=2>Name</th>';
echo '<th>Title</th>';
echo '<th>Alts</th>';
echo '<th>Division</th>';
echo '<th>Joined</th>';
echo '<th>Timezone</th>';
echo '<th>Email</th>';
echo '</tr>';
 $result = mysql_query("SELECT * FROM memberlist ORDER BY mem_id");
if (!$result)
 {echo(mysql_error());}
else {
 while ($row = mysql_fetch_array($result)) {
$id = $row['mem_id'];
 $active = $row['is_active'];
 $avatar = $row['avatar'];
 $name = $row['name'];
 $title = $row['title'];
 $alts = $row['alts'];
 $div = $row['division'];
 $started = $row['join_date'];
 $zone = $row['timezone'];
 $email = $row['email'];
/*remove inactive flagged members */
if ($active == 1) {
echo "<tr><td><img src=\"".$avatar."\"></td><td><b>". $name."</b></td><td>".$title."</td><td>". $alts."</td><td>".$div."</td><td>". $started."</td><td>".$zone."</td><td>&nbsp;<a href=\"mailto:".$email."\">".$email."</a></td></tr>"; }
else {}
 }
 }
?>

Thanks in advance of any help.

[edited by: jatar_k at 2:18 am (utc) on July 20, 2004]
[edit reason] fixed sidescroll [/edit]

[rlkanter]

Here is a basic way to do it

$sort_by = $_GET['sort_by'];

/* append the order to the sql statement */
if( $sort_by ){
$sql.= " ORDER BY " . $sort_by . " DESC";
} else {
$sql.= " ORDER BY mem_id";
}

/* change this:
echo '<th colspan=2>Name</th>';
to this: */

printf("<th colspan=2><a href=\"%s?sort_by=name\">Name</a></th>\n",
$_SERVER['PHP_SELF'] );

[Viduus]

I'm sorry, I'm pretty slow, been at this thing for hours.... I understand the printf & where it goes, but the other two snippets, I have no idea where to locate in my page.
I tried inserting them in a couple of spots but i get errors from closing tags '}' , and if I remove the "offenders" I get a syntax error.

(got my dates displaying properly, finally, by the way ;} )

[rlkanter]

Try replacing your mysql_query line with this:

$sort_by = $_GET['sort_by'];

$sql = 'SELECT * FROM memberlist';

/* append the order to the sql statement */
if( $sort_by ){
$sql.= ' ORDER BY ' . $sort_by . ' DESC';
} else {
$sql.= ' ORDER BY mem_id';
}

$result = mysql_query( $sql );

Make sure the $sort_by is a column in db, or modify the $sort_by if/else to accomodate it.

[Viduus]

rlkanter
You rock, thanks a million! :}

/me goes looking for a method to invert the sort.... ;}

[rlkanter]

Just append ASC (for ascending) or DESC (for descending) on the end of the ORDER BY field string.

For example:

ORDER BY name DESC
ORDER BY id ASC

Glad I could help :)

[Viduus]

Hmm sometimes simple gets complicated... this is supposed to work, on paper...:

printf("<th><a href=\"%s?sort_by=email&last_sort_by=$sort_by&is_asc=$is_asc\">Email</a></th>\n", $_SERVER['PHP_SELF'] );
echo '</tr>';
$sort_by = $_GET['sort_by']; 
$last_sort_by = $_GET['last_sort_by']; 
$is_asc = $_GET['is_asc']; 
if (strlen($sort_by) == 0) 
{ 
$sort_by = 'mem_id'; 
$is_asc = '1'; 
} 
else if ($sort_by == $last_sort_by) 
{ 
$is_asc = ($is_asc == '1'? '0' : '1'); 
} 
else 
{ 
$is_asc = '1'; 
}
$sql = "SELECT * FROM memberlist ORDER BY " . $sort_by . $asc; 
 
$result = mysql_query( $sql );

But it doesn't; it sorts ascending in each and every case.

What am I doing wrong?!