Categories & subcategories

Hi there,
A question for all experts ...

I have a db table
name : categories
the table has the follow columns
id
CatID
Categories
Subcategories
SubID

What i want to do is something .... like   www.download.com

Show all data from Categories  and also data from subcategories that are equal to CatID .

i don't know how to apply a loop for this , or how many recordsets need to create

overall , have a look at www.download to see what i mean

msolomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kingsfan76Commented:
easiest way is to use two recordset:

<%
strSQL = "SELECT * FROM categories WHERE CatID IS NOT NULL"
set rst1 = conn.execute(strSQL)
while not rst1.EOF

        strSQL = "SELECT * FROM categories WHERE SubID IS NOT NULL AND CatID=" & rst1.Fields("CatID")
         set rst2 = conn.execute(strSQL)
         while not rst2.EOF
                ..............
                rst2.MoveNext
          wend
    rst1.MoveNext
wend
%>

you should be able to loop through all your main cat and their sub cat using the above method.  just a comment about the table, it is better to use two tables instead of one for your design.

categories
CatID    Category

subCategories
SubID    CatID    SubCategory
0
msolomosAuthor Commented:
using DMX  to
create the 2nd recordset
SELECT *
FROM Categories
WHERE SubID <> null AND CatID= "& Recordset1.Fields.Item("CatID")

what wrong with this ?
i get an error
pls help
0
landofcashCommented:
I think that the best db structure for this is

ID    ParentID    Category

(ParentID links with ID)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

kingsfan76Commented:
try

"SELECT *
FROM Categories
WHERE SubID IS NOT null AND CatID= "& Recordset1.Fields("CatID")
0
msolomosAuthor Commented:
here is  the code :
-------------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1253"%>
<!--#include file="Connections/amicro.asp" -->
<%
Dim rscategories
Dim rscategories_numRows

Set rscategories = Server.CreateObject("ADODB.Recordset")
rscategories.ActiveConnection = MM_amicro_STRING
rscategories.Source = "SELECT * FROM Categories"
rscategories.CursorType = 0
rscategories.CursorLocation = 2
rscategories.LockType = 1
rscategories.Open()

rscategories_numRows = 0
%>
<%
Dim rssubcategories
Dim rssubcategories_numRows

Set rssubcategories = Server.CreateObject("ADODB.Recordset")
rssubcategories.ActiveConnection = MM_amicro_STRING
rssubcategories.Source = "SELECT * FROM Subcategories"
rssubcategories.CursorType = 0
rssubcategories.CursorLocation = 2
rssubcategories.LockType = 1
rssubcategories.Open()

rssubcategories_numRows = 0
%>
<%
Dim HLooper1__numRows
HLooper1__numRows = -3
Dim HLooper1__index
HLooper1__index = 0
rssubcategories_numRows = rssubcategories_numRows + HLooper1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
<link href="/assets/ISS.css" rel="stylesheet" type="text/css">
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td> <table>
        <%
startrw = 0
endrw = HLooper1__index
numberColumns = 3
numrows = -1
while((numrows <> 0) AND (Not rssubcategories.EOF))
      startrw = endrw + 1
      endrw = endrw + numberColumns
 %>
        <tr align="center" valign="top">
          <%
While ((startrw <= endrw) AND (Not rssubcategories.EOF))
%>
          <td> <div align="left"><span class="bodytext1title"><%=(rscategories.Fields.Item("category_name").Value)%></span><br>
              <span class="bodytext1"><%=(rssubcategories.Fields.Item("Subcategories").Value)%>,</span></div></td>
          <%
      startrw = startrw + 1
      rssubcategories.MoveNext()
      Wend
      %>
        </tr>
        <%
 numrows=numrows-1
 Wend
 %>
       
      </table></td>
  </tr>
</table>
</body>
</html>
<%
rscategories.Close()
Set rscategories = Nothing
%>
<%
rssubcategories.Close()
Set rssubcategories = Nothing
%>

------------------
can you correct it to gives me what i want ?
0
kingsfan76Commented:
I see what you are trying to do.. but i don't think that will give you the main cat and subcat the way like in download.com.  Here's a modification to your code that will give you the right output, you just need to tweak it a little to get what exactly you want:


-------------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1253"%>
<!--#include file="Connections/amicro.asp" -->
<%

Dim rscategories

Set rscategories = Server.CreateObject("ADODB.Recordset")
rscategories.ActiveConnection = MM_amicro_STRING
rscategories.Source = "SELECT * FROM Categories"
rscategories.CursorType = 0
rscategories.CursorLocation = 2
rscategories.LockType = 1
rscategories.Open()

%>
<%
Dim rssubcategories
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1253">
<link href="/assets/ISS.css" rel="stylesheet" type="text/css">
</head>

<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
 <tr>
    <td>&nbsp;</td>
 </tr>
 <tr>
    <td> <table>
<%

while(not rscategories.EOF)
%>
<tr valign="top">
    <td> <div align="left"><span class="bodytext1title"><%=(rscategories.Fields.Item("category_name").Value)%></span></div></td>
<tr valign="top">
         <td>
             <span class="bodytext1">
<%
   Set rssubcategories = Server.CreateObject("ADODB.Recordset")
   rssubcategories.ActiveConnection = MM_amicro_STRING
   rssubcategories.Source = "SELECT * FROM Subcategories WHERE CatID=" & rscategories.Fields("CatID")
   rssubcategories.CursorType = 0
   rssubcategories.CursorLocation = 2
   rssubcategories.LockType = 1
   rssubcategories.Open()
 
   while not rssubcategories.EOF      'loop through the sub cat for each main CatID
%>
       <%=(rssubcategories.Fields.Item("Subcategories").Value)%>,
<%
        rssubcategories.MoveNext
   wend
%>      
   </span></div></td>
<%
      rssubcategories.Close()
      Set rssubcategories = Nothing
   rscategories.MoveNext                        'next main cat record
   Response.Write("<tr><td>&nbsp;")    'line break
Wend
%>
       
     </table></td>
 </tr>
</table>
</body>
</html>
<%
rscategories.Close()
Set rscategories = Nothing
%>

hope this make sense to you.  if you have problem formatting it the way you want, tell me how exactly you want it to appear and i might be able to help

p.s. correct any mis-spelling i have if there's any  :-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
msolomosAuthor Commented:
>>> kingsfan76

first off all, i want to thank you
it's very good

my only problem now is that i dont know how to make the sublinks

i noticed that u use only one recorsetet
SELECT *
FROM Categories

using this recordset i can make myself the links for categories
but how i will make the links for subcategories ??

--------------------
try to help you on this i setup the page like i wanted ..manually (pure html)

have a look .... www.amicro.gr/categories.asp

that is how i want it
see the subcategories links
how to make these ???

can you pls send me your great code back ...with the link for subacategories ?
thanks
0
msolomosAuthor Commented:
sorry mate , i am silly :)

i made the link /sublist.asp?cID=<%=(rscategories.Fields.Item("catID").Value)%>&sID=<%=(rssubcategories.Fields.Item("SubID").Value)%>

last question before i close it

can i choose only the first 3 records from subcategories ?

0
kingsfan76Commented:
yes.
you can use this "TOP #" clause to select the first # of records

"SELECT TOP 3 * FROM subcategories WHERE CatID=" & rscategories.Fields("CatID")
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.