Nested loop - Page 3

Question

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

Not the code on this page, the code on page 6, the second to last code I sent you.

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

You probably don't even need middlecategory. If you wish, you should do it this way:

SQL = "SELECT TC.topcategory, DISTINCT(MC.middlecategory) "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory"

This way it will grab only the unique sub names (which you don't want duplicates of anyway). No need for group by, as when you have uniques, grouping 1, 2, 3 will turn out as 1, 2, 3. It's pointless.

Glad to see its working. Show me? :)

TobbeK commented: One of a kind +1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

SheSaidImaPregy 28 Veteran Poster · Answer 1 · 2008-02-16T00:15:17+00:00

Let's loose all the stupid loops.

Oh please tell me you "Dim LastTopCat" at the top of the page right?

Try this:

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")
	TopCat = RS("topcategory")
	MiddleCat = RS("middlecategory")
	LastTopCat = ""
	i = 3

	Do While Not RS.EOF
		If i = 3 Then
			Response.Write("<tr>")
			i = 0
		End If

		If TopCat <> LastTopCat Then
			Response.Write ("<td width=""190"">")
			Response.Write(TopCat & "<br />")
			Response.Write("-----------------------------")
			LastTopCat = TopCat
		End If

		If TopCat = LastTopCat Then
			Response.Write(MiddleCat)
		End If

		RS.MoveNext

		If TopCat <> RS("topcategory") Then
			TopCat = RS("topcategory")
		End If

		MiddleCat = RS("middlecategory")

		If TopCat <> LastTopCat Then
			Resposne.Write("</td>")
			i = i + 1
		End If

		If i = 3 Then
			Response.Write("</tr>")
		End If
	Loop

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

TobbeK 0 Junior Poster · Answer 2 · 2008-02-16T00:16:16+00:00

This is what it hopefully will looks like

This is faked table

TobbeK 0 Junior Poster · Answer 3 · 2008-02-16T00:19:54+00:00

TobbeK 0 Junior Poster

16 Years Ago

Yes, I did now. But some error there is.

TobbeK 0 Junior Poster · Answer 4 · 2008-02-16T00:22:22+00:00

There is two recordsets available, one for topcats and one for middles

One by each SQL query
RS and RS1

SheSaidImaPregy 28 Veteran Poster · Answer 5 · 2008-02-16T00:24:38+00:00

wait, you didn't declare the variable before? Run the last code where you said it looks good.

The reason why it kept skipping over is that it didn't keep LastTopCat in memory, therefore when it compared, it compared TopCat to nothing, hence failing.

Dim LastTopCat at the top of the page and try the 2nd to last code I gave you.

TobbeK 0 Junior Poster · Answer 6 · 2008-02-16T00:35:27+00:00

TobbeK 0 Junior Poster

16 Years Ago

Nope, nothing at all, All variables are dimmed

SheSaidImaPregy 28 Veteran Poster · Answer 7 · 2008-02-16T00:36:28+00:00

I don't know then. It should all work just fine, especially with the 2nd to last code I gave you. I mean, it looped through all the top cats, caught the middles, then went on. Now nothing at all?

TobbeK 0 Junior Poster · Answer 8 · 2008-02-16T00:37:20+00:00

The only wrong I can find was Resposne.Write and changed that to Response.Write

TobbeK 0 Junior Poster · Answer 9 · 2008-02-16T00:40:02+00:00

TobbeK 0 Junior Poster

16 Years Ago

Hang on.....

TobbeK 0 Junior Poster · Answer 10 · 2008-02-16T00:42:37+00:00

I reloaded it a couple of times and this is the output

And row 47 is this one
If TopCat <> RS("topcategory") Then

SheSaidImaPregy 28 Veteran Poster · Answer 11 · 2008-02-16T00:48:38+00:00

This one:

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")

	Do While Not RS.EOF
		Response.Write("<tr>")
		i = 0
		TopCat = RS("topcategory")
		MiddleCat = RS("middlecategory")

		Do While i <> 3
			If Not RS.EOF Then
				If Not LastTopCat = TopCat Then
					Response.Write ("<td width=""190"">")
					Response.Write(RS("topcategory") & "<br />")
					Response.Write("-----------------------------")
					LastTopCat = TopCat
				End If
					
				Response.Write("<br />" & MiddleCat)

				RS.MoveNext

				If Not RS.EOF Then
					TopCat = RS("topcategory")
					MiddleCat = RS("middlecategory")
				End If

				If Not LastTopCat = TopCat or RS.EOF Then
					Response.Write("</td>")
					i = i + 1
				End If
			Else
				Response.Write("<td width=""190""></td>")
				i = i + 1
			End If
		Loop

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

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

TobbeK 0 Junior Poster · Answer 12 · 2008-02-16T00:49:51+00:00

That one (on page 6) is also dimmed and that one is working in the matter of 1 topcat and 1 middlecat

SheSaidImaPregy 28 Veteran Poster · Answer 13 · 2008-02-16T00:56:43+00:00

The lovely good ole days of Classic ASP.

No reason why something shouldn't work, but there's the error.

TobbeK 0 Junior Poster · Answer 14 · 2008-02-16T01:05:25+00:00

Yep, I can agree with that

The reason why I split the SQL query into 2 queries was the repeating issue. You will only get the first middlecat along with each topcategory within the query (SQL). The (SQL1) combined with (SQL) was meant as a workaround to pick out every middles connected to each topcat without the need of repeating topcats or middlecats.

Maybe the solution is to optimize the SQL !? I don't know?

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

