ASP: sort posts in a select menu..

Hey everyone!

I have a simple address-script (its actually a guestbookscript that I changed a bit). Part of the code looks like this:

<%

Dim IntSida, Conn, RecSet, SQL

IntSida = Trim(Request.QueryString("Sida"))
If IntSida = "" Then
IntSida = 1
Else
IntSida = Cint(IntSida)
End If



Set Conn = Server.CreateObject("ADODB.Connection")
Set RecSet = Server.CreateObject("ADODB.RecordSet")
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)};dbq=" & Server.MapPath("adressdb.mdb")
SQL = "SELECT * FROM adresser ORDER BY datum DESC"

RecSet.Open SQL, Conn, 3,1

If RecSet.EOF = True Then
Response.Write("Inga nyheter hittades i databasen!<BR>")
Else

RecSet.PageSize = 200
RecSet.AbsolutePage = IntSida
End If

Do until RecSet.EOF Or IntAntal >= RecSet.PageSize
IntAntal = IntAntal + 1
%>

Code (markup):

As you can see, it sorts the posts by "datum" (date in Swedish). What I want is a select list, where I can choose if I want it to sort the posts by date, name, number, etc. Like this: http://www.glanby.se/adressboken200608/adresser.asp
Although the select list does NOT actually work in the example.
Can anyone help me with this?

Thanks in advance!

 

sure, pass the category as a querystring, i.e.

http://www.glanby.se/adressboken200608/adresser.asp?cat=datum

or something to that affect.

then, on adresser.asp, look for that querystring value, create an "order by" clause that reflects the sort order, and insert it into your sql query. like so.

<% cat = replace(request("cat"),"'","''")

select case cat
case "datum"
sqlorder = "ORDER by datum asc"
case "address"
sqlorder = "ORDER by address asc"
case "cup_size"
sqlorder = "ORDER by cup_size asc"
case else
sqlorder = "ORDER by datum asc"
end select


SQL = "SELECT * FROM adresser " & sqlorder
%>

that should work just peachy keen for ya. yes i did just say peachy keen. ~shudder~

ps notice how i'm not passing the value directly to the SQL query. Anytime you can avoid passing user input directly to the sql query entirely you're much better off from a security standpoint. if you're manually defining what you're passing to your sql query you're not in any danger of SQL injection.

That's right.... Vectorgraphx makes YET ANOTHER reference to SQL injection it aint goin away people - you might as well get used to coding around it.

VG

 

Thanks a bunch! Only one problem: I´m an idiot when it comes to ASP.
I´m still trying to learn, so I didnt really understand all of your instructions.
Did you mean I should create a select menu like this:

<select name="test">
<option>--Sort posts by--</option>
<option value="www.address.com?cat=date">Date</option>
<option value="www.address.com?cat=name">Name</option>
<option value="www.address.com?cat=phone">Phone</option>
</select>

Code (markup):

?

Im also having trouble understanding where to put this code:

<% cat = replace(request("cat"),"'","''")

select case cat
case "datum"
sqlorder = "ORDER by datum asc"
case "address"
sqlorder = "ORDER by address asc"
case "cup_size"
sqlorder = "ORDER by cup_size asc"
case else
sqlorder = "ORDER by datum asc"
end select


SQL = "SELECT * FROM adresser " & sqlorder
%>

Code (markup):

I understand the idea of your code, but I just dont know how to implement it with my code. (Sorry, I´m an idiot! )

Thanks again for helping!

 

yes, create your dropdown menu exactly as you have it.

then, insert this code (modified to fit your select statement as provided in your second post) right where your sql statement currently is, replacing your sql statement with the one i provided you.


<% cat = replace(request("cat"),"'","''")

select case cat
case "date"
sqlorder = "ORDER by datum asc"
case "name"
sqlorder = "ORDER by name asc"
case "phone"
sqlorder = "ORDER by phone asc"
case else
sqlorder = "ORDER by datum asc"
end select


SQL = "SELECT * FROM adresser " & sqlorder
%>


VG

 

hm, ok.. so I should replace these two lines in my code:

SQL = "SELECT * FROM adresser ORDER BY datum DESC"

RecSet.Open SQL, Conn, 3,1

Code (markup):

with your piece of code:

<% cat = replace(request("cat"),"'","''")

select case cat
case "date"
sqlorder = "ORDER by datum asc"
case "name"
sqlorder = "ORDER by name asc"
case "phone"
sqlorder = "ORDER by phone asc"
case else
sqlorder = "ORDER by datum asc"
end select


SQL = "SELECT * FROM adresser " & sqlorder
%>

Code (markup):

Did I get it right....?

 

replace

SQL = "SELECT * FROM adresser ORDER BY datum DESC"

RecSet.Open SQL, Conn, 3,1

Code (markup):

with this:

cat = replace(request("cat"),"'","''")

select case cat
case "date"
sqlorder = "ORDER by datum asc"
case "name"
sqlorder = "ORDER by name asc"
case "phone"
sqlorder = "ORDER by phone asc"
case else
sqlorder = "ORDER by datum asc"
end select


SQL = "SELECT * FROM adresser " & sqlorder
RecSet.Open SQL, Conn, 3,1

Code (markup):

 

Ahaaa... great!
I can never get those <% -signs right.

Thanks a million for the help!

 

that's a good way of doing it in asp, but every time someone clicks on a sort by header it means a round trip to the server.

If you know any .Net it would be far easier to do it using a bound control like a gridview, as all the sorting is built-in and it doesn't need to refetch the data from the server.

regards

FBJ

 

Hey again!
It works fine now, I just had to add

onchange="location.href = this.options[this.selectedIndex].value"

Code (markup):

to my select menu to get it to work. Thanks a million, Vectorgraphx!!

 

you're welcome - congratulations on getting it to work

 

Guess what...? Im back..

I have another idea: Instead of sorting the posts, it would actually be more efficient if I could choose which posts to show. Right now I have four images, representing family, friens, work and authorities/companys. It would be great if I could choose "show only friends" in a select menu, and it hides the other categories. Any thoughts on how to do this?
(sorry if Im a pain, but I realized sorting the posts by name, phone, etc didnt really have a major function, but its better to be able to show ONE particular category.)
The select menu where I choose which category the post belongs to simply looks like this:

<select name="bild" style="border:1px solid #808080; width: 100%">
<option>--Välj i listan--</option>
<option value="familj.gif">Familj</option>
<option value="vanner.gif">Vänner</option>
<option value="jobb.gif">Jobb</option>
<option value="myndighet.gif">Myndigheter</option>
</select>

Code (markup):

And then of course, one of the columns in the database is called "bild".
Any ideas..?

Thanks for a great forum, I´ll try to help out others as well!

 

glanby, you can use the same idea, except instead of building a dynamic "order by" clause based on your querystring, build a dynamic "where" clause instead with your select case statement.

 

hmm.. ok, but now there´s only one column in the database to focus on. The database contains four columns: date, name, phone, cellphone and category. So when I add a new contact I fill out their name, phonenumber, cellphonenumber and which category they fall into.
So if I want a select menu where I can choose which category to display, Im only focusing on one of the four columns in the database, the "category" column.
I just cant put it together in my head how Im supposed to do that.
The select menu that looks like this:

<select name="test">
<option>--Sort posts by--</option>
<option value="www.address.com?cat=date">Date</option>
<option value="www.address.com?cat=name">Name</option>
<option value="www.address.com?cat=phone">Phone</option>
</select>

Code (markup):

won´t do me much good anymore.
I´m sorry if I sound a bit dizzy in my explanations, I´m having some trouble expressing myself.

 

You wont learn anything if you dont understand what's going on with the codes! Try some asp tutorials.

Now coming back to your question:
You want to display results from a particular category, dont you?

category_id = request("category_id")
' In case the user didnt select any category, default it to say friends
if category_id = 0 then category_id = 1 end if

sqlorder = request("sqlorder")
' in case the user didnt specify a sort order, default it to say 'name'
if sqlorder = "any" then sqlorder = "name" end if

SELECT * FROM adresser WHERE category = " & category_id & " ORDER BY " & sqlorder

Assuming you have a select box for the categories (family, friends etc) and another one for sorting the results by name, phone etc.

<select name="category_id">
<option value="0">Select Category</option>
<option value="1">Friends</option>
<option value="2">Family</option>
</select>

<select name="sqlorder">
<option value="any">Select SortOrder</option>
<option value="name">Name</option>
<option value="phone">Phone</option>
</select>

Take your time to understand what's going on ...