Help with sorting columns

[Anon]

I've just started messing with php in the last week, and I am trying to do the following:

Have a php/HTML table of data with the first row being headings, and the subsequent rows being the appropriate data; the important part is that the headings must be clickable, and clicking a heading must refresh the page, displaying the data sorted on that column that you clicked. If you click on the heading again, it will sort in the reverse order.

I have started a page with the data, but clicking on the column headings doesn't sort them. Can anyone please tell me what I'm doing wrong?? Do I need another php file or am I missing some data in the php file I have? (the members.php) Here's the page if you want to view the source. http://www.clanrtl.com/fortune/members.php

Thanks in advance for any suggestions

[Anon]

You don't get that effect for free - you have to code it yourself.

It's not that hard - it's just a bunch of if statements. And depending on the if statement that's true, you sort by that thing. For example, if you execute the script like:

members.php?sortBy=age

In your code you'll have:

$sortSQL = "";
if (!isset($sortyBy)) {
$sortSQL = " ORDER BY name"; // default sort
} elseif ($sortBy == "age") {
$sortSQL = " ORDER BY age";
} elseif (etc...) {
}

Then in your query:

$query = "SELECT name, age, points FROM tbl";
$query .= $sortSQL;
mysql_query($query);

Hope you understood that...

For the "if cliked twice, reverse order" effect, you'll use the same idea - pass a parameter to the script saying that it should sort in reverse order.

Diego

[bretticus]

Good advice from Diego...

If you wanna save a lil' bit of writers cramp...

do basically the same except in stead of all those if,elseif statements, just use the value of your query string

query = "SELECT * FROM table";

if (isset($orderby)){
query .= " ORDER BY $orderby";
}
//as for the asc desc sorting...
if (isset($sortby)){
query .= " $sortby";
}

Now in your top columns use the following links <a href="<?=$PHP_SELF?>?orderby=colname&sortby=
<?
if ($orderby == "colname"){
if ($sortby == "asc"){
echo "desc";
}else{
echo "asc"
}
}else{
echo "asc";
}?>
">Col Name</a>

Sorry for the ugly code...I wrote it fast and off the top of my head.

Hope that helps.

[Anon]

Thanks for the suggestion, but I'm not sure what to input in place of what you have. I mean, what is supposed to be filled in (data) and what are the commands?

You have

members.php?sortBy=age

In your code you'll have:

$sortSQL = "";
if (!isset($sortyBy)) {
$sortSQL = " ORDER BY name"; // default sort
} elseif ($sortBy == "age") {
$sortSQL = " ORDER BY age";
} elseif (etc...) {
}

Would I put

members.php?sort=1

$sortSQL = "";
if (!isset($sort)) {
$sortSQL = " ORDER BY name"; // default
sort
} elseif ($sort == "1") {
$sortSQL = " ORDER BY 1";
} elseif (?????what goes here?) {
}

What goes in your etc?
Is the "age" a command that I replace with my numbers I have? ( 1, 2, 3, etc)
I have 7 columns, so how do I repeat it and where does the code go in my .php file? Before the table command?

I'm sorry for all the questions, but I keep trying different things, and can't seem to get it right...I really appreciate any help.