Loop within loop for many-to-many related table 1

[taylorantone]

I'll try to say this in as few words as possible so it's not any more confusing than it currently is.

I have a database in Access with a table for DVD's and a table for Genres. Each of these tables has a one-to-many relationship with a linking table--I needed a way for a movie to be assigned to multiple genres, while also allowing a genre to be assigned to multiple movies. The relevant fields are laid out as follows:

tblDVD
- dvdID (autonumber--related one-to-many with dvdID in the next table)
- title

tblDVDGenres
- dvdID
- genreID

tblGenres
- genreID (autonumber--related one-to-many with genreID in the table above)
- genre

There are the three tables essentially givin me the many-to-many resulting relationship that I need.

Now, I have a current ASP page that lets you choose a genre and a sorting field (also whether to sort asc or desc) from a database-populated drop-down and the table lists the DVD's that are assigned to that genre. Here's the current SQL statement:

sqlqrygenre = "SELECT tblGenres.genre, tblDVD.title, tblDVD.notes, tblDVD.genres, tblDVD.year, IIf([borrowerID]=28,'Yes','No') AS [in] FROM tblGenres INNER JOIN (tblDVD INNER JOIN tblDVDGenre ON tblDVD.dvdID = tblDVDGenre.dvdID) ON tblGenres.genreID = tblDVDGenre.genreID WHERE genre = '" & Request.Form("selgenre") & "' " & Request.Form("sort") & " " & Request.Form("order") & ";"

Here's a link to the current page in case that helps me explain this any better:

http://oneant.dyndns.org/dvdlist/searchgenreinput.asp

Here's what I'm trying to do. As the query loops through the recordset and displays each record, I want to have a field called "genres" pull in all of the related genres to the DVD on the current record and separates them with a comma.

Just taking a stab at it, I'd think it would need to put something like this into the data for the "genres" field (pseudo-code below--if I knew the actual code I wouldn't be posting ):

Do While NOT oRSqrygenre.EOF
Response.Write oRSqrygenre("genre") & ", "
oRSqrygenre.MoveNext
Loop

Anyone know how to get this work? I know there's a way, but I'm not a very advanced ASP guy so I run into walls pretty easily.

Thanks!!


UPDATE!!!!!!!!!!!!!!!!!!!


Man, I'm right on the edge of getting this, but there's some small thing I'm doing wrong. Here are the changes I made:
- created a new query in Access with just the information I need
- created a variable called "vartitle"
- created another recordset object for this new query
- here's the SQL statement for it:

sqlgenresperdvd = "SELECT tblDVD.title, tblDVD.notes, tblDVD.year, tblDVD.borrowerID, tblDVDGenre.genreID FROM tblDVD INNER JOIN tblDVDGenre ON tblDVD.dvdID = tblDVDGenre.dvdID WHERE tblDVD.title = '" & vartitle & "';"

- the field that writes the title of each DVD now looks like this:

<% vartitle = oRSqrygenre("title")%><%=vartitle%>

I had the variable initialized first and then used the variable to write the title to make sure that the variable was initializing successfully--and it is
- the loop for the genres field looks like this:

<%
Do While NOT oRSgenresperdvd.EOF
Response.Write oRSgenresperdvd("genreID") & ", "
oRSgenresperdvd.MoveNext
Loop
%>

Now, I've managed to remove all of the errors I was receiving and the search pulls up the matching records, but no genres are being written into the genres field. I don't get it. Like I said, I feel like I'm right on the edge of getting this to work, but there's something stupid I'm missing or doing wrong.

Here's what's happening:

http://oneant.dyndns.org/development/searchgenreresults.asp

Anyone out there with a decent ASP/SQL background to give me an idea?

 

[TJRTech]

I suspect that the SQL query you are using for getting the list of movies given the user selections either DOESN'T include a GENRE column or if it does, when you query that data for use in generating your table of returned movies you are referring to it via a column name that doesn't match that of the column in the query.

