|
Subject:
|
GROUP BY... then sub group?
|
|
Posted By:
|
mr.smith
|
Post Date:
|
11/25/2003 6:46:41 PM
|
Hi there,
I am trying to create a sub-set of records to populate two lists.
One list will be the category, the next will be the sub-category. I am trying to list the "sub-category" only for each category.
Category 1
Sub-Category 1
Sub-Category 2
Sub-Category 3
Category 2
Sub-Category 1
Sub-Category 2
Sub-Category 3
Remember, I only want the SUB-categories. How do I do this? I've tried everything I can think of.
|
|
Reply By:
|
rodmcleay
|
Reply Date:
|
11/25/2003 8:31:07 PM
|
Not quite sure what you mean,
Do you wish a recoredset to populate a dropdown with catagories and when selected another dropdown will only show the subcategories for that catagory
or
Do you wish to display records from a database like you have shown in the question
======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
|
|
Reply By:
|
rodmcleay
|
Reply Date:
|
11/25/2003 8:33:36 PM
|
If you wish only to display the a recordset like you have shown then try
Order you select statment by Catagory and Sub-Category
Set the category = ""
Loop throught the records
if catagory <> recordset("Category") then
'write your category header
end if
'write the Subcategory line
end loop
======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
|
|
Reply By:
|
mr.smith
|
Reply Date:
|
11/25/2003 10:17:19 PM
|
Yes, a dropdown with catagories and when selected another dropdown will only show the subcategories for that catagory.
|
|
Reply By:
|
rodmcleay
|
Reply Date:
|
11/25/2003 10:40:11 PM
|
Best way
You could create a two dimensional javascript array holing all the catagories and all the subcatagories within each catagory.
With the onchange event of the first dropdown remove all the elements of the second dropdown and then loop through the 2d array, when the catagory matches (1st dimension) loop thought the second dimension and add the subcatagories to the dropdown.
Adding and removing dropdown options:
http://tutorials.beginners.co.uk/read/id/68
Multidimensional javascript arrays:
http://www.js-examples.com/asp/multiarray.php3
An less elegant method would be to create different subcatagory dropdowns for each catagory and hiding and showing them on the catagory onchange, but his is a fairly ugly solution.
======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
|
|
Reply By:
|
rodmcleay
|
Reply Date:
|
11/25/2003 11:00:26 PM
|
Sorry not a good array eg
try
get your recordset
test there is a record then
i = 0
arr = "var cArr= new Array();"
Loop throught the records
arr = arr & "cArr[" & i & "]= new Array();"
arr = arr & "cArr[" & i & "][0]= '" & recordset("category") & "';"
arr = arr & "cArr[" & i & "][1]= '" & recordset("sub-category") & "';"
i = i + 1
loop
then just write the vbscript variable arr into a js script block
on the onchange of the category remove all elements of the sub-catagory dropdown, loop the the cArr array and ad any cArr[index][1] where cArr[index][0] == categorydropdown.value
Sorry about the half code half pseudo explanation.
======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
|
|
Reply By:
|
mr.smith
|
Reply Date:
|
11/26/2003 4:43:26 PM
|
I tried your first suggestion and all I get is a huge list of categories and the sub-categories don't even write out.
Your second suggestion is a little over my head.
What I'm looking for is a simple block that writes out a category name, and the writes out its associated sub categories, and then moves to the next category.
The code is written in javascript. It's actually this one:
http://www.trans4mind.com/personal_development/ /menuWrite.htm
I simply want to populate the dropdowns with what is in the database.
|
|
Reply By:
|
mr.smith
|
Reply Date:
|
11/26/2003 4:44:21 PM
|
Uh... so why did this system crop my URL?
http://www.trans4mind.com/personal_development/JavaScript/menuWrite.htm
|
|
Reply By:
|
rodmcleay
|
Reply Date:
|
11/26/2003 8:40:47 PM
|
That is a good example, it's a variation on what I was trying to show you, so you can go with that. What you need to do is dynamically produce the bit where the new options are added.
So go back to ordering your recordset by Category,Sub-Category.
create the jsOut variable below while looping through the recordset to create the category dropdown.
i=-1
j=0
currentCategory = ""
jsOut = ""
while not recordset.eof
if recordset("Category") <> currentCateggory then
'Write your Category option here
'
' Category option code
'
'CODE TO WRITE THE JS
'Close the previous if statement if not the first one
if i >= 0 then
jsOut = jsOut & "}" & vbNewLine
end if
'increment i and reset j
i = i + 1
j=0
'write the js if statement to the variable
jsOut = jsOut & "if (index == " & i & "){" & vbNewLine
end if
'write the sub-cat as a new option within the js if.
jsOut = jsOut & "options[" & j & "]=new Option(""" & recordset("Sub-Category") & """);" & vbNewLine
'increment j
j = j + 1
wend
'close the last js if statement
jsOut = jsOut & "}"
Then where the example had the code
if (Indx==1){
options[0]=new Option("Choose a JavaScript Page","");
options[1]=new Option("Alerts","alerts.htm");
options[2]=new Option("Back and Forward Buttons","BackForward.htm");
options[3]=new Option("Contents","index.html");
}
You write
<%= jsOut%>
Give that a try and let me know if you have any drama
======================================
They say, best men are moulded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================
|
