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?

 

[taylorantone]

Okay. Your suggestion works REALLY well, not to mention it's a hell of a lot faster than the multiple loops. The only catch is that it doesn't write the first movie title. It starts with the 2nd movie and puts the 1st movie's genres into it's genre field.

My head is starting to hurt from this. I've gone through the code while charting out exactly what it does and what the variables become, etc, and the first time it writes a movie title is when it's moved on to the first record of a movie whose dvdID doesn't match the first one.

Where should I place the Response.Write("title")? Then again, would another variable that stores the movie title be better here? Kind of like you've done with the strGenres, but it stores the movie title until the dvdID changes and then writes it out with the strGenres?

I also get an error at the top:
error '80020009'
/development/fulllist.asp, line 53

I really ought to take a class on this instead of trying to teach myself. It's frustrating.

 

[TJRTech]

It will require a little debugging. The code I provided was more of a suggestion than working code...it was meant to show you the general idea.

You are RIGHT about the logic of the loop. It should have stored the various fields in temp vars so that upon the transition you write out the old ones, not the new ones.

I think the new code that does that would look "more" like the following:

Dim old_dvdID, new_dvdID, strGenres
Dim title

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>" + title
          ... "<td>" + strGenres + </td></tr>"
      end if
      old_dvdID = new_dvdID  ' Transitioning form old to new
      strGenres = rs.fields("genre") ' start genre
      title = rs.fields("title") ' get title       
   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>" + title
     ... "<td>" + strGenres + </td></tr>"
end if

What is line 53, exactly?

TR

 

[taylorantone]

The one with the <----- to the right is line 53. Sorry for the word wrapping, but I like my stuff in straight lines (holdover from HTML) so I don't break them if I don't have to.

The difference in the time it takes to retrieve the results with the code below is amazing compared to the loops.

dim old_dvdID, new_dvdID, strGenres

old_dvdID = 0
strGenres = ""
%>

<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="300" height="19"><font class="normaltext" color="#FFFFFF"><b>genres</b></font></td>
    </tr>
  </th>

<%
If NOT (oRSsearchgenre.EOF Or oRSsearchgenre.BOF) Then
	While NOT oRSsearchgenre.EOF
		new_dvdID = oRSsearchgenre("dvdID")

		If old_dvdID <> new_dvdID Then
			If old_dvdID <> 0 Then
				Response.Write "<tr><td>" & oRSsearchgenre("title") & "</td><td>" & strGenres & "</td></tr>"
			End If
			old_dvdID = new_dvdID
			strGenres = oRSsearchgenre("genre")
		Else
			strGenres = strGenres & ", " & oRSsearchgenre("genre")
		End If

		oRSsearchgenre.MoveNext
	Wend
	Response.Write "<tr><td>" & oRSsearchgenre("title") & "</td><td>" & strGenres & "</td></tr>"  <-----
End If
%>

</table>

</body>

</html>

 

[DreXor]

uhm in the supplied code, unless it's clipped you dont have the recordset being created, opened, or queried to be able to response.write anything from it ( or a connection )

also might help if you picked one or the other of BOF or EOF, makes for less checking, typically i use EOF only, because if it's empty, the beginning and end will be the same


DreX
aKa - Robert

 

[taylorantone]

Sorry...it's clipped. Here's the first part:

<html>

<head>
	<title>Full DVD List</title>
	<link rel="stylesheet" type="text/css" href="../style.css">
	<link rel="stylesheet" type="text/css" href="../iframe.css">
</head>

<body>

<div class="title">entire list test</div>
<br>
<br>
<i><font class="about">See more detailed movie information at <a target="_blank" href="[URL unfurl="true"]http://www.allmovie.com">All[/URL] Movie Guide</a></font></i>
<br>
<%
dim oRSsearchgenre
set oRSsearchgenre = Server.CreateObject("ADODB.Recordset")
sqlsearchgenre = "SELECT tblDVD.dvdID, 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"

 

[TJRTech]

Checking for:

IF NOT (rs.BOF AND rs.EOF) THEN

after the query execution is typical, boilerplate Microsoft code. That is the way I learned it and so that is the way I always do it. I have tried different mechanism but that always seems to be the safest.

So, taylor, make sure to use AND, not OR (see above...sorry if I mislead you).


As for line 53...try making everything fit on one line and see if the problem goes away. I suspect it will.

If, for formatting purposes, you wish to split a line, use the & _ characters, as in the following example:

 Response.Write "<tr><td>" & oRSsearchgenre("title") & _
                "</td></tr>"

The underscore indicates that the line splits, and I have only ever used it when preceeded by an ampersand.

Other than that, Taylor, I think you must be pretty close.

TR

 

[DreXor]

np we all have prefs on how we do things

as for the line continuances only way i'ce seen them and for them to be semi functional is :

this = "blahblahblah"_
&"blahblahblah"

as for the line 53 error :

[b]<CLIP>[/b]
        oRSsearchgenre.MoveNext <<cycling towards EOF
    Wend << when EOF this will fail and continue to next line
    Response.Write "<tr><td>" & oRSsearchgenre("title") & "</td><td>" & strGenres & "</td></tr>"  <<^ fails because it's at the EOF, hence no data and also error
End If

DreX
aKa - Robert

 

[TJRTech]

Right...move past end of file will cause that problem that DreXor is describing.

That's why you should only "write" out the values cached in vars as discussed and shown in the last code sample of mine.

TR

 

[taylorantone]

My fault. Sorry TJRTech...somehow I missed the added title variable in the code--I was looking at the prior one.

I also had to add variables for the other data I wanted displayed (notes, year, etc.) but it works like a charm!!

Of course, trying to add a search form now that chooses a genre and a search order (by title, year, etc.) is going to foul it all up.

This might not make any sense, but I'm thinking that I need to set up two recordsets: 1 that gathers the titles of the DVD's that match the genre search box and then orders them based on the chosen sort field and then whether it's Asc or Desc (see

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

for how I have the old one set up), and then a 2nd one to do exactly what it already does, except the WHERE clause only pulls the titles that match the ones in the first recordset.

 

[TJRTech]

I think you are over-thinking this.

I am not sure why you need the 2nd recordset.

You have a form that supplies the Genre, the ORDER BY field and the order by direction (ASC, DESC). That is all you need to generate the appropriate select statement that gives you EXACTLY the recordset you need.

Oh, and a little suggestion...a really KILLER version of your search engine might have everything done in one form.

At the top of the form you could have your GENRE select list and have "-All-" as the first, default item. And, the default sort order could be by Title, ascending.

Then out a GO or Refresh link/button next to your GENRE select list and have that POST the form to itself.

Lastly, place a table header row in for your returned results and make each title a hyperlink that when clicked forces the page to reload with that new column selected as the SORT BY column.

Really sophisticated versions might have Up/Down arrow graphics as part of the current sort column.

The solution I am describing will require some javascript programming, and probably a hidden field or two to managing posting of the form to itself (so it doesn't get confused between a post and the first time loaded).

There are probably examples of this around.

TR

 

[taylorantone]

I've got something similar. I had the link wrong. It's

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

It has the form you're talking about. However, since the page that y'all just helped me put together requires that the recordset that it returned have all of the movies listed individually so that it can go through and pull out the genres that are assigned to it, if I alter the ORDER BY and WHERE clauses, won't it foul up the genres that are returned? I mean, it'll get the movie titles just fine, but it would only write out the genre that matches the one chosen in the search because that search box will limit the recordset returned?

Or is there a way to work with the data on the client end so that it's not trying to refresh from the server each time?

 

[DreXor]

this might be of help to you/reference
it was a rather similar setup to what you're dealing with
3 tables and crossed ids

thread333-865885 and thread333-867880

DreX
aKa - Robert

 

[TJRTech]

I never assumed that you would want to return ALL the movies to the client and then limit the ones viewed, client-side, by the genre selected.

You can do that, I suppose, but it would be a lot more work and probably require use of XML and XSLT, then you are stuck on an IE Client (there may be other ways to render effectively client-side, though).

I would simply reload the page (e.g. "go back to the server") everytime the user changes the criteria and hits the button. In that way, you get your list of movies that matches the criteria sorted as you want it.

Oh, and one last thing, just to be sure...you will always need two queries to fill the page: one for the select box of Genres, and the other for the movies found based on the criteria.

Regards,
TR