For example, you may have called the "GENRE" in the query, but when building your table HTML you incorrectly called it "GENER", or somesuch.

TR

 

[taylorantone]

I know it's asking a lot, but would seeing all of the sql statement (all of the page code perhaps) help to tell me what's going on?

Are you saying that I need a column called genres in the tblDVD (the one that is used to get the movie title, notes, year data in the main results table) to get this to work? Even though I really don't want to store any data in that column whatsoever? I just want this thing to loop through, list a movie, then, when it hits the genres column, loop through the genres that this movie belongs to and write them, then hit the next movie and the next, etc, etc.

 

[TJRTech]

When you say loop through? Why are you looping?

Seems to me that your page with the dropdown should have a query that lists the Genre's from the tblGenre table for selecting;

and the page that lists the movies found and their genre's should use a query that joins the three tables together.

Thus, the latter table there is no looping and sub-looping. The joins do all that for you.

But, as you have said, the above will return multiple records for a single DVD *IF* it is associated with several genres.

What I would do is add an ORDER BY in the query so that you order by dvdID.

Then, all the like dvdIDs would be grouped together. Then, in your ASP code in the loop where you make the table keep track of the "last_dvdID", and whenever it changes you know you have a new dvd. Upon a DVD transition, write the previous DVD info, along with the concatenated list of genres.

I may not be explaining it properly, but I think the key is to use an order by and to return a single record for each DVD title and genre combination, and to concatenate the genre list as you go and render the dvd entry one moving from one dvd band or records to the next.

TR

 

[taylorantone]

The page does have a drop down that is populated from tblGenres.genre. That was the easy part.

Okay, I'm trying to work from the basics forward on this one. I've now got a page that pull the tblDVD.title, tblDVD.notes, tblGenre.genre, tblDVD.year based on a query I created in Access:

sqlsearchgenre = "SELECT tblDVD.title, tblDVD.notes, tblGenres.genre, tblDVD.year, IIf([borrowerID]=28,'Yes','No') AS [in] FROM tblGenres INNER JOIN (tblDVD INNER JOIN tblDVDGenre ON tblDVD.dvdID = tblDVDGenre.dvdID) ON tblGenres.genreID = tblDVDGenre.genreID ORDER BY tblDVD.title, tblGenres.genre;"

The table lists each movie for every genre it belongs to.

1. How do I keep track of the last tblDVD.dvdID (or can I use the tblDVD.title)? Would a variable do the trick?

2. The concatenation part is where I'm having the most trouble. I'm not trying to get you to write this for me, but any details you can provide are GREATLY appreciated!

 

[DreXor]

