Good Paging alphabetically tutorial?

[BradleyT]

I have a site that lists peoples names from SQL Server alphabetically.

I have a few problems with this.

1. The routine I use for naming just compares the first letter as a "save" letter. If it's different then it prints out a header letter.

A <--- Header letter
Abby
Anne
Azule

B <--- Header letter
Billy
Bobby

Ok the problem is some people have ' and - in their names (this is for an online game).

So it does:

B
Billy
Bobby
-
-Britanny
B
Brynn

Any help on this? Write a routine that loops through non-alphabetically characters until it reaches an alpha char and compare that?

2. Some pages have over 300 names on them now. It's too long to scroll all the way down. Anyone know a good paging tutorial using alphabetical rather than 1-2-3-4-Next type paging?

Such as setting up A-D, E-H, I-M in place of the 1-2-3-4 etc.

[Meercat]

first off i write all the links out, i use # for "other characters"

<center>
- <a href='?page=memberlist&amp;letter=other'>#</a> -
<%
for x=1 to 26
response.write " <a href='?letter=" & chr(64+x) & "'>" & chr(64+x) & "</a> -"
next
%>
</center>

now heres some SQL to get you going, it checks for a Request.QueryString("letter"), if none is found it displays the last 10 people who logged in

if request.QueryString("letter") = "" then
sSQL = "SELECT TOP 10 Username,LastLogin,Email,ShowEml,clanURL,ImageURL FROM Members ORDER BY LastLogin DESC"
else
if request.QueryString("letter") = "other" then
sSQL = "SELECT Username,LastLogin,Email,ShowEml,clanURL,ImageURL " & _
"FROM Members " & _
"WHERE Username Like '[1234567890{}()$£-]%'" & _
"ORDER BY Username"
else
sSQL = "SELECT Username,LastLogin,Email,ShowEml,clanURL,ImageURL " & _
"FROM Members " & _
"WHERE Username LIKE '" & request.Querystring("letter") & "%'" & _
"ORDER BY Username"
end if
end if

you can then page the returned recordset any way you please, its returned alpabetically.

the only special characters i allow are {}()[]$£-, people whu start their name with square brackets are not listed in the mater listing - but what the hell

[BradleyT]

Thanks very much, got it working like a charm :)

I thought I'd have to use ADO paging functions but that's more for numerical results rather than alphabetical. Although I suppose that could be a possibility if one of the letter categories gets too large in the future.

[cococure]

Hi,

I just recently used this code from Asp101 : [asp101.com...]

Worked like a champ for alphabetical and numerical paging.

[Meercat]

cococure: the good thing about my example is you can

SELECT * FROM Members WHERE Username='A%'

the % simply implies "anything", so we get a recordset returned from the database of all users where the username is A<something> [not case sensitive]

the square brackets are used to imply the first letter

i.e.
[1234567890{}()$£-]% basically means "starts with" 123456789{}()$£- "then anything"

[cococure]

I understand. I just wanted to offer an alternative for when pages get too long. We used it when listing links to products, we initially followed your example and had several hundred links or more on a page. We used paging to keep it around 100 to make sure every link could be accessed by a crawl.

[BradleyT]

Well right now we only have 2091 user profiles listed spread amongst 10 "cities" they can play in. With 28 letters per city (A-Z,-,') there isn't much of a major problem with too many of a particular letter showing up yet.

However the example cococure gave will be good for when (if) we need to impliment scalability to the site.

Right now it's just a hobby site used basically to get something on my resume for when I graduate and to bring in a couple extra hundred bucks a month :)