Multiple query fields into one string on web page

[taylorantone]

I used Front Page to pull data from a query onto the web. Everything works fine EXCEPT for the many-to-many relationship I have set up.

It pulls the first 3 fields from tblDVD just fine, then it's set to pull the many-to-many part (the genres) from the other table. The same movie is listed for every genre it belongs to.

What I want to do is have the results only have one movie per record, and consolidate the genres into the Genre field like this (Crime, Drama, Comedy). I don't even need the commas.

I'm also going to need to set up a search box later so that you can browse by genre, but I'll cross that bridge when I come to it.

Is this possible and not-too-terribly difficult?

Here's a link to what I'm talking about:

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

 

[Tarwn]

First of all, I would consier reformatting your tables if possible. I would suggest something along the lines of:
Movies:
movie_id - autonumber
movie_name - text

Genres:
genre_id - autonumber
genre_name - text

MovieGenre:
genre_id
movie_id

The purpose behind this change is to remove the many-to-many relationship as well as store information based on a numeric id so that multiple records that pertain to a single movie or genre don't accidentally have misppelings, etc. plus it will keep the storage needs lower because you will only have one copy of each movie and genre and then the multiple record portion of the relationship will only store the numeric id's.

If you can't alter the table design, then this would be a possible solution to your problem:
If your only selecting a single row from tblDVD for each movie, then go ahead and cross in the table of genres at this point also. I would suggest something like:
SELECT fieldname, fieldname, etc, movie, genre FROM (tblDVD INNER JOIN tblGenre ON tblDVD.Movie = tblGenre.Movie) WHERE conditions here ORDER BY movie, genre

Then after you receive your recordet back you could loop through the recordset and output the data like this:

'assume recordset is called rs_movies
Dim cur_movie
If Not rs_movies.EOF Then rs_movies.MoveFirst
Response.Write "<table><tr><th>Movie</th><th>Genres</th></tr>"
Do Until rs_movies.EOF
   'check if the movie we are working on has changed or first row
   If Not rs_movies("movie") = cur_movie Then
      'if the cur_movie is not empty (not first row) end the previous row
      If len(cur_movie) > 0 Then Response.Write "</td></tr>"

      'output the beginning of the row for this movie
      Response.Write "<tr><td>" & rs_movies("movie") & "</td><td>" & rs_movies("genre")
      
      'set the current movie
      cur_movie = rs_movies("movie")
   Else
      'we already started this movie row, just output the genre
      Response.Write " " & rs_movies("genre")
   End If

   'move to next row
   rs_movies.MoveNext
Loop

'finish the last outstanding row
Response.Write "</td></tr></table>"

So basically it outputs the beginning of the movie row each time it runs into a new movie (and ends it if there was a previous cur_movie value). Then as long as that movie is still the one in the records your looking at it only outputs the additional genres into that same column.

If we look at a sample recordset that looks like this:
"Movie 1" "Comedy"
"Movie 1" "Drama"
"Movie 1" "Sci Fi"
"Movie 2" "Drama"

Then what happens as the loop goes through is this:

pre-loop: output "<table><tr><th>Movies</th><th>Genres</th></tr>"
1: cur_value is blank, so output "<tr><td>Movie 1</td><td>Comedy"
2: cur_value matches, so output " Drama"
3: cur_value matches, so output " Sci Fi"
4: cur_value does not match and is not null, so output </td></tr><tr><td>Movie 2</td><td>Drama
5: Loop ends, output "</td></tr></table>"

Anyways, hope this helps,

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/