Categories and Database

Rory,

i am a little confused as to what you wish to do. can you briefly explain your goal?

quote:Originally posted by rory

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
</blockquote id="quote"></font id="quote">

B.

DiscountASP.NET
http://www.DiscountASP.NET

 

Hmmm.. i am still not very sure I understand

Can you tell me what these column means?

Cat & ParentID

And what is a sub category?



quote:Originally posted by rory

Hi,

I basically want to get all the sub cats.
</blockquote id="quote"></font id="quote">

B.

DiscountASP.NET
http://www.DiscountASP.NET