well considering you have a many:many relationship, you can get away with a little here, you dont necessarily need to force use of the tbldvd to get the dvdid, ( remember that's also in your tbldvdgenres ) you can use the didID from there with teh same results

secondly sometimes the easiest or starting route is the least obvious yet sometimes the simplest

meaning, you can select all movie titles, and cycle through them (RS1) then query the dvdgenre (RS2) for all dvdid matches, then translate each of these genreID's to genres (RS3)

now this can be cut down a little and by looking at the explaination you should be able to catch them in order of heirarchy like RS2 & 3 can be combined in the past when i've had seeminly impossible SQL joins in 2 directions, i just pull the bulk data from one side and array it for the time being ( 1 temp connection using getrows ) then search the array for the genreid, and spew out the genre

hopefully the info helps, email notice is on if you have more questions ps .. your psuedo code was non psuedo

DreX
aKa - Robert

 

[taylorantone]

Okay, I hit upon a partial solution RIGHT before I got the e-mail about your post DreXor.

Trying to start with the basics first, I wanted to get a table that listed every record in the database once for every genre it was associated with. For example:
Born On The 4th of July Drama
Born On The 4th of July War
etc....

My SQL statement looks like this:

SELECT tblDVD.title, tblDVD.notes, tblGenres.genre, tblDVD.year, IIf([borrowerID]=28,'Yes','No') AS [in] FROM tblGenres INNER JOIN (tblDVD INNER JOIN tblDVDGenre ON tblDVD.dvdID = tblDVDGenre.dvdID) ON tblGenres.genreID = tblDVDGenre.genreID ORDER BY tblDVD.title, tblGenres.genre;

Once I had that, instead of writing out the genre as in the above table, I added a loop containing the following so that it concatenated the genres into the genre field:

<%
do while (vartitle = oRSsearchgenre("title"))
Response.Write oRSsearchgenre("genre") & ", "
oRSsearchgenre.MoveNext
Loop
%>

A SUCCESS!!!

However, now I need to re-add the genre search drop-down. Am I correct in assuming that, once I add that and modify the SQL query with a WHERE clause filtering out the movies with non-matching genres, it will foul up the way I have the page set up and I'll just get the genre that I searched for listed in the concatenated genre field? From your post above it sounds as though I can do the testing for the matching genre in my loop somewhere so that my SQL statement doesn't limit the records returned.

Thanks guys! It sucks being a total n00b at something and really wish you weren't. I just started all this on Thursday, so when I say I'm a n00b I REALLY AM!

 

[taylorantone]

Oh, the page code looks like this:

<html>

<head>
	<title>Genre Search</title>
	<link rel="stylesheet" type="text/css" href="../style.css">
	<link rel="stylesheet" type="text/css" href="../iframe.css">
</head>

<body>

<%
dim vartitle
dim oRSsearchgenre

set oRSsearchgenre = Server.CreateObject("ADODB.Recordset")

sqlsearchgenre = "SELECT tblDVD.title, tblDVD.notes, tblGenres.genre, tblDVD.year, IIf([borrowerID]=28,'Yes','No') AS [in] FROM tblGenres INNER JOIN (tblDVD INNER JOIN tblDVDGenre ON tblDVD.dvdID = tblDVDGenre.dvdID) ON tblGenres.genreID = tblDVDGenre.genreID ORDER BY tblDVD.title, tblGenres.genre;"

oRSsearchgenre.open sqlsearchgenre, "DSN=dvdlist"
%>

<table width="100%" border="0" cellpadding="3" cellspacing="0" height="74">
  <th>
    <tr>
      <td bgcolor="#7B92C1" width="300" height="19"><font class="normaltext" color="#FFFFFF"><b>title</b></font></td>
      <td bgcolor="#7B92C1" width="400" height="19"><font class="normaltext" color="#FFFFFF"><b>notes</b></font></td>
      <td bgcolor="#7B92C1" width="300" height="19"><font class="normaltext" color="#FFFFFF"><b>genres</b></font></td>
      <td bgcolor="#7B92C1" width="40" height="19"><font class="normaltext" color="#FFFFFF"><b>year</b></font></td>
      <td bgcolor="#7B92C1" width="40" height="19"><font class="normaltext" color="#FFFFFF"><b>in</b></font></td>
    </tr>
  </th>

<% 
  Do While NOT oRSsearchgenre.EOF  
	Dim x, varbgcolor
	if x = 1 then
	  varbgcolor="#CCD5E8"  
	  x=2
	Else
	  varbgcolor="#FFFFFF"
	  x=1
	End if
%>
  <tr>
    <td bgcolor="<%=varbgcolor%>" width="300"><font class="databaseresults" color="#6A6A6A"><b><% vartitle = oRSsearchgenre("title")%><%=vartitle%></b></font></td>
    <td bgcolor="<%=varbgcolor%>" width="400"><font class="databaseresults" color="#6A6A6A"><i><%=oRSsearchgenre("notes")%></i></font></td>
	<td bgcolor="<%=varbgcolor%>" width="300"><font class="databaseresults" color="#6A6A6A">
	<%
	do while (vartitle = oRSsearchgenre("title"))
		Response.Write oRSsearchgenre("genre") & ", "
		oRSsearchgenre.MoveNext
	Loop
	%>
	</font></td>
    <td bgcolor="<%=varbgcolor%>" width="40"><font class="databaseresults" color="#6A6A6A"><%=oRSsearchgenre("year")%></font></td>
    <td bgcolor="<%=varbgcolor%>" width="40"><font class="databaseresults" color="#6A6A6A"><%=oRSsearchgenre("In")%></font></td>
  </tr>
<%	
	oRSsearchgenre.MoveNext
	Loop  

	oRSsearchgenre.close
	set oRSsearchgenre = nothing
%>

</table>

</body>

</html>

 

[DreXor]

are you limiting the dropdown box to the genres in the search results when you're doing the title-->genres list vs the genre-->titles list (in case of confusion translate --> with the words "CHOSEN TO SHOW")

that's anout the only hang up i can see expecially if the drop down is before the list, in that cause you will have to complete the data build before writing, so you have "end results" first

DreX
aKa - Robert

 

[taylorantone]

I was hoping to populate the drop-down with the tblGenres.genre field, while setting the value equal to the genreID.

Will I have to set up multiple recordsets or can I just use an IF statement again to check that the Request.Form(genre) field matches one of the genres in the concatenated genre field?

 

[DreXor]

yeah if you're just doing a general list in the drop down there's no problem there

just request("genre") or whatever the form value is being passed and check against it .. like

<select name="genre" onchange="submit();">
<%
do while not genreRS.eof
If Request("genre") = genreRS("genre") then
OptSelect = " SELECTED"
Else
OptSelect = ""
End If
%>
<option<%=OptSelected%>><%=genreRS("Genre")%></option>
<%
genreRS.MoveNext
loop
%>

auto selects the last posted genre, and if yout into a sub call or something you could easily replicate this at top and bottom of results


then everything else you got worked out, you just will have a genre recordset ( open shortly just to make the dropdown(s) ) and you main RS or whatever for listing titles etc..

DreX
aKa - Robert

 

[taylorantone]

I've got the genre drop-down search working on another page, but in order to get the results I just got working (the title and the genres concatenated into one field) to work with the genre drop-down search, won't I have to add WHERE genre = Request.Form("genre") to the SQL statement, effectively limiting the number of records available in my results table? Seems like that would mess up what I've just got working.

 

[DreXor]

depending on what your target goal is you may need to alter for the ability to allow for "Where genre=" blah

the best advice i could give on this is to get the 3 tables to interact in a proper join set to where you can retrieve the full list ( user friendly style / long names not integers ) then being able to apply filters to it.which in turn would be doing a 1:1 join on the DdvGenre table working from the middle out, since that will be your main referencing talbe and the 2 side tables are reference/lookup material

which in turn would give you your best options for dynamics, change, performance, and customization.

DreX
aKa - Robert

 

[DreXor]

ps .. for a beginner you're doing pretty darn good

DreX
aKa - Robert

 

[taylorantone]

Ok. Before I move on to the search box, I just noticed that at on the very last movie in the results, there is a movie that belongs to two genres: Comedy and Parody.

Well, it skips Comedy and writed out Parody, then gives me the following error:
error '80020009'
/dvdlist/fulllist.asp, line 176

This is what's in the field for genres:

If NOT oRSsearchgenre.EOF Then
Do While (vartitle = oRSsearchgenre("title"))   <-----Line 176
Response.Write oRSsearchgenre("genre") & ", "
oRSsearchgenre.MoveNext
Loop
End If

What's up?

 

[DreXor]

possibly null value?


DreX
aKa - Robert

 

[taylorantone]

Well, two things are going on.

1. I had a MoveNext in the Do While Loop that created the concatenated genres so that it would test T/F on the next record. Then I had a MoveNext after all fields were written to test on the main loop that loops through all the records.

Both weren't necessary and it was pushing my cursor forward a record and causing each movie except for the first one to skip the first genre in the list.

2. The current problem is that the MoveNext in my Do While loop that writes out the concatenated genres moves the cursor to the next record BEFORE it writes out the Year and In fields for the current record. Therefore, I get the EOF error at the very end of the table of results.

Short of moving the genres field to the end of the table, is there a way to correct this? I know that you can do a MovePrevious but that means changing the cursor type and I'd have to read up on how to do that.

Thanks for your patience. I know how I am with people who don't know networking, so I can only imagine what it's like for something as tedious as programming.

 

[TJRTech]

I guess I am still confused as to why you have two loops, an inner and an outter. I hope my chiming in now doesn't cause harm.

But, it seems to me that you need a single query that brings together your three tables and sorts by title:

select tblDVD.dvdID,
       tblGenres.genreID,
       tblDVD.title,
       tblGenres.genre
from tblDVD, tblDVDGenres, tblGenres
where tblDVD.dvdID = tblDVDGenres.dvdID and
      tblDVDGenres.genreID = tblGenres.genreID
order by title, genre

I think you already have the above:

Then, the psuedo code for your loop would be something like the following:

Dim old_dvdID, new_dvdID, strGenres

old_dvdID=0
strGenres = ""

set rs = Cmd.Execute(qry)
if not (rs.eof and rs.bof) then
 while (!rs.eof)
   new_dvdID = rs.fields("dvdID")

   ' Check if old_dvdID not equal to new_dvdID
   if old_dvdID<>new_dvdID then
      if old_dvdID<>0 then  ' Avoid print on 1st rec
         ' Code for spitting out HTML of a single entry
         Response.Write "<tr><td>" + rs.fields("title")...
          ... "<td>" + strGenres + </td></tr>"
      end if
      old_dvdID = new_dvdID  ' Transitioning form old to new
      strGenres = rs.fields("genre")  ' start genres str
      end if
   else
     ' They are equal so just grow Genres str
     strGenres = strGenres + ", " + rs.fields("genre")
   end if
   rs.MoveNext
  wend

  ' Code for writing out LAST, trailing DVD title
  ' same as above
  Response.Write "<tr><td>" + rs.fields("title")...
     ... "<td>" + strGenres + </td></tr>"
end if

[CODE]

The key is the extra variables for detecting the transition from one DVD to the next and the print out of the HTML upon that transition, and the building of the Genres string while within a single DVD set of records.

TR

 

[taylorantone]

Okay, I was going over the code you typed above trying to make some sense of it in my head. Is there an extra End If in there? If so, which one doesn't need to be there? If not, is there a missing If Then statement?

 

[TJRTech]

The code was written from scratch and could have bugs, but I see the extra end if.

A more accurate version is below:

Dim old_dvdID, new_dvdID, strGenres

old_dvdID=0
strGenres = ""

set rs = Cmd.Execute(qry)
if not (rs.eof Or rs.bof) then
 while (!rs.eof)
   new_dvdID = rs.fields("dvdID")

   ' Check if old_dvdID not equal to new_dvdID
   if old_dvdID<>new_dvdID then
      if old_dvdID<>0 then  ' Avoid print on 1st rec
         ' Code for spitting out HTML of a single entry
         Response.Write "<tr><td>" + rs.fields("title")...
          ... "<td>" + strGenres + </td></tr>"
      end if
      old_dvdID = new_dvdID  ' Transitioning form old to new
      strGenres = rs.fields("genre")  ' start genres str
      
   else
     ' They are equal so just grow Genres str
     strGenres = strGenres + ", " + rs.fields("genre")
   end if
   rs.MoveNext
  wend

  ' Code for writing out LAST, trailing DVD title
  ' same as above
  Response.Write "<tr><td>" + rs.fields("title")...
     ... "<td>" + strGenres + </td></tr>"
end if