Looping Through Record "Categories" Based on Another Table 1

[dlongnecker]

I'll try to draw this out the best I can.

I have two tables, discipline (reading, math, writing, etc) and the actual information table. I want to do a loop through the information table that looks all of the records, determines the dicipline and "groups" them together when it displays

Example, the discipline table showed that a discipline id (d_it for this example) of 1 was Reading; "for each" of the d_it = 1 records in the data table, write them to the screen.

Reading (d_it = 1)
Level
Target
Focus
Strategy
Resource

"next"

Then, loop through the d_it = 2, 3, 4, etc.

I originally designed these disciplines as hard coded (Math, Science, Reading, Writing, etc); however, the customer wants to be able to update the table (which also populates a combo box that users are "editing" these from) if the local government decides to add new curriculum areas.

I've tried different for each loops, while not, etc... no luck and/or I'm just not getting the code correct.

Let me know if you need more details or a better explaination... and I'll keep updating the thread as I think of things...

Regards,

David

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[Sheco]

Wichita Public Schools, USD 259

Good call. Knowing Kansas they might remove Science and replace it with Theology!

But seriously, I assume there is something in the second table... the one with the actual information, that relates back to the disciplines... like a foriegn key?

There is more than one way to do this but the best way will depend on what your database structure.

 

[steven290]

so basically you discipline table looks like this

d_it | discipline
1 | reading
2 | reading
1 | math
1 | science
5 |reading

you could simpply do you sql like this

select * from disciplinetb order by d_it

then just do a loop

 

[bslintx]

like sheco mentioned...please put BOTH tables in post and show the relationship since you are "grouping" (joining) them...then we can see if your database is structured correctly..thanks

 

[dlongnecker]

Sheco - ROFL... I'm a techie... not a curriculum. My only comment - "I'm glad I've already graduated..."

Here's the basic structure:

tblDisc

disc_id     disc_name
1           reading
2           mathematics
3           writing
...

tblSection6

id   disc_id    target_id   focus_id ...
1    1          2           7
2    1          3           4
3    2          1           5
4    1          1           7

So, for the output, using what I had above:

Disc_ID 1 : Reading
  ID: 1
         Target: 2       Focus: 7
  ID: 2
         Target: 3       Focus: 5
  ID: 4
         Target: 1       Focus: 7

Disc_ID 2: Math
  ID: 3
         Target: 1       Focus: 5

Disc_ID 3: Writing
  None

...

Steven290 - That's how my SQL statement is written... perhaps some pseudo code on the loop statement.. I think that's what's messing me up. (T_T)

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[dlongnecker]

bslintx - See the latest post. Those are just pseudo recreations; however, the key fields are the same.

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[steven290]

seems like you would just do a

select * from blSection6 order by Disc_ID

 

[bslintx]

dlongnecker...now how did you know he had the foreign key and structure to the other table set up currectly by simply looking at the , yes i am aware -it's pseudocode? hmmm inquiring minds would like to know hence...look at 2nd post

 

[steven290]

then to display just do a
using the getrows method

response.write "Reading<br>"
for i=lbound(rows,2) to ubound(rows,2)
if rows(1,i)="1" then
response.write "ID: "&rows(0,i)&" | "&"Target: "&rows(2,i)&" | "&"Focus: "&rows(3,i)&"<br>"
end if
next

response.write "Math<br>"
for i=lbound(rows,2) to ubound(rows,2)
if rows(1,i)="2" then
response.write "ID: "&rows(0,i)&" | "&"Target: "&rows(2,i)&" | "&"Focus: "&rows(3,i)&"<br>"
end if
next

response.write "Writing<br>"
for i=lbound(rows,2) to ubound(rows,2)
if rows(1,i)="3" then
response.write "ID: "&rows(0,i)&" | "&"Target: "&rows(2,i)&" | "&"Focus: "&rows(3,i)&"<br>"
end if
next

 

[bslintx]

lol....disregard dlong...i thought it was another user ;-)


steven? would this allow the user to update if necessary...or join then order by Disc_ID?

 

