?
Solved

Categories & subcategories

Posted on 2003-11-19
9
Medium Priority
?
496 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:kingsfan76
ID: 9782056
try

"SELECT *
FROM Categories
WHERE SubID IS NOT null AND CatID= "& Recordset1.Fields("CatID")
0
 

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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 informatio…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

764 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