[thelist] SQL: using GROUP BY and COUNT
Ken Kogler
ken.kogler at curf.edu
Tue Aug 20 00:10:02 CDT 2002
> > what i was talking about was the
> > value of having a piece of code that
> > says
> >
> > <td>#temperature#</td>
> >
> > rather than
> >
> > <td>#expression3#</td>
> >
> > or even your implied
> >
> > <td>#queryresult[3]#</td>
> ahhh... right. afaik asp doesn't work like that.
But it can! Assume getRows() returns a bunch or rows from a 4-column
table of contact info. The table is laid out like this:
cID cName cPhone cZip
--- --------- ---------- -----
001 Bob Jones 17085551212 60305
002 Jan Smith 17345551212 48195
003 Ken Rawls 13145551212 63123
And by executing "arrContact = objRS.getRows()" we get a 2D array in the
form of arrContact(rowNum,colNum):
arrContact(0,0) = "001"
arrContact(1,0) = "Bob Jones"
arrContact(2,0) = "17085551212"
arrContact(3,0) = "60305"
arrContact(0,1) = "002"
arrContact(1,1) = "Jan Smith"
arrContact(2,1) = "17345551212"
arrContact(3,1) = "48195"
arrContact(0,1) = "003"
arrContact(1,1) = "Ken Rawls"
arrContact(2,1) = "13145551212"
arrContact(3,1) = "63123"
We can write a FOR loop to write out all the phone numbers:
for rowLoop = 0 to ubound(arrContact,2)
response.write arrContact(2,rowLoop)
next
But if we look at that code a year from now, we won't have any clue what
"arrContact(2,rowLoop)" is supposed to be without digging back through
the code to look it up, which (depending on your code!) can be a real
pain. Consider this then:
const cPhone = 2
for rowLoop = 0 to ubound(arrContact,2)
response.write arrContact(cPhone,rowLoop)
next
Catch that? By assigning the value of "2" to an aptly named variable (in
this case one that matches the column name in the db table) -- and
making sure it's a constant -- we can write code that is MUCH easier to
read with a minimal amount of extra effort.
Neat huh?
--Ken "back from vacation" Kogler
More information about the thelist
mailing list