Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Aug 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question grouping by category in recordsets

    I have a list in a database that I have categorised using a category field. I now want to retrieve all records but grouped by categories. how do i get my asp to loop through each category creating a recordset for each one rather than hard code a new recordset for each category i would rather it did it dynamically if thats possible ???

    so eg

    category travel
    1 - B&B's records (recid 1)
    2- rail info (recid 2)

    category local info
    1 - swimming baths (recid 3)
    2 - museum info (recid 4)


    etc etc

    cheers

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    you can use "GROUP BY" in your SQL statement like so

    Code:
    SELECT category, field1, field2 FROM myTable GROUP BY category ORDER BY field1, field2
    say you want to have a heading above each of the categories then change the code to include an array and loop through the array

    Code:
    dim objConn, rs, myArray, i
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Insert_your_connection_string_here"
    myArray = Array("travel","local info")
    for i = 0 to UBound(myArray)
        Set rs = objConn.Execute("SELECT category, field1, field2 FROM myTable WHERE category = '" & myArray(i) & "' ORDER BY field1, field2"
        If Not rs.EOF Then
           Response.Write "<h3>" & myArray(i) & "</h3>"
           Response.write rs("field1")  'etc....
        End If
        Set rs = Nothing
    next


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •