Re: Display DB Records Differently
am 13.05.2007 21:59:46 von rn5a
On May 13, 9:54 pm, Adrienne Boswell wrote:
> Gazing into my crystal ball I observed r...@rediffmail.com writing innews:1179058500.940097.129970@p77g2000hsh.googlegroups.com :
>
>
>
>
>
> > A MS-Access DB has 3 tables - Teacher, Class & TeacherClass. The
> > Teacher table has 2 columns - TeacherID & TeacherName (TeacherID being
> > the primary key). The Class table too has 2 columns - ClassID &
> > ClassName (ClassID being the primary key)
>
> > The TeacherClass table has 3 columns - TCID (AutoNumber), TeacherID &
> > ClassID. One teacher can teach multiple classes & one class can be
> > taught by multiple teachers. The TeacherClass table basically stores
> > this info. If TeacherID=1 teaches 3 classes, then the TeacherClass DB
> > table would look like this (1st column shown below is the TeacherID &
> > the 2nd column is the ClassID column):
>
> > 1 3
> > 1 7
> > 1 9
>
> > & not as
>
> > 1 3, 7, 9
>
> > Assume that TeacherID=1, whose name is, say, Peter teaches 3 classes
> > whose ClassIDs are, say, Class-3, Class-7 & Class-9. I have already
> > framed the following SQL query that will retrieve these records by
> > JOINing the 3 tables together:
>
> > SELECT T.TeacherName,C.ClassName,T.TeacherID,C.ClassID FROM
> > (TeacherClass AS TC INNER JOIN Teachers AS T ON
> > T.TeacherID=TC.TeacherID) INNER JOIN Classes AS C ON
> > C.ClassID=TC.ClassID ORDER BY T.TeacherName
>
> > but the problem I am facing is in displaying the records in a HTML
> > table. I don't want the records to be displayed in the traditional way
> > whose source code would look something like this:
>
> >
> >
> >TEACHER NAME |
> >CLASS NAME |
> >
---|
>
> >
> >Peter |
> >Class-3 |
> >
>
> >
> >Peter |
> >Class-7 |
> >
>
> >
> >Peter |
> >Class-9 |
> >
> >
>
> > This ASP code would produce the above source code of the HTML table
> > with the records:
>
> ><%
> > 'Connection objConn
>
> > 'here comes the above SQL query
> > strSQL="SELECT........"
>
> > 'Recordset objRS
> > objRS.Open strSQL,objConn
> > %>
>
> >
> >
> >TEACHER NAME |
> >CLASS NAME |
> >
---|
> ><%
> > Do Until(objRS.EOF)
> > %>
> >
> ><%= objRS("TeacherName") %> |
> ><%= objRS("ClassName") %> |
> >
> ><%
> > objRS.MoveNext
> > Loop
> > %>
>
> > But I want the source code of the HTML table to look something like
> > this:
>
> >
> >
> >TEACHER NAME |
> >CLASS NAME |
> >
---|
>
> >
> >Peter |
> >
> >
> >- Class-3
> >- Class-7
> >- Class-9
> >
> > |
> >
> >
>
> > In other words, the 1st cell in the 1st row in the HTML table should
> > display Peter only once while the corresponding 2nd cell in the 1st
> > row should display the 3 classes - Class-3, Class-7 & Class-9 as
> > bulleted lists.
>
> > How do I do this?
>
> > One way of doing this is to create 2 SQL queries - the 1st one will
> > retrieve DISTINCT TeacherName from the Teacher table & the 2nd SQL
> > query will be the one shown above, then create 1 child HTML table
> > within each of the 2 cells of the parent HTML table & finally display
> > the DISTINCT TeacherName in the 1st child HTML table (which is within
> > the 1st cell of the parent HTML table) & then display the
> > corresponding classes in the 2nd child HTML table (which is within the
> > 2nd cell of the parent HTML table)? Would this be the correct
> > approach?
>
> > Thanks
>
> You can test to see if the next record is the same value, and if it is
> not, act accordingly. The problem you might have is getting the markup
> correctly generated.
>
> --
> Adrienne Boswell at Home
> Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
> Please respond to the group so others can share- Hide quoted text -
>
> - Show quoted text -
Thanks, Adrienne, for your suggestion but how do I do the test? I mean
to say that objRS("TeacherName") will give me the
TeacherName...OK...fine... but against whom or against what should the
comparison be done to find whether the next TeacherName is the same as
the previous TeacherName or not? Can you please show me a small
example?
Thanks once again,
Regards,
RON
Re: Display DB Records Differently
am 15.05.2007 09:07:33 von Adrienne Boswell
Gazing into my crystal ball I observed rn5a@rediffmail.com writing in
news:1179086386.270713.26530@l77g2000hsb.googlegroups.com:
> On May 13, 9:54 pm, Adrienne Boswell wrote:
>> Gazing into my crystal ball I observed r...@rediffmail.com writing
>> innews:1179058500.940097.129970@p77g2000hsh.googlegroups.com :
>>
>>
>>
>>
>>
>> > A MS-Access DB has 3 tables - Teacher, Class & TeacherClass. The
>> > Teacher table has 2 columns - TeacherID & TeacherName (TeacherID
>> > being the primary key). The Class table too has 2 columns - ClassID
>> > & ClassName (ClassID being the primary key)
>>
>> > The TeacherClass table has 3 columns - TCID (AutoNumber), TeacherID
>> > & ClassID. One teacher can teach multiple classes & one class can
>> > be taught by multiple teachers. The TeacherClass table basically
>> > stores this info. If TeacherID=1 teaches 3 classes, then the
>> > TeacherClass DB table would look like this (1st column shown below
>> > is the TeacherID & the 2nd column is the ClassID column):
>>
>> > 1 3
>> > 1 7
>> > 1 9
>>
>> > & not as
>>
>> > 1 3, 7, 9
>>
>> > Assume that TeacherID=1, whose name is, say, Peter teaches 3
>> > classes whose ClassIDs are, say, Class-3, Class-7 & Class-9. I have
>> > already framed the following SQL query that will retrieve these
>> > records by JOINing the 3 tables together:
>>
>> > SELECT T.TeacherName,C.ClassName,T.TeacherID,C.ClassID FROM
>> > (TeacherClass AS TC INNER JOIN Teachers AS T ON
>> > T.TeacherID=TC.TeacherID) INNER JOIN Classes AS C ON
>> > C.ClassID=TC.ClassID ORDER BY T.TeacherName
>>
>> > but the problem I am facing is in displaying the records in a HTML
>> > table. I don't want the records to be displayed in the traditional
>> > way whose source code would look something like this:
>>
>> >
>> >
>> >TEACHER NAME |
>> >CLASS NAME |
>> >
---|
>>
>> >
>> >Peter |
>> >Class-3 |
>> >
>>
>> >
>> >Peter |
>> >Class-7 |
>> >
>>
>> >
>> >Peter |
>> >Class-9 |
>> >
>> >
>>
>> > This ASP code would produce the above source code of the HTML table
>> > with the records:
>>
>> ><%
>> > 'Connection objConn
>>
>> > 'here comes the above SQL query
>> > strSQL="SELECT........"
>>
>> > 'Recordset objRS
>> > objRS.Open strSQL,objConn
>> > %>
>>
>> >
>> >
>> >TEACHER NAME |
>> >CLASS NAME |
>> >
---|
>> ><%
>> > Do Until(objRS.EOF)
>> > %>
>> >
>> ><%= objRS("TeacherName") %> |
>> ><%= objRS("ClassName") %> |
>> >
>> ><%
>> > objRS.MoveNext
>> > Loop
>> > %>
>>
>> > But I want the source code of the HTML table to look something like
>> > this:
>>
>> >
>> >
>> >TEACHER NAME |
>> >CLASS NAME |
>> >
---|
>>
>> >
>> >Peter |
>> >
>> >
>> >- Class-3
>> >- Class-7
>> >- Class-9
>> >
>> > |
>> >
>> >
>>
>> > In other words, the 1st cell in the 1st row in the HTML table
>> > should display Peter only once while the corresponding 2nd cell in
>> > the 1st row should display the 3 classes - Class-3, Class-7 &
>> > Class-9 as bulleted lists.
>>
>> > How do I do this?
>>
>> > One way of doing this is to create 2 SQL queries - the 1st one will
>> > retrieve DISTINCT TeacherName from the Teacher table & the 2nd SQL
>> > query will be the one shown above, then create 1 child HTML table
>> > within each of the 2 cells of the parent HTML table & finally
>> > display the DISTINCT TeacherName in the 1st child HTML table (which
>> > is within the 1st cell of the parent HTML table) & then display the
>> > corresponding classes in the 2nd child HTML table (which is within
>> > the 2nd cell of the parent HTML table)? Would this be the correct
>> > approach?
>>
>> > Thanks
>>
>> You can test to see if the next record is the same value, and if it
>> is not, act accordingly. The problem you might have is getting the
>> markup correctly generated.
>>
>> --
>> Adrienne Boswell at Home
>> Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
>> Please respond to the group so others can share- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks, Adrienne, for your suggestion but how do I do the test? I mean
> to say that objRS("TeacherName") will give me the
> TeacherName...OK...fine... but against whom or against what should the
> comparison be done to find whether the next TeacherName is the same as
> the previous TeacherName or not? Can you please show me a small
> example?
>
> Thanks once again,
>
Quick and dirty - adapt as needed:
testarr = array("dog", "cat", "cat", "bird", "horse")
for i = 0 to ubound(testarr)
'response.write "
testthingy: " & tempthingy
if trim(tempthingy) = trim(testarr(i)) then
else
response.write "
" & i & ": " & testarr(i)
end if
tempthingy = testarr(i)
next
tempthingy = ""
=======================
Results:
0: dog
1: cat
3: bird
4: horse
--
Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share
Re: Display DB Records Differently
am 16.05.2007 06:11:54 von rn5a
On May 15, 12:07 pm, Adrienne Boswell wrote:
> Gazing into my crystal ball I observed r...@rediffmail.com writing innews:1179086386.270713.26530@l77g2000hsb.googlegroups.com:
>
>
>
>
>
> > On May 13, 9:54 pm, Adrienne Boswell wrote:
> >> Gazing into my crystal ball I observed r...@rediffmail.com writing
> >> innews:1179058500.940097.129970@p77g2000hsh.googlegroups.com :
>
> >> > A MS-Access DB has 3 tables - Teacher, Class & TeacherClass. The
> >> > Teacher table has 2 columns - TeacherID & TeacherName (TeacherID
> >> > being the primary key). The Class table too has 2 columns - ClassID
> >> > & ClassName (ClassID being the primary key)
>
> >> > The TeacherClass table has 3 columns - TCID (AutoNumber), TeacherID
> >> > & ClassID. One teacher can teach multiple classes & one class can
> >> > be taught by multiple teachers. The TeacherClass table basically
> >> > stores this info. If TeacherID=1 teaches 3 classes, then the
> >> > TeacherClass DB table would look like this (1st column shown below
> >> > is the TeacherID & the 2nd column is the ClassID column):
>
> >> > 1 3
> >> > 1 7
> >> > 1 9
>
> >> > & not as
>
> >> > 1 3, 7, 9
>
> >> > Assume that TeacherID=1, whose name is, say, Peter teaches 3
> >> > classes whose ClassIDs are, say, Class-3, Class-7 & Class-9. I have
> >> > already framed the following SQL query that will retrieve these
> >> > records by JOINing the 3 tables together:
>
> >> > SELECT T.TeacherName,C.ClassName,T.TeacherID,C.ClassID FROM
> >> > (TeacherClass AS TC INNER JOIN Teachers AS T ON
> >> > T.TeacherID=TC.TeacherID) INNER JOIN Classes AS C ON
> >> > C.ClassID=TC.ClassID ORDER BY T.TeacherName
>
> >> > but the problem I am facing is in displaying the records in a HTML
> >> > table. I don't want the records to be displayed in the traditional
> >> > way whose source code would look something like this:
>
> >> >
> >> >
> >> >TEACHER NAME |
> >> >CLASS NAME |
> >> >
---|
>
> >> >
> >> >Peter |
> >> >Class-3 |
> >> >
>
> >> >
> >> >Peter |
> >> >Class-7 |
> >> >
>
> >> >
> >> >Peter |
> >> >Class-9 |
> >> >
> >> >
>
> >> > This ASP code would produce the above source code of the HTML table
> >> > with the records:
>
> >> ><%
> >> > 'Connection objConn
>
> >> > 'here comes the above SQL query
> >> > strSQL="SELECT........"
>
> >> > 'Recordset objRS
> >> > objRS.Open strSQL,objConn
> >> > %>
>
> >> >
> >> >
> >> >TEACHER NAME |
> >> >CLASS NAME |
> >> >
---|
> >> ><%
> >> > Do Until(objRS.EOF)
> >> > %>
> >> >
> >> ><%= objRS("TeacherName") %> |
> >> ><%= objRS("ClassName") %> |
> >> >
> >> ><%
> >> > objRS.MoveNext
> >> > Loop
> >> > %>
>
> >> > But I want the source code of the HTML table to look something like
> >> > this:
>
> >> >
> >> >
> >> >TEACHER NAME |
> >> >CLASS NAME |
> >> >
---|
>
> >> >
> >> >Peter |
> >> >
> >> >
> >> >- Class-3
> >> >- Class-7
> >> >- Class-9
> >> >
> >> > |
> >> >
> >> >
>
> >> > In other words, the 1st cell in the 1st row in the HTML table
> >> > should display Peter only once while the corresponding 2nd cell in
> >> > the 1st row should display the 3 classes - Class-3, Class-7 &
> >> > Class-9 as bulleted lists.
>
> >> > How do I do this?
>
> >> > One way of doing this is to create 2 SQL queries - the 1st one will
> >> > retrieve DISTINCT TeacherName from the Teacher table & the 2nd SQL
> >> > query will be the one shown above, then create 1 child HTML table
> >> > within each of the 2 cells of the parent HTML table & finally
> >> > display the DISTINCT TeacherName in the 1st child HTML table (which
> >> > is within the 1st cell of the parent HTML table) & then display the
> >> > corresponding classes in the 2nd child HTML table (which is within
> >> > the 2nd cell of the parent HTML table)? Would this be the correct
> >> > approach?
>
> >> > Thanks
>
> >> You can test to see if the next record is the same value, and if it
> >> is not, act accordingly. The problem you might have is getting the
> >> markup correctly generated.
>
> >> --
> >> Adrienne Boswell at Home
> >> Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
> >> Please respond to the group so others can share- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks, Adrienne, for your suggestion but how do I do the test? I mean
> > to say that objRS("TeacherName") will give me the
> > TeacherName...OK...fine... but against whom or against what should the
> > comparison be done to find whether the next TeacherName is the same as
> > the previous TeacherName or not? Can you please show me a small
> > example?
>
> > Thanks once again,
>
> Quick and dirty - adapt as needed:
> testarr = array("dog", "cat", "cat", "bird", "horse")
>
> for i = 0 to ubound(testarr)
> 'response.write "
testthingy: " & tempthingy
> if trim(tempthingy) = trim(testarr(i)) then
> else
> response.write "
" & i & ": " & testarr(i)
> end if
> tempthingy = testarr(i)
> next
> tempthingy = ""
>
> =======================
> Results:
> 0: dog
> 1: cat
> 3: bird
> 4: horse
>
> --
> Adrienne Boswell at Home
> Arbpen Web Site Design Serviceshttp://www.cavalcade-of-coding.info
> Please respond to the group so others can share- Hide quoted text -
>
> - Show quoted text -
Thanks, Adrienne, for the suggestion but that isn't what I want. What
I want is something like this:
Dog
Animal Camel
Horse
Parrot
Bird Pigeon
Crow
Soccer
Sport Tennis
Golf
Piano
Music Guitar
Flute
MacOS
OS Windows
Linux
Note that I have left a blank line in between for clarity. In other
words, dog, camel & horse are animals; thus they have been grouped
together corresponding to Animal. Parrot, pigeon & crow are birds;
thus they have been grouped together corresponding to Bird. Soccer
tennis & golf are sports; thus they are grouped together corresponding
to Sport. Piano, guitar & flute are musical instruments; hence they
have been grouped together corresponding to Music. MacOS, Windows &
Linux are operating systems; hence they have been grouped together
corresponding to OS.
To some extent, I have got it with the following code:
TEACHER |
CLASS |
---|
<%
Dim strOld
Do Until(objRS.EOF)
If(objRS("TeacherName")<>strOld) Then
%>
<%= objRS("TeacherName") %> |
<%
Else
%>
|
<%
End If
%>
- <%= objRS("ClassName") %>
|
<%
strOld=objRS("TeacherName")
objRS.MoveNext
Loop
%>
But the problem is in displaying Animal, Bird, Sport, Music & OS.
Currently the above code would display the following HTML table in the
browser:
Animal Dog
Camel
Horse
Bird Parrot
Pigeon
Crow
Sport Soccer
Tennis
Golf
Music Piano
Guitar
Flute
OS MacOS
Windows
Linux
i.e. Animal resides in the 1st cell under the 1st column but it should
be in the 2nd cell under the 1 column. Currently 2nd & 3rd cells under
the 1st column remain empty but the 1st & the 3rd cell should remain
rempty under the 1st column. The no. of cells in the 2nd column that
the items in a particular group occupy, the group name should occupy
the middle cell. For e.g. Animal has 3 items; hence the text Animal
should occupy the 2nd cell.
Any idea how do I alter the above ASP code to do this?
Thanks once again,
Regards,
RON