[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