List of Categories and Sub Categories

[EchoAlertcom]

Hello,

I need some help with creating a directory list of Categories and Sub Categoies and then Names under either the Category if the Sub Cat is NULL and in a Sub Cat if it is not null. I will show my sql and and an example of what I want it to look like. Thank you for any guidance. I have been beating my head against the wall for too long. Help put a stop to the self-inflicted violence.

SQL =

SELECT Cat, SubCat, Listing
FROM Listings
WHERE bd_Catagory_Name <> NULL
ORDER BY Cat, SubCat, Listing

Visually I would like to show up on the page like this, I will try to cover all possibilities:

Cat 1
      Listing A (record has Cat = Cat 1, SubCat is NULL)
      Listing B (record has Cat = Cat 1, SubCat is NULL)
   SubCat 1
      Listing C (record has Cat="Cat 1", SubCat = "SubCat 1")
   SubCat 2
      Listing D (record has Cat="Cat 1", SubCat = "SubCat 2")
Cat 2
   SubCat 3
      Listing E (record has Cat="Cat 2", SubCat = "SubCat 3")

I am stumped and lost. Thank you for any help. Let me know if I can clear this mud up any. I really tried to make it as clear as possible.

Sincerely,
Steve

 

[TonyU]

[tt][COLOR=navy}Any way you can show us your database table structure?



[sup]

There's never time to do it right, but there's always time to do it over!
​

[/sup]

 

[lebisol]

one SQL statement wont cut it for that type of displays.look into these concepts
"DataShaping"

http://www.4guysfromrolla.com/webtech/100699-2.shtml

READ 4guysfromrolla********

http://www.dwfaq.com/Tutorials/Dynamic/datashape.asp

"Nested Repeated Regions"

http://www.dwfaq.com/Tutorials/Dynamic/MultipleRepeatRegion.asp

 

[rac2]

Yes this is kind of tricky. I will take a stab at it.

The issue of null sub-categories is really a non-issue because you might have made a sub-category called General and printed that. Handle this by always checking whether the sub-category is null before printing the sub-category. If so, don't print. See *** below.

Assuming you are using ADODB, let the recordset be rsGet.

//Initialize.
new_cat = false;
//If there is at least one record set values.
if( !rsGet.EOF ){
   Cat_current = rsGet("Cat").value;
   Sub_current = rsGet("SubCat").value;
   Cat_previous = "";
   Sub_previous = "";
}


//Loop until there are no more records.
while( !rsGet.EOF ){

   //If the current record is in a different category
   // than the previous record print new category name.
   if( Cat_current != Cat_previous ){
      //Print Cat_current.
      //And replace previous category with new one.
      Cat_previous = Cat_current;
      new_cat = true;
   }
   
   //If the current record is in a new sub-category
   //  then print a new sub-category name.
   if( Sub_current != Sub_previous ) {
      
      //***But check for null sub-category.
      if( Sub_current != null){
          //Print Sub_current.
      }
      
      //Replace previous sub-category with new one.
      Sub_previous = Sub_current;
   }
   
   //In case we have a new category with the
   //  same sub-category
   if( new_cat == true &&
       Sub_current == Sub_previous) {
      
      //***Always check for null sub-category.
      if( Sub_current != null){
          //Print Sub_current.
      }
      
      //Reset flag.
      new_cat = false;
   }
   
   
   //Loop and print listings so long as the category 
   // and sub-category remain the same.
   //And we still have records to print.
   while( !rsGet.EOF &&
          Cat_current == Cat_previous &&
          Sub_current == Sub_previous ) {
      
      //Print listing.
      //Replace previous categories.
      Cat_previous = Cat_current;
      Sub_previous = Sub_current;
      
      //Get the next record.
      rsGet.moveNext();
      
      //Set current categories, if any.
      if( !rsGet.EOF ) {
         Cat_current = rsGet("Cat").value;
         Sub_current = rsGet("SubCat").value;
      }
   }
}

Well then, if you try this logic let me know whether it works.