Advice on Sorting with Dropdown

rdobbs

I am looking for a good way to allow our users to sort data by selecting the sort column from a drop down box on the web page. Any advice or links to some helpful tutorials? I'm not a very advanced coder, but confident I can get something to work.

Thanks in advance.
-Dave

kburger

There's not nearly enough information here to help you much. But assuming each item in the dropdown corresponds to a field in the database table that you want to sort by -- just use that in the query that returns the results.

eg. ...ORDER BY $dropdownvalue

rdobbs

Ah sorry. Yes, pulling data in from MSSQL 7. I will try what you suggested.

Thx

rdobbs

I still haven't been able to get this to work, I'm flubbing up somewhere. I'll post some code snippets. Again, the data is already being pulled successfully into the web page from a MSSQL 7 server. But the user has no sorting abilty, so I'd like to include a drop down on the page that will allow them to sort by a few of the fields being displayed.

//The query being used on the page is like this, but changed to protect the innocent:

$query = "SELECT Table.Alias,Table.ProductTicker<many other fields listed> FROM <tables> WHERE ID='$ID' order by Alias;";

//then i set some variables, here are a couple

$product[$index] = $data["Alias"];
$ticker[$index] = $data["ProductTicker"];

//then i display these on the page

<td>$product[$count]</td>
<td>$ticker[$count]</td>

//now create a form drop down. this is where i get confused. How do I call the query and get it to sort by the other field
<form name="Sort">
<select name=DropDown onChange="ChangeThePageSort();">
<option value=-1>
<option value='page.phtml?ID=<? echo $ID; ?>'<??>>Alias</option>
<option value='page.phtml?tID=<? echo $ID ?>'<? ?>>Product Ticker</option>
</select>
</form>

I appreciate any help and apologize if this is still too vague for anyone to help.

kburger

One thing to keep in mind is that PHP is a server side scripting language, meaning that any processing that you want PHP to do will be done on the server before the page is sent to the browser. It looks like what you're trying to do is rearrange the stuff on the page after it's been sent to the browser. If you want to do it this way it will need to be done using javascript -- very messy. If you want to do it that way I'll leave it up to you. 🙂

However, if you want it to be done using PHP I can help you, but the page will need to be "reloaded" from the server. Here's the basic idea.

<form method="post" action="">
<select name=sort onchange="this.form.submit()">
<option value="">Sort By...</option>
<option value="Alias">Alias</option>
<option value="ProductTicker">Product Ticker</option>
</select>
</form> 

<?php
if (isset($_POST['sort'])) $sort = $_POST['sort'];
else $sort = "Alias";

$query = "SELECT <whatever> FROM <wherever> WHERE <wherever> ORDER BY $sort;";

// down here execute your query and display your results
?>

rdobbs

I'd definitely like this to be server side, any other way is rather inefficient and just plain "messy" as you suggested.

Thanks again for your assistance, I appreciate it.
I'll give this a go.

rdobbs

Ok, looks like I got this working 🙂.

I have one last question, and I don't necessarily need a reply from you (you've gotten me this far). I'd like to take this one step further and be able to sort ASC or DESC. And really this is only pertinent to a couple fields, for example "market value". I'm just going to try and throw a "If...Then" where If $sort = "market value" then "order by $sort DESC", etc. I'm also wondering if there's anyway to set it up that if the user goes back and selects the same field again that it sorts the other direction. That way I don't have to do it the Amazon way with 2 entries "Sort A-Z" and "Sort Z-A", etc.

I'll figure it out. Thanks again

Cheers!

kburger

There are lots of different ways this could be done. One way might be to have a "radio" field in your form called "ascdesc" with values of "ASC" and "DESC". Then your query would be "...ORDER BY $sort $ascdesc".

Or you might want to do away with the form completely and just create a couple of links in each of your HTML column headers with an "up" arrow and a "down" arrow, and pass the relevant info via "GET".