Solved

Categories & subcategories

Posted on 2003-11-19
9
490 Views
Last Modified: 2012-05-04
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

0
Comment
Question by:msolomos
  • 4
  • 4
9 Comments
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9781766
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
 

Author Comment

by:msolomos
ID: 9781928
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
 

Expert Comment

by:landofcash
ID: 9781937
I think that the best db structure for this is

ID    ParentID    Category

(ParentID links with ID)
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9782056
try

"SELECT *
FROM Categories
WHERE SubID IS NOT null AND CatID= "& Recordset1.Fields("CatID")
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:msolomos
ID: 9782271
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
 
LVL 12

Accepted Solution

by:
kingsfan76 earned 125 total points
ID: 9782802
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
 

Author Comment

by:msolomos
ID: 9802191
>>> 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
 

Author Comment

by:msolomos
ID: 9802200
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
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9806388
yes.
you can use this "TOP #" clause to select the first # of records

"SELECT TOP 3 * FROM subcategories WHERE CatID=" & rscategories.Fields("CatID")
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now