p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Professional (http://p2p.wrox.com/forumdisplay.php?f=63)
-   -   Listing Category then Items (http://p2p.wrox.com/showthread.php?t=7163)

harpua December 11th, 2003 07:58 PM

Listing Category then Items
 
Can somebody please give me some advise on how to implement this, I can do the easy SQL statements, but I get lost on the complex ones and what I've tried doesn't work or I know there is a better way, I just don't know it. I am using ASP, SQL Server. Right now I have one table where I want to select all the Categories and Items and have them display as in my sample, without the categories repeating.

SELECT Category, Item FROM Table1 WHERE Start_Date <= '" & Date & "';

This is how I would like it to display
    Category_01
        Item_01
        Item_02

    Category_02
        Item_01
        Item_02
        Item_03
        Item_04

Can this work with one table or should I make another table then select the category and do some kind of Join statement, also some Categories might not have any Items for that day.

Any help would be appreciated, Thanks in advance.

pgtips December 12th, 2003 06:40 AM

Ok, so you've got the category and item in the same recordset. Your SQL is fine, you need to do the grouping in your asp code. You just need to keep track of the category as you loop through the recordset and only output the category when it changes, something like this:
Code:

sCatPrevious = ""
Do While Not rs.EOF

  sCatCurrent = "" & rs.Fields("Category").Value
  If sCatCurrent <> sCatPrevious Then
    ' output the category
    Response.Write sCatCurrent

    ' update the "previous" variable
    sCatPrevious = sCatCurrent

  End If

  ' output the item
  Response.Write "" & rs.Fields("Item").Value

  rs.MoveNext
Loop


pgtips December 12th, 2003 06:43 AM

Just noticed that you need an ORDER BY clause in your SQL to ensure that the Categories are sorted in your recordset
Code:

"SELECT Category, Item FROM Table1 WHERE Start_Date <= '" & Date & "' ORDER BY Category, Item";

harpua December 12th, 2003 11:47 AM

Thanks a million. You saved me a lot of time.


All times are GMT -4. The time now is 10:31 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.