ASP: sort posts in a select menu..

Discussion in 'C#' started by Glanby, Aug 8, 2006.

  1. #1
    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!
     
    Glanby, Aug 8, 2006 IP
  2. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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 :D it aint goin away people - you might as well get used to coding around it.

    VG
     
    vectorgraphx, Aug 8, 2006 IP
  3. Glanby

    Glanby Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks a bunch! Only one problem: I´m an idiot when it comes to ASP. :eek:
    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! :eek: )

    Thanks again for helping! :)
     
    Glanby, Aug 8, 2006 IP
  4. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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
     
    vectorgraphx, Aug 8, 2006 IP
  5. Glanby

    Glanby Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    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....?
     
    Glanby, Aug 8, 2006 IP
  6. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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):
     
    vectorgraphx, Aug 8, 2006 IP
  7. Glanby

    Glanby Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Ahaaa... great!
    I can never get those <% -signs right. :p

    Thanks a million for the help!
     
    Glanby, Aug 8, 2006 IP
  8. Free Born John

    Free Born John Guest

    Messages:
    111
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #8
    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
     
    Free Born John, Aug 8, 2006 IP
  9. Glanby

    Glanby Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    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!! ;)
     
    Glanby, Aug 9, 2006 IP
  10. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #10
    you're welcome - congratulations on getting it to work :D
     
    vectorgraphx, Aug 9, 2006 IP
  11. Glanby

    Glanby Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    Guess what...? Im back.. :rolleyes:

    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..? :eek:

    Thanks for a great forum, I´ll try to help out others as well!
     
    Glanby, Aug 10, 2006 IP
  12. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #12
    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.
     
    vectorgraphx, Aug 10, 2006 IP
  13. Glanby

    Glanby Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    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. :D
     
    Glanby, Aug 10, 2006 IP
  14. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #14
    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 ...
     
    fluid, Aug 12, 2006 IP