[thelist] ASP+SQL+HTML tables: is this the sensible way to query my db and display the data

[rudy]

hi elfur

please let me comment on a couple of things you mentioned

first, it is better to do one database call than several

imagine the database is like an old bookshelf that is three flights of
stairs up in the attic

if you want me to bring you down seven books, i'm going to get pretty
annoyed if you tell me the name of the first one, make me climb upstairs
and get it, then tell me the name of the second one, make me climb upstairs
and get it, and so on... better just give me a list of books so i can get
them all in one trip

second, you sort of hinted at a formatting problem, er, challenge, in
arranging the results in seven columns of i-dunno-how-many rows

that's the part that i want to understand fully, before i write any sql
select statement, and *way* before i think about how to "loop" the results
into an html table

you said "the time slot is not the same all through each row. (not all days
have name at 08.00)" and i think i understand this, but i'm not sure
that's how i would try to display the results

if it were me, i would consider having all the time slots starting on the
same level, i.e. each time slot would start a new row -- this will make
the display look "sparse" because each row will be as tall as the tallest
days for that time slot, i.e. the day with the most records for that time
slot

0800  A D ~ E ~
~~~~  B ~ ~ F ~
~~~~  C ~ ~ ~ ~
0830  ~ ~ G ~ H
~~~~  ~ ~ ~ ~ I
0900  ~ J K ~ ~
~~~~  ~ ~ L ~ ~
0930  M ~ ~ ~ N

oooooops, i only did the example for five days... oh well, you will just
have to imagine the pattern for seven

take a look at those records A through N, you'll see them again in a minute

the nice part about this is that your eye can instantly see what's
scheduled for 0830 on thursday

on the other hand, maybe you want to just stack everything for each day
at the top of the column for that day -- this gives a nice "dense" table,
except then you'd have to mark each record with its own time and this
doubles the amount of text in the table (making it "really dense")

this is just like that icelandic page you gave the link for

~~A~  ~~D~  ~~G~  ~~E~  ~~H~
0800  0800  0830  0800  0830
~~B~  ~~J~  ~~K~  ~~F~  ~~I~
0800  0900  0900  0800  0830
~~C~  ~~~~  ~~L~  ~~~~  ~~N~
0800  ~~~~  0900  ~~~~  0930
~~M~  ~~~~  ~~~~  ~~~~  ~~~~
0930  ~~~~  ~~~~  ~~~~  ~~~~

note this is the same data as the "sparse" table but it's harder to find
stuff visually

now obviously, the only way to do this second method is to sort by time
within day

the table will have one row, and each column has all the records for
that day separated by <br> --  i.e. you would have to finish monday's
before starting tuesday's, you could not go through any kind of sorted
result set printing the first record for each day across a row, then the
second record for each day across the row, and so on, it's just not
possible -- after you print D, how do you know G is next?

alternatively, instead of <br>'s between each day's records, you can use a
nested table -- nested html tables are nowhere near as inefficient as
people say they are, you can nest them six to eight deep and even early
browsers won't slow down

for this "dense" method, the looping you do in ASP will be very simple,
and all you have to test for is any days when there's no records at all for
that day (if you know what i mean)

now, to do my "sparse" method, you can loop either by row or by column,
it doesn't matter, because both are tricky -- you also have to test for
time slots that aren't there in addition to days that aren't there, and of
course the order by sequence depends on which way you want to do it...

i hope i'm making sense

somewhere back in last month's tip harvester, there's a tip for cold fusion
which jeff wrote that lets you display stuff in a "balanced" table with
variable number of columns, but this is not the same case -- there, all the
cells were the same, and the objective was to balance the number of entries
in each column, and fill up the cells alphabetically, down one column, then
down the next, and so on

this is different because the records are all different, and you want to
arrange them in two dimensions, by date and time slot

if you are already programming, then please go ahead, but if you haven't
started yet, please consider doing it the first way, because it's more
tufte-ish...

rudy