Solved

Categories & subcategories

Posted on 2003-11-19
9
494 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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