rory
10-11-2004, 04:59 AM
Hi, im sure this is pretty simple, but its been a while since i really was involved in any depth with database and asp, at least I wrote some code way back when that did this but dont have it anymore.
I have a database, with table:
CATS
catid: cat: parentid
Then for example I have the following Entries:
catid: cat: parentid
1 Arts & Entertainment 0
2 Clubs & Nightlife 1
3 Nightclubs 2
4 Bars & Pubs 2
5 Comedy Clubs 2
Obviously I have tons more categories but this is for example purposes.
Anyway, I can either, using 2 different codes get the first 2 levels, and no more below, or get say catid 1, 2, and 3, but not 4 and 5.
------------- EXCUSE The MESS as Was Back And Forth with code ------
SET rs_sub_cats = conn.EXECUTE("select * from cat where parentid = '" & REQUEST("catid") & "'")
IF NOT rs_sub_cats.EOF THEN
sub_cat = rs_sub_cats("cat")
sub_catid = rs_sub_cats("catid")
get_sub_cats = get_sub_cats & "<a href=""" & REQUEST.ServerVariables("URL") & "?catid=" & rs_sub_cats("catid") & """>" & rs_sub_cats("cat") & "</a>" & showcnt & "
"
DO
set rs_ci2 = conn.execute("select * from cat where parentid = '" & sub_catid & "'")
if not rs_ci2.eof then
sub_catid = rs_ci2("catid")
sub_cat = rs_ci2("cat")
response.write sub_catid & "
"
response.write "<li>" & sub_cat & "-" & sub_catid & "
"
rs_ci2.movenext
end if
loop until rs_ci2.eof
end If
--------------------RETURNS --------------
select * from cat where parentid = '1'
2
Clubs & Nightlife-2
select * from cat where parentid = '2'
3
Nightclubs-3
select * from cat where parentid = '3'
----------so you see it is not getting the other cats ----
any ideas?
there has to be an easier way, then a bunch of sub loops and for nexts loops etc. There is a way Im doing it now with an extra field called above cats and it is like this -1-2-3 then I just do an Instr() in the SQL select and it is fast, but still, would rather not as that is extra code when adding the record.
thanks
Rory
I have a database, with table:
CATS
catid: cat: parentid
Then for example I have the following Entries:
catid: cat: parentid
1 Arts & Entertainment 0
2 Clubs & Nightlife 1
3 Nightclubs 2
4 Bars & Pubs 2
5 Comedy Clubs 2
Obviously I have tons more categories but this is for example purposes.
Anyway, I can either, using 2 different codes get the first 2 levels, and no more below, or get say catid 1, 2, and 3, but not 4 and 5.
------------- EXCUSE The MESS as Was Back And Forth with code ------
SET rs_sub_cats = conn.EXECUTE("select * from cat where parentid = '" & REQUEST("catid") & "'")
IF NOT rs_sub_cats.EOF THEN
sub_cat = rs_sub_cats("cat")
sub_catid = rs_sub_cats("catid")
get_sub_cats = get_sub_cats & "<a href=""" & REQUEST.ServerVariables("URL") & "?catid=" & rs_sub_cats("catid") & """>" & rs_sub_cats("cat") & "</a>" & showcnt & "
"
DO
set rs_ci2 = conn.execute("select * from cat where parentid = '" & sub_catid & "'")
if not rs_ci2.eof then
sub_catid = rs_ci2("catid")
sub_cat = rs_ci2("cat")
response.write sub_catid & "
"
response.write "<li>" & sub_cat & "-" & sub_catid & "
"
rs_ci2.movenext
end if
loop until rs_ci2.eof
end If
--------------------RETURNS --------------
select * from cat where parentid = '1'
2
Clubs & Nightlife-2
select * from cat where parentid = '2'
3
Nightclubs-3
select * from cat where parentid = '3'
----------so you see it is not getting the other cats ----
any ideas?
there has to be an easier way, then a bunch of sub loops and for nexts loops etc. There is a way Im doing it now with an extra field called above cats and it is like this -1-2-3 then I just do an Instr() in the SQL select and it is fast, but still, would rather not as that is extra code when adding the record.
thanks
Rory