[dlongnecker]

Steven290 -

Okay, that makes sense, my question; however, is once I have those in order... how can I tell the code to group those into the correct section headings?

Not only do I need Reading to all be grouped together, I would like there to be a heading called "Reading" above that (I have a function that I can pull the name from the discipline table based on the ID)... Like I said, I think I just need to figure out how/where to place the loops.

Here's the SQL Connection Code from the Page

CIPQuery = "SELECT * from plans_discipline ORDER BY disc_id"
Set Disc = CIPConnect.Execute(CIPQuery)

And the code that I want looped based on the discipline. The ImportSection6 Connection object is called above as:

CIPQuery = "SELECT * from plans_section6 where cip_id = '" & cip_id & "'"

The CIP_ID is a variable passed to each page in the querystring and read at the top of each page.

The functions you see are converting the numeric to the "friendly names"... however, we're only storing the numerics behind the code due to statistical analysis this information will undergo...

<%
If Not ImportSection6.EOF Then

While Not ImportSection6.EOF
	%>
		<tr><th colspan="5"><%=LookupDisc(ImportSection6("disc_id"))%> Strategies</th></tr>
		<tr>
		<th width="20%" class="subheader">Level</th>
		<th width="20%" class="subheader">Target Area</th>
		<th width="20%" class="subheader">Focus Area</th>
		<th width="20%" class="subheader">Strategy</th>
		<th width="20%" class="subheader">Resources</th>
		</tr>
		<tr>
		<td><p><%="<a href='edit_cip_section6.asp?cip_id=" & cip_id & "&strategy_id=" & ImportSection6("strategy_id") & "&action=delete' onclick=""javascript:return confirm('Are you sure you want to delete this strategy?')"">" & LookupLevel(ImportSection6("level_id")) & "</a>"%></p></td>
		<td><p><%=LookupTarget(ImportSection6("target_id"))%></p></td>
		<td><p><%=LookupFocus(ImportSection6("focus_id"))%></p></td>
		<td><p><%=LookupStrat(ImportSection6("strat_id"))%></p></td>
		<td><p><%=ImportSection6("resources")%></p></td>
		</tr>
	<%

	ImportSection6.MoveNext
	Wend
Else
	' Nothing in the base for this CIP...
        response.write("This CIP does not have any existing strategies.")
End if

This code "While Not Empty" in the recordset loops through each of them. The "discipline loop" should go in there, but, everytime I try to loop, it displays nothing.

The code I was trying for the loop:

For Each ImportSection6("disc_id")
 ...
Next

Bslintx - I'm not sure I understand what you're saying. Who is "he"? Steven in his example? If you'd like a SQL Query dump of the table, I can post it... just figured posting the columns that I'm attemping to loop through would be relevant. What about the second post?

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[steven290]

try my getrows method i believe it answers your question

just replace the response.write "reading<BR>" with table heads, etc

 

[dlongnecker]

Let me do a bit of research on the GetRows function; I'm not as familiar with using some of the ADODB commands (ack, learning curve. ^_~).

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[bslintx]

nevermind me long, "he" was you but didn't realize it was you and obviously you know if you had a foreign key. the second post was wondering the same is all...anyways you're in good hands w/ steven...you'll rolling in no time soon...good luck!

 

[steven290]

set rs=conn.execute(sq)
if not rs.eof then
rows=rs.getrows 'get row grabs all data as an array
rs.close
conn.close
set rs=nothing
set conn=nothing
end if

for i = lbound(rows,2) to ubound(rows,2) ' loop rows start to finish

 

[dlongnecker]

Okay, bit of tweaking and I get

Section 6 - Strategies 
Reading
ID: 11 | Target: 1 | Focus: 1
Math
ID: 11 | Target: 1 | Focus: 1
Writing
ID: 13 | Target: 2 | Focus: 2

*dance*

