[thelist] RE: sql.table structure for site I am bidding on...

[Ben Gustafson]

John,

I'm coming into this post a little late and I don't know what your
project's middleware is, but it sounds like what you need is a filter. I
used a recordset filter in ASP to do a similar thing for a workshop
registration form, where the workshops were organized by the city in
which they were held. I created two recordsets, one which contained all
the workshop info, including the city, and one that contained only the
cities. I then used the city recordset to filter the workshop recordset,
within nested while loops. The filter allows you to move between
recordsets that aren't hidden by the filter. I think you could do a
similar thing by creating a recordset that contains all of the movie
info, including title, and then another containing only the titles.
Below is a code snippet that you could use as a basis:

<%

//'Get workshop info;:
strContentSQL  = " SELECT WorkshopID, Name, City, State, Dates, Price,
sortOrder ";
strContentSQL += " FROM WORKSHOP ";
strContentSQL += " ORDER BY sortOrder, City ";
rsWorkshops = Connection.Execute(strContentSQL);

//'Get cities for use in workshop list filter:
strCitiesSQL  = " SELECT DISTINCT City, sortOrder ";
strCitiesSQL += " FROM WORKSHOP ";
strCitiesSQL += " ORDER BY sortOrder ";
rsCities = Connection.Execute(strCitiesSQL);

//'Print out list of workshops, grouped by city:
while (!rsCities.eof) {
	var strCity = rsCities("City");
	var strFilter = "City LIKE '" + strCity + "'";
	rsWorkshops.Filter = strFilter;
	Response.Write("<P>" + strCity + ", " + rsWorkshops("State") + " " +
rsWorkshops("Dates") + ", " + year + ": <BR>");
	while (!rsWorkshops.eof) {
		Response.Write(" <INPUT TYPE=\"checkbox\" NAME=\"workshopfrm\"
VALUE=\"" + rsWorkshops("WorkshopID") + "\">");
		Response.Write(rsWorkshops("Name") + ", ");
		Response.Write("$" + rsWorkshops("Price") + "<BR>");
		rsWorkshops.MoveNext();
	}
	Response.Write("</P>");
	rsCities.MoveNext();
}

rsWorkshops.Close();
rsWorkshops = null;
rsCities.Close();
rsCities = null;
Connection.Close();
Connection = null;

%>

--Ben

________________
Ben Gustafson
Webmaster
Lionbridge Technologies, Inc.
www.lionbridge.com

> hey guys,
> 
> Sorry to be a dunce, but...
> 
> <snip rudy's pseudocode>
> this is a classic 1-to-many query
> 
> select filmTitle
>       , showings.showingDate
>       , showings.showingTime
>       from films, showings
>      WHERE showings.showingDate
>            <= DATE_ADD('2002-01-06', INTERVAL 14 DAY)
>       AND films.filmID = showings.filmID
> 
> let's call the query "john" so that i can access the values 
> in the columns
> of the result set with that qualifer
> 
> so here's pseudocode for nested output --
> 
> set thisTitle=''
> loop
>    get the next resultset row
>    if thisTitle <> john.filmTitle
>       {  /* print the new title */
>          thisTitle=john.filmTitle }
>    /* print the showing */
> end loop
> </snip>
> 
> 
> OK...this is exactly what I needed, and if it were simple 
> dumping contents
> to a page, I'd be there. But I have to format the output, 
> which means I have
> to conditionally close a table cell. I cna't figure it out...
> 
> I need to print the movie info (title, desctiption, rating, 
> showtime) then,
> if its the same movie, print the other showtimes. Then, close 
> the <td> &
> <tr>.
> 
> I can't figure out the nesting of the if's to make the </td> 
> </tr> close
> when the row is done printing.