SQL1 = "SELECT middlecategory " &_
"FROM tbmiddlecategory WHERE tbmiddlecategoryID = '" & RS("MCLink") & "'"
set RS1 = Server.CreateObject("ADODB.recordset")
RS1.Open SQL1,Conn

SheSaidImaPregy 28 Veteran Poster · Answer 15 · 2008-02-16T01:21:28+00:00

Oh brother, try this code and then I will tell you what's wrong.

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")

	Do While Not RS.EOF
		Response.Write("<tr>")
		i = 0
		TopCat = RS("topcategory")
		MiddleCat = RS("middlecategory")

		Do While i <> 3
			If Not RS.EOF Then
				If Not LastTopCat = TopCat AND Len(TopCat) > 3 Then
					Response.Write ("<td width=""190"">")
					Response.Write(RS("topcategory") & "<br />")
					Response.Write("-----------------------------")
					LastTopCat = TopCat
				End If
					
				Response.Write("<br />" & MiddleCat)

				RS.MoveNext

				If Not RS.EOF Then
					TopCat = RS("topcategory")
					MiddleCat = RS("middlecategory")
				End If

				If (Len(TopCat) > 3 AND TopCat <> LastTopCat) or RS.EOF Then
					Response.Write("</td>")
					i = i + 1
				End If
			Else
				Response.Write("<td width=""190""></td>")
				i = i + 1
			End If
		Loop

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

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

SheSaidImaPregy 28 Veteran Poster · Answer 16 · 2008-02-16T01:24:15+00:00

Your databases aren't connected correctly. Your topcategory and middlecategory should be connected like this:

TopCat
- TopCatID
- TopCatName

MiddleCat
- MiddleCatID
- TopCatID
- MiddleCatName

They aren't, and because of that you are pulling empty values.

When you pull the TopCat Name, you prolly are able to pull 1 value with it.

You need a database restructure.

TobbeK 0 Junior Poster · Answer 17 · 2008-02-16T01:25:08+00:00

TobbeK 0 Junior Poster

16 Years Ago

Works with the usual 1 topcat 1 middlecat.

SheSaidImaPregy 28 Veteran Poster · Answer 18 · 2008-02-16T01:31:25+00:00

Yes it's what your database is pulling, not how the code handles it. You need a database redesign/restructure to better relate the data.

If it requires you for two queries to grab the information, then the information isn't related correctly.

If you want to see what I mean, run this code:

If Not RS.EOF Then
  Do Until RS.EOF
    Response.write(RS("topcategory") & " ~ " & RS("middlecategory") & "<br />")
    RS.MoveNext
  Loop
End If

TobbeK 0 Junior Poster · Answer 19 · 2008-02-16T01:33:02+00:00

The connecting table (tbconnectcategory) which holding the foreign keys looks like this:

TbConnectCategory
- topcategorylink <> tbtopcategoryID
- middlecategorylink <> tbmiddlecategoryID

That should be enough as long as keys are unique and the query is intelligent enought (he he), depends who the writer is. Maybe I should rewrite my SQLs

TobbeK 0 Junior Poster · Answer 20 · 2008-02-16T01:39:35+00:00

MAN you are absoluteley right.

As talked about before (GROUP BY) cluse was set to top category. I deleted that one and VOILA

Hang on

CHECK THE ATTACH - IT IS NOT A FAKE IT'S FOR REAL

SheSaidImaPregy 28 Veteran Poster · Answer 21 · 2008-02-16T01:41:19+00:00

Oh okay that's how you have it set. Yeah that is fine. Not highly recommended, but it works. Okay then try this query:

SQL = "SELECT TC.topcategory, MC.middlecategory "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory "

You have to lost the DISTINCT, otherwise you will only grab One Top Category, which means you only grab one MiddleCategory. Drop the destinct, and no need for the MCLink.

And now your thing should work.

SheSaidImaPregy 28 Veteran Poster · Answer 22 · 2008-02-16T01:43:16+00:00

NO NO, you have to keep GROUP BY clause in there, if you don't, your records won't go in the right places, and you'll have duplicate columns!

You just need to lose the DISTINCT. DISTINCT grabs only the unique ones. So if you have 10 TopCats named NameMyTopCat, it will only pull 1. Since it only pulls 1, it will only pull one MiddleCat. That's where you failed.

TobbeK 0 Junior Poster · Answer 23 · 2008-02-16T01:46:33+00:00

Yes this was the right one, added middlecat as well to GROUP BY and it is working

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

TobbeK 0 Junior Poster · Answer 24 · 2008-02-16T01:51:12+00:00

I don't how to thank you enough for the most valued and professional help any can ever get on any forum.

TobbeK 0 Junior Poster · Answer 25 · 2008-02-16T01:53:16+00:00

This is the output (before I prints it out and hang it in some golden frame)

You probably don't even need middlecategory. If you wish, you should do it this way:
SQL = "SELECT TC.topcategory, DISTINCT(MC.middlecategory) "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory"
This way it will grab only the unique sub names (which you don't want duplicates of anyway). No need for group by, as when you have uniques, grouping 1, 2, 3 will turn out as 1, 2, 3. It's pointless.
Glad to see its working. Show me? :)

SheSaidImaPregy 28 Veteran Poster · Answer 26 · 2008-02-16T01:57:06+00:00

Next time, remind me to look at the query first lol.

You're welcome.

TobbeK 0 Junior Poster · Answer 27 · 2008-02-16T02:00:22+00:00

Dont hesitate to contact me if there is anything I can do for help. I will be here now and then.

Again thanks and many greetings from Stockholm

sincerely
Torbjorn