Solved

Categories & subcategories

Posted on 2003-11-19
9
488 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How to solve this equation 3 48
JQuery Date Time picker not showing 29 95
add custom headers to my mail 2 42
Time/Date Query 11 34
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

20 Experts available now in Live!

Get 1:1 Help Now