Okay... now... to make this semi-more painful... this is what I originally planned on doing (though without GetRows... that's a new trick!) until they wanted those disciplines to be dynamic. Example, the code on this page shouldn't "know" what the disciplines are... just read what disciplines are "available" in the tblDisc, then group them accordingly on this page. Example, tomorrow, they could reword Writing to "Use of Language" or something. The ID remains the same, just the display changes. Or, if they add Theology, it'll notice the new discipline and check the "data" table for that discipline.

Would I do another one of the GetRows against the Discipline table?

Steps:

1. What disciplines are available?
okay, we have 3.

2. What's the first?
reading (disc_id = 1)

3. OKay, pull all of the records that match disc_id = 1 from the section6 table, display them nicely.

4. What's the next?
math (disc_id = 2)

5. repeat #3 for Math

6. Repeat #4 and keep going until you're out of disciplines.

As you can probably guess... I'm a visual kinda guy... I should take a photo of the whiteboard with these ideas on it in my office. Odd, astranged arrows pointing everywhere.

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[vongrunt]

If I understand correctly...

Get everything with single query:

select A.disc_id, A.disc_name, B.id, B.target_id, B.focus_id
from tblDisc A
left outer join tblSection6 B on A.disc_id=B.disc_id
order by A.disc_id, B.id

Then use 2 nested loops (oRS is recordset constructed from above query):

Dim iDiscID
Do While not oRS.Eof
	iDiscID = oRS("disc_id").value
	
	' Write group header here

	Do While not oRS.Eof
		If iDiscID <> oRS("disc_id").value Then Exit Do
		
		If IsNull(oRS("id")) Then 'primary key in joined table (tblSection6)
			' None - empty group, LEFT JOIN returned NULL.
		Else
			' write detail row here
		End If	
		
		oRS.moveNext
	Loop
	
	' Write group footer here
Loop	
' close/destroy oRS

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[dlongnecker]

bslintx - *stares blankly and confused, slightly drooling*

LOL... No worries... just my already confused, sorta burned mind was even more confused. Thanks though. ^_~

---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[steven290]

try something like this

set rs=conn.execute(sq)
if not rs.eof then
rows=rs.getrows 'get row grabs all data as an array
rs.close
set rs=nothing
end if
set rs2=conn.execute("select * from tbldisc")

do while not rs2.eof
	response.write rs2("disc_id")
	for i=lbound(rows,2) to ubound(rows,2)
	if rows(1,i)=rs2("disc_name") then
	response.write "ID: "&rows(0,i)&" | "&"Target: "&rows(2,i)&" | "&"Focus: "&rows(3,i)&"<br>"
	end if
	next
rs2.moveNext
loop

rs2.close
conn.close
set rs2=nothing
set conn=nothing

 

[dlongnecker]

Okay... I appreciate everyone bearing with me...

I verbatium used Steven's code (pointing to the right tables)...

<%
set rs = cipconnect.execute("select * from plans_Section6 where cip_id = '" & cip_id & "'")
if not rs.eof then
	rows = rs.getrows()
end if

set rs2 = cipconnect.execute("select * from plans_discipline ORDER by disc_name")

do while not rs2.eof
	response.write(rs2("disc_name") & " - " & rs2("disc_id") & "<br/>")
	for i=lbound(rows,2) to ubound(rows,2)
[highlight]response.write(rows(2,i) & " - " & rs2("disc_id") & "<br>")[/highlight]
if rows(2,i)=rs2("disc_id") then
			response.write "ID: "&rows(0,i)&" | "&"Level: "&rows(3,i)&" | "&"Target: "&rows(4,i)&" | "&"Strategy: "&rows(5,i)&"<br>"
		end if
next
	rs2.movenext
loop

%>

.. and it pulls in the discipline names... but that's it! (T_T).

So, I tossed in the highlighted section to see if the numbers matched the database

Mathematics - 3
1 - 3
3 - 3
2 - 3
1 - 3
3 - 3
Reading - 1
1 - 1
3 - 1
2 - 1
1 - 1
3 - 1
Writing - 2
1 - 2
3 - 2
2 - 2
1 - 2
3 - 2

Which, that's correct! Any idea why that If statement isn't picking them up?





---
David R. Longnecker
Web Developer
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259