How do I handle alphabetic paging?

The following will allow you to query a table for last names beginning with each letter in the alphabet.

<%
Ltr = UCase(Request.QueryString("Ltr"))
if (Ltr < "A") or (Ltr > "Z") then Ltr = "A"
for i = 65 to 90
if i > 65 then response.write " | "
if Asc(Ltr) <> i then
response.write "<a href='Alpha.asp?Ltr=" & _
Chr(i) & "'>" & Chr(i) & "</a>"
else
response.write "" & Chr(i) & ""
end if
next
SQL = "SELECT lastName FROM nameTable WHERE lastName LIKE '" & Ltr & "%'"
Response.Write "" & SQL

' ... execute query and process results
%>

Now, you might only be interested in letters (and other characters) that are actually represented in your database. Most of you probably don't have last names that start with Q or X, for example; and sometimes a user will enter their name incorrectly, e.g. !Smith. So you could consider building your alphabet list like this:

<%
Ltr = UCase(Request.QueryString("Ltr"))
Set Conn = CreateObject("ADODB.Connection")
conn.open "<connectionString>"
SQL = "SELECT DISTINCT LEFT(lastName,1) FROM nameTable ORDER BY LEFT(lastName,1)"
set rs = conn.execute(SQL)
do while not rs.eof
response.write " | "
rs0 = UCase(rs(0))
if rs0 <> Ltr then
response.write "<a href='Alpha.asp?Ltr=" & _
rs0 & "'>" & rs0 & "</a>"
else
response.write "" & rs0 & ""
end if
rs.movenext
loop
response.write " |"

SQL = "SELECT lastName FROM nameTable WHERE lastName LIKE '" & Ltr & "%'"
Response.Write "" & SQL

' ... execute query and process results
%>

Something More Challenging

Another request has been for code that shows the divisions between the pages, so that, for example, if I have 300 names that start with A, and only 100 that start with B, and so on, there wouldn't be 300 results on page 1, and 100 results on page 2. Rather, they would be grouped in divisions of some pre-defined number, and labeled/linked like this:

Aardvark - Abigail
AbyNormal - Anderson
Andersson - Anderwurst
Andres - Atwell
...

And you would click on the first link, and see all the names between Aardvark and Abigail. When you click on the next link, you would see the same number of names, but they would be from AbyNormal to Anderson with one s, and so on. Well, here is an example using SQL Server that will let you do this. Note that I have an IDENTITY column in order to provide a primary key (even though it's a surrogate), and I added a computed column for e-mail address, just to return some extraneous data with the query.

SET NOCOUNT ON

CREATE TABLE FaqUsers
(
FirstName VARCHAR(32) NOT NULL,
LastName VARCHAR(32) NOT NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX lnfn
ON FaqUsers(LastName, FirstName)
GO

INSERT FaqUsers(LastName, FirstName) VALUES('Aardvark', 'Bob')
INSERT FaqUsers(LastName, FirstName) VALUES('Abbott', 'Bob')
INSERT FaqUsers(LastName, FirstName) VALUES('Barnsworth', 'Bill')
INSERT FaqUsers(LastName, FirstName) VALUES('Bertrand', 'Aaron')
INSERT FaqUsers(LastName, FirstName) VALUES('Burns', 'Edward')
INSERT FaqUsers(LastName, FirstName) VALUES('Burns', 'Mr.')
INSERT FaqUsers(LastName, FirstName) VALUES('Clamshell', 'Grill')
INSERT FaqUsers(LastName, FirstName) VALUES('Casper', 'Ghosty')
INSERT FaqUsers(LastName, FirstName) VALUES('Clatton', 'Amy')
INSERT FaqUsers(LastName, FirstName) VALUES('Da Vinci', 'Leonardo')
INSERT FaqUsers(LastName, FirstName) VALUES('Dafoe', 'Byron')
INSERT FaqUsers(LastName, FirstName) VALUES('Damaso', 'Tommy')
INSERT FaqUsers(LastName, FirstName) VALUES('Damaso', 'Virgil')
INSERT FaqUsers(LastName, FirstName) VALUES('Duke', 'Bo')
INSERT FaqUsers(LastName, FirstName) VALUES('Duke', 'Daisy')
INSERT FaqUsers(LastName, FirstName) VALUES('Duke', 'Luke')
INSERT FaqUsers(LastName, FirstName) VALUES('Ertl', 'Ham')
INSERT FaqUsers(LastName, FirstName) VALUES('Finch', 'Dundas')
INSERT FaqUsers(LastName, FirstName) VALUES('Grep', 'Rtfm')
INSERT FaqUsers(LastName, FirstName) VALUES('Hatcher', 'Derian')
INSERT FaqUsers(LastName, FirstName) VALUES('Hazard', 'Duke')
INSERT FaqUsers(LastName, FirstName) VALUES('Isaac', 'Newton')
INSERT FaqUsers(LastName, FirstName) VALUES('Islington', 'Jon')
INSERT FaqUsers(LastName, FirstName) VALUES('Johnson', 'John')
INSERT FaqUsers(LastName, FirstName) VALUES('Johnston', 'John')
INSERT FaqUsers(LastName, FirstName) VALUES('Klemp', 'Stooge')
INSERT FaqUsers(LastName, FirstName) VALUES('Laderoute', 'Jamie')
INSERT FaqUsers(LastName, FirstName) VALUES('McBain', 'McDuff')
INSERT FaqUsers(LastName, FirstName) VALUES('McLaren', 'Klye')
INSERT FaqUsers(LastName, FirstName) VALUES('Nincombe', 'Oop')
INSERT FaqUsers(LastName, FirstName) VALUES('Osprey', 'Ray')
INSERT FaqUsers(LastName, FirstName) VALUES('Petrovic', 'Ivan')
INSERT FaqUsers(LastName, FirstName) VALUES('Russell', 'Leon')
INSERT FaqUsers(LastName, FirstName) VALUES('Simpson', 'Homer')
INSERT FaqUsers(LastName, FirstName) VALUES('Sizlack', 'Moe')
INSERT FaqUsers(LastName, FirstName) VALUES('Sizzlean', 'Bacon')
INSERT FaqUsers(LastName, FirstName) VALUES('Thomsen', 'Inge')
INSERT FaqUsers(LastName, FirstName) VALUES('Thomson', 'Ted')
INSERT FaqUsers(LastName, FirstName) VALUES('Thompson', 'Mona')
INSERT FaqUsers(LastName, FirstName) VALUES('Trascal', 'Ronnie')
INSERT FaqUsers(LastName, FirstName) VALUES('Udelay', 'Heyhoo')
INSERT FaqUsers(LastName, FirstName) VALUES('VanWyck', 'Lisa')
INSERT FaqUsers(LastName, FirstName) VALUES('Washington', 'Malivai')
INSERT FaqUsers(LastName, FirstName) VALUES('Weatherspoon', 'Dumb')
INSERT FaqUsers(LastName, FirstName) VALUES('Witheringham', 'Hiriam')
GO

So, now we have some sample data. Let's say we want to show 10 of these names per page, and show nice pretty links as described above.

Here is a stored procedure that will produce two recordsets; the first contains the HTML that produces the links to the different divisions, and the second produces the 10 names for the current set.

CREATE PROCEDURE dbo.showNames
@divID INT = 1
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #nameDivs
(
divID INT IDENTITY(1,1),
f VARCHAR(32) NOT NULL,
l VARCHAR(32) NOT NULL,
link BIT NOT NULL DEFAULT 0
)

WHILE 1 = 1
BEGIN
IF EXISTS (SELECT 1 FROM #nameDivs)
BEGIN
INSERT #nameDivs(f, l, link)
SELECT
MIN(LastName),
MAX(LastName),
link = CASE
WHEN @divID =
(
SELECT MAX(divID)+1
FROM #nameDivs
) THEN 0 ELSE 1 END
FROM
(
SELECT TOP 10 LastName
FROM FaqUsers f
WHERE LastName >
(
SELECT MAX(l)
FROM #nameDivs
)
ORDER BY LastName
) x
END
ELSE
BEGIN
INSERT #nameDivs(f, l, link)
SELECT
MIN(LastName),
MAX(LastName),
link = CASE WHEN @divID = 1
THEN 0 ELSE 1 END
FROM
(
SELECT TOP 10 LastName
FROM FaqUsers f
ORDER BY LastName
) x
END

IF EXISTS
(
SELECT 1 FROM #nameDivs
WHERE l =
(
SELECT MAX(lastName)
FROM FaqUsers
)
)
BEGIN
BREAK
END
END

DECLARE @maxl VARCHAR(32), @minl VARCHAR(32), @maxDiv INT

SELECT
@minl = MIN(f),
@maxl = MAX(l),
@maxDiv = MAX(DivID)
FROM #nameDivs

SELECT [html] =
CASE WHEN f = @minl THEN '[' ELSE '|' END
+ ' ' + CASE WHEN link = 1 THEN
'<a href=shownames.asp?divID='+RTRIM(DivID)+'>'
+ f + '...' + l + '</a>'
ELSE '' + f + '...' + l + '' END
+ ' ' + CASE WHEN l = @maxl THEN ']'
ELSE '' END
FROM #nameDivs ORDER BY DivID

DECLARE @fLN VARCHAR(32), @lLN VARCHAR(32)

SELECT @divID = CASE
WHEN @divID > @maxDiv THEN @maxDiv
ELSE @divID END

SELECT
@fLN = f,
@lLN = l
FROM #nameDivs
WHERE DivID = @divID

SELECT
FirstName,
LastName
FROM FaqUsers
WHERE LastName BETWEEN @fLN AND @lLN
ORDER BY LastName, FirstName

DROP TABLE #nameDivs
END
GO

So from shownames.asp, you would call it like this:

<%
divID = request.querystring("divID")
if not isnumeric(divID) then divID = 1
sql = "EXEC dbo.showNames " & divID
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute(sql)
do while not rs.eof
response.write rs(0)
rs.movenext
loop
response.write "<hr>"
set rs = rs.nextrecordset()
do while not rs.eof
response.write rs("FirstName") & " "
response.write rs("LastName") & " "
rs.movenext
loop
rs.close
set rs = nothing
conn.close
set conn = nothing
%>

And the results would look something like this:

Aardvark...Da Vinci

Dafoe...Hatcher

Hazard...Nincombe

Osprey...Trascal

Udelay...Witheringham

And another request from Laphan

Laphan wanted a combination of the above, where all letters were shown (regardless of whether they had any actual data). Those that had data would be linked, and those that did not, would not.

First, create your names table:

CREATE TABLE Names
(
name VARCHAR(32)
)

Insert some dummy data:

INSERT Names VALUES('33r')
INSERT Names VALUES('9994')
INSERT Names VALUES('Aaron')
INSERT Names VALUES('Bob')
INSERT Names VALUES('Dilbert')
INSERT Names VALUES('Frank')
INSERT Names VALUES('Foobar')
INSERT Names VALUES('Mojo')
INSERT Names VALUES('Zachary')

Now create an alphabet table to left join against:

CREATE TABLE Alphabet
(
letter CHAR(1)
)

And insert the 26 letters (not all included, for brevity):

INSERT Alphabet VALUES('A')
INSERT Alphabet VALUES('B')
INSERT Alphabet VALUES('C')
...
INSERT Alphabet VALUES('X')
INSERT Alphabet VALUES('Y')
INSERT Alphabet VALUES('Z')

Now, to get the numerics, you need this query:

<%
' note: for Access, change = 1 to = TRUE
sql1 = "SELECT COUNT(*) FROM table WHERE ISNUMERIC(LEFT(column, 1))=1"
set rs = conn.execute(sql1)
if rs(0) > 0 then
response.write "<a href=page.asp?letter=0-9>0-9</a>"
else
response.write "0-9"
end if
%>

And to get the letters, you can use a LEFT JOIN:

<%
sql2 = "SELECT n = MIN(N.NAME), a.letter FROM alphabet a" & _
"LEFT JOIN names n ON LEFT(n.name,1) = a.letter" & _
"GROUP BY a.letter ORDER BY a.letter"

set rs = conn.execute(sql2)
do while not rs2.eof
if isnull(rs("n")) then
response.write rs("letter")
else
response.write "<a href=page.asp?letter="
response.write rs("letter") & ">" & rs("letter") & "</a>"
end if
rs.movenext
loop
%>

Yes, I would handle the above in a stored procedure. However I left it this way to illustrate that you could handle this very similarly if you are stuck using Access, like Laphan (seemingly, the only person interested in this article <G>).

Let us know if you have any questions about any of these techniques.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

How do I handle alphabetic paging?

Related Articles