Nested loop

[TobbeK]

Need some help with a nested loop.

This one (my example code below) prints out a correct 3 column HTML table with the recordset with proper opening and closing HTML tags AS LONG as the GRUOP BY clause is set by the topcategory Db table.

I think I may need a additional loop withing the existing one to handle the one-to-many database query without messing up the HTML table cells and rows.

EXAMPLE CODE:

SQL = "SELECT DISTINCT TC.topcategory, MC.middlecatego ry "&_
"FROM tbtopcategory TC,tbmiddlecate gory MC,tbconnectcat egory CC "&_
"WHERE TC.topcategoryI D = CC.topcategoryl ink "&_
"AND MC.tbmiddlecate goryID = CC.middlecatego rylink " &_
"GROUP BY TC.topcategory "
RS.Open SQL,Conn

Cols = 3

Response.Write( "<table width='570' border='1'>")

If Not RS.EOF then

Do Until RS.EOF
Response.Write( "<tr>")

For i = 1 To Cols

If RS.EOF then
Response.Write( "<td width='190'>xx" )
Else

Response.Write( "<td width='190'>")

' I NEED START OF LOOP FROM HERE

TopCat = RS("topcategory ")
If LastTopCat <> TopCat Then
Response.Write( ""&RS("topcateg ory") &"<br>")
Response.Write( "-----------------------------<br>")
LastTopCat = TopCat
End If

Response.Write( RS("middlecateg ory"))

' END OF LOOP TO HERE

RS.MoveNext

End If
Response.Write( "</td>")
Next

Loop
End If
Response.Write( "</tr>")
Response.Write( "</table>")



thanks in advance
Torbjorn

[markrawlingson]

You'll may have to be more clear as per what you're trying to do but it sounds like you're trying to generate a table but only have 3 records per row (<tr>) written out, instead of creating a <td> </td> for each record returned with your recordset, is that correct?

Sincerely,
Mark

[TobbeK]

Thank you for your reply


The DB query results contain a recordset from two tables ,from topcategory and middlecategory (a undercategory to topcategory). This is an one-to-many related output with only 1 topcategory per post, but many middlecategory per post. The problem is the middlecategory becourse it is continue creating new cells in the loop.

I need those "middlecategori es" to stay within the same cell as topcategory.



best regards
Torbjorn

[markrawlingson]

I think i understand what you're saying.. correct me if i'm wrong of course. You want basically 1 table row <tr> to display the top category, and then for each sub-category or middlecategory you want all those records to spit out inside the same <td> - rather than generating a new line.. I think that's accurate?

In a case like this you'd have to pull the top category records and the middle category records separate. Loop through the top category, displaying it's info, and then within that loop - grab the middle category data and display it's info.

This can get pretty encumbersome to the SQL server (and your production server as well) if you're looping through a recordset inside of a recordset loop (because if you have 10 middle categories for each top category you're opening and closing 10 recordsets for each time you loop through a record in your sub category. I've seen this done before, and in that particular case the programmer was causing over 300 recordsets to be opened and closed on a single page - ouch! The problem is accumulative as well, the more middlecategorie s per top category you have the worse the problem is.) - you can get around that inefficiency problem but it's fairly complicated - but IMO it's worth the complicated code. I've taken pages which load in 6 minutes down to loading in a few milliseconds.

Fortunately i've got oodles of experience in this "category" - sorry for the pun - hah! The way you're doing it right now looks like your recordset will return a cartesian product - have you ran it through your actual database?

Anyway, let me know if this is an accurate re-explanation of what you're trying to do and I'll give you some code examples on how you can go about it.

Sincerely,
Mark

Thank you for your reply


The DB query results contain a recordset from two tables ,from topcategory and middlecategory (a undercategory to topcategory). This is an one-to-many related output with only 1 topcategory per post, but many middlecategory per post. The problem is the middlecategory becourse it is continue creating new cells in the loop.

I need those "middlecategori es" to stay within the same cell as topcategory.



best regards
Torbjorn

[TobbeK]

Yes you have understand this (topcats and middlecats) within same cells.
And you have a point in the matter of DB effeciency, i have another approach to that where the (topcats and middlecats) stays withing same cells. I have used RS.Getrows and put everything into an array. I guess is more effecient instead of making new queries.

My problem here is another (i don't know how to break the table into columns, everything is printed out in one single row)

Here is the code


SQL = "SELECT DISTINCT TC.topcategory, MC.middlecatego ry "&_
"FROM tbtopcategory TC,tbmiddlecate gory MC,tbconnectcat egory CC "&_
"WHERE TC.topcategoryI D = CC.topcategoryl ink "&_
"AND MC.tbmiddlecate goryID = CC.middlecatego rylink " &_
"ORDER BY TC.topcategory ASC, MC.middlecatego ry ASC "
RS.Open SQL,Conn

arrDB = RS.GetRows()
iStart = LBound(arrDB,1)
iStop = UBound (arrDB,2)


Response.Write( "<table width='570' border='1'>")

For i = iStart to iStop

TopCat = arrDB(0, i)
If LastTopCat <> TopCat Then
Response.Write( "</td><td width='190'>")
TCat = arrDB(0, i) & "<br>"
Response.write TCat
LastTopCat = TopCat
End If

Response.write arrDB(1, i) & " , "

Next

Response.Write( "</tr>")
Response.Write( "</table>")


RS.Close
Conn.Close

I think i understand what you're saying.. correct me if i'm wrong of course. You want basically 1 table row <tr> to display the top category, and then for each sub-category or middlecategory you want all those records to spit out inside the same <td> - rather than generating a new line.. I think that's accurate?

In a case like this you'd have to pull the top category records and the middle category records separate. Loop through the top category, displaying it's info, and then within that loop - grab the middle category data and display it's info.

This can get pretty encumbersome to the SQL server (and your production server as well) if you're looping through a recordset inside of a recordset loop (because if you have 10 middle categories for each top category you're opening and closing 10 recordsets for each time you loop through a record in your sub category. I've seen this done before, and in that particular case the programmer was causing over 300 recordsets to be opened and closed on a single page - ouch! The problem is accumulative as well, the more middlecategorie s per top category you have the worse the problem is.) - you can get around that inefficiency problem but it's fairly complicated - but IMO it's worth the complicated code. I've taken pages which load in 6 minutes down to loading in a few milliseconds.

Fortunately i've got oodles of experience in this "category" - sorry for the pun - hah! The way you're doing it right now looks like your recordset will return a cartesian product - have you ran it through your actual database?

Anyway, let me know if this is an accurate re-explanation of what you're trying to do and I'll give you some code examples on how you can go about it.

Sincerely,
Mark

[markrawlingson]

Smart guy/gal! GetRows() is the way to go in a situation like this. Arrays in ASP are annoying, frankly, to traverse since they're (only) index based. I much prefer PHP's way of traversing arrays, but that's another story for another day...

I hope you have a solid understanding of Multi-dimensional arrays. If you do this should be no problem, if not it might be a little difficult and you may want to create the recordset inside a recordset situation if that's easier.
On to the code..

[code=asp]

<%
'Pull everything from the sub-category table, push this information into a multidimensiona l array and close this recordset. We'll now have an array with all of the sub categories within it. As we loop through the "master/primary" recordset we loop through this array and check to see if the ID of the top category matches the topcategoryID field in our array, if it does we display that information and move on to the next top category record.
sSQL = "SELECT * FROM middlecategory; "

Set rsTemp = Server.CreateOb ject("ADODB.Rec ordSet")

rsTemp.Open sSQL, Connection, adoConst, adoConst

aMiddleCategori es = rsTemp.GetRows( )
rsTemp.Close
Set rsTemp = Nothing


sSQL = "SELECT * FROM topcategory WHERE Condition = '" & sCondition & "';"

Set oRs = Server.CreateOb ject("ADODB.Rec ordSet")

oRs.Open sSQL, Connection, adoConst, adoConst

%>

<table border="0" cellpadding="0" cellspacing="0" class="indent" width="100%">
<tr>
<td>
Some Header<br />

<table border="0" cellpadding="2" cellspacing="1" width="100%">
<tr>
<td width="2%">
<strong>#</strong>
</td>
<td>
<strong>Top Category</strong>
</td>
</tr>
<tr>
<td colspan="100%">
<hr />
</td>
</tr>
<%
If oRs.EOF = True Then
%>
<tr>
<td colspan="100%">
NO Top Categories!
</td>
</tr>

<%
End If
Do Until oRs.EOF
If IsNull(iRecord) Then
iRecord = 1
Else
iRecord = iRecord + 1
End If
%>
<tr>
<td>
<strong><%=iRec ord%>.</strong>
</td>
<td class="smallpri nt">
<strong><% =oRs("topcatego rytitle") %></strong>
</td>
</tr>
<%
'here's where the magic happens. Now, of course this will create a new <TR> for each sub-category which pertains to the master/primary category - but it formats better. You can play around with the HTML of course if it's not what you're aiming for.
If IsArray(aMiddle Categories) Then
For i = 0 To UBound(aMiddleC ategories, x)
'(x = the number of fields your recordset returned. If you return SELECT field1, field2, field3 FROM table - then x = 3 because you returned 3 fields.)
If aMiddleCategori es(x,i) = oRS("ID") Then
'(x=the position of the topcategoryID field returned to the array in your recordset. In the example above, Field1 would be 0, field2 would be 1, field3 would be 2, etc etc)
'oRS("ID") is the relational field in the top category table that relates to topcategoryID in the middle category table
%>
<tr>
<td>
&nbsp;
</td>
<td colspan="100%" style="padding-left:30px;">
<strong><% =aMiddleCategor ies(x,i)%></strong> <strong></strong>
</td>
</tr>
<%
End If
Next
ElseIf aMiddleCategori es <> "" Then
%>
<tr>
<td>
&nbsp;
</td>
<td colspan="100%" style="padding-left:30px;">
<strong><% =aMiddleCategor ies %></strong> <strong></strong>
</td>
</tr>
<%
End If
oRS.MoveNext
Loop
oRs.Close
Set oRs = Nothing
%>
<tr>
<td colspan="100%">
<hr />
</td>
</tr>
</table>
</td>
</tr>
</table><br /><br />
[/code]

So the general idea here is..

1. Open a recordset to your sub-category table and push all the data into a multidimensiona l array. Close the recordset.
2. Open your recordset to the top category table
3. Create your table header information
4. loop through your top category recordset and display the top category title information, etc.
5. create a nested loop, looping through the multidimensiona l array, spitting out the sub category information that is pertinent to the particular top category record we're dealing with
6. Close your recordset
7. Set your table close information </table>, etc

Hope that gives you some insight.

Sincerely,
Mark

[TobbeK]

Thanks a lot. I am really grateful for all the help I can get.

I am creating a small business directory where a small spot of links (URLs) is neccessary. You have probably seen it before, nothing fancy ( Yahoo like) topcategory link and undercategory link. But it was trickier than I first thought.

I will take a deep look into your code and get back here with feedback.

best regards
Torbjorn

[TobbeK]

Alomost there, used by the current db query and arrays

Missing part is that the (middlecategory ) don't stay within the same cells as the topcategories.



SQL = "SELECT DISTINCT TC.topcategory, MC.middlecatego ry "&_
"FROM tbtopcategory TC,tbmiddlecate gory MC,tbconnectcat egory CC "&_
"WHERE TC.topcategoryI D = CC.topcategoryl ink "&_
"AND MC.tbmiddlecate goryID = CC.middlecatego rylink " &_
"ORDER BY TC.topcategory ASC, MC.middlecatego ry ASC "
Set RS = Server.CreateOb ject("ADODB.rec ordset")
RS.Open SQL,Conn

arrDB = RS.GetRows()
iStart = LBound(arrDB,1)
iStop = UBound (arrDB,2)

Response.Write( "<table width='570' border='1'>")

For i = iStart to iStop
If Col = 1 then
response.write( "<tr>")
End if
Response.Write( "<td>")

TopCat = arrDB(0, i)
If LastTopCat <> TopCat Then
'Response.Write ("<td>")
TCat = arrDB(0, i) & " <br>"
Response.write TCat
LastTopCat = TopCat
End If

Response.write arrDB(1, i) & " , "

Response.Write( "</td>")
If Col = 3 then
Response.Write( "</tr>")
Col = 0
End If
Col = Col + 1
Next

Response.Write( "</tr>")
Response.Write( "</table>")


RS.Close
Conn.Close

[TobbeK]

Alomost there, used by the current db query and arrays

Missing part is that the (middlecategory ) don't stay within the same cells as the topcategories.

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"ORDER BY TC.topcategory ASC, MC.middlecategory ASC "
Set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn

arrDB = RS.GetRows()
iStart = LBound(arrDB,1)
iStop = UBound (arrDB,2)

Response.Write("<table width='570' border='1'>")

For i = iStart to iStop
If Col = 1 then
response.write("<tr>")
End if
Response.Write("<td>")

  TopCat = arrDB(0, i)
  If LastTopCat <> TopCat Then
  'Response.Write("<td>")
  TCat = arrDB(0, i) & " <br>"
  Response.write TCat
  LastTopCat = TopCat
  End If

  Response.write arrDB(1, i) & " , "

Response.Write("</td>")
If Col = 3 then
Response.Write("</tr>")
Col = 0
End If
Col = Col + 1
Next

Response.Write("</tr>")
Response.Write("</table>")


RS.Close
Conn.Close