Corrupticus
asked on
Alphabetical Recordset Navigation/Index
I have an dreaweaver-built ASP page that displays the results of an access DB via the recordset derived from a DB query. My asp page has buttons that sort (via URL params) the Access DB results by column name - using order by ASC or DESC. I page through the recordset at 15 records per page.
They way the numeric paging works now is that when the next/previous page is clicked on there's a variable called offset that gets appended to the URL that changes with the page #. For instance, when the page first loads (page 1) the offset is 0. For the next page (page 2) with the next set of records, the offset is 15, the next page offset is 30 (in line with the 15 results per page setup) and so forth.
When a user sorts the results of the DB by the column "company", for instance, they have keep paging thru the recordset to get to a company beginning with a certain letter. Even though the results can be sorted ASC or DESC, a user still has to keep flipping thru the pages to get to a company whose name begins with say letter "J". What I'd like to do is create an alphabetical index at the bottom of the ASP page, underneath the numeric page numbers, that allows users to click on a given letter (e.g. "J") and have them taken to the section of the recordset with companies that begin with letter "J"
This way I I have the offset/page jumping to wherever wherever it is in the recordset that the company starts with "J" or whatever letter has been clicked on. The offset for letter "J" is offset 201 (page 14) - which displays the results for companies that begin with J and whatever else follows in the recordset. The user can then keep paging thru the recordset numerically (using the pages) if they choose to, to see the next set of records - any left-over Js & K's and beyond.
Hardcoding the offsets to the letters would not be very feasible in the long-run b/c if new records are added, the offsets will surely change, and will no longer be accurate. Is there any way to programmatically assign a letter to an offset/page #, such that when the letter is clicked on, the user can "jump" to the section of the recordset where the records begin with the clicked on letter?
Any assistance with this (esp with examples) will greatly be appreciated!
I've included the numeric paging code (a dreamweaver extension by tom muck) which might be useful in helping to see how the paging (and use of offset) works:
VARIABLES USED IN THE CODE:
MM_Size = rsProposals_numRows
rsProposals_numRows = rsProposals_numRows + Repeat1_numRows
Repeat1_numRows = 15 'i.e. 15 records per page
MM_keepMove basically keeps track of what URL parameters are to be maintained - in this case the DB column to be sorted by and the direction of sort - ASC or DESC
Note: 9 pages are displayed at a go - i.e. one can choose to view/browse the results of up to 9 pages at a go
'This section shows/navigates back to the Previous 9 pages
<%
If MM_offset > MM_size * 9 Or MM_offset/MM_size - int(9/2) > 0 Then
TFM_Previous = MM_offset - 10 * MM_size
If TFM_Previous < 0 then TFM_Previous = 0
Response.Write(".<a href=""" & Request.ServerVariables("U
Response.Write("Previous " & "9" & " pages</a> ")
End If
%>
''This section shows the current 9 pages a viewer can click on to view results for : i.e. e.g. -> 10 11 12 13 14 15 16 17 18
<%
TFM_MiddlePages = 9 ' # of pages to be displayed at a time
TFM_delimiter = " " ' a space separates the page #s
TFM_startLink = MM_offset + 1 - MM_size * (int(TFM_middlePages/2))
If MM_offset > 0 Then TFM_LimitPageEndCount = int(TFM_startLink/MM_size)
If TFM_startLink < 1 Then
TFM_startLink = 1
TFM_LimitPageEndCount = 0
End If
TFM_endLink = MM_size * TFM_MiddlePages + TFM_startLink - 1
If TFM_endLink > rsProposals_total Then TFM_endLink = rsProposals_total
For i = TFM_startLink to TFM_endLink Step MM_size
TFM_LimitPageEndCount = TFM_LimitPageEndCount + 1
if i <> MM_offset + 1 Then
Response.Write("<a href=""" & Request.ServerVariables("U
Response.Write(TFM_LimitPa
else
Response.Write("<font color='#FF0000'> ") ' change color of currently displayed page (which is now unclickable)
Response.Write(TFM_LimitPa
End if
if(i <= TFM_endLink - MM_size) then Response.Write(TFM_delimit
Next
%>
'This section shows/navigates to the Next 9 pages
<%
If MM_offset < rsProposals_total - MM_size * 9 Then
Response.Write(" <a href=""" & Request.ServerVariables("U
Response.Write("Next " & "9" & " of " & rsProposals_TFMtotalPages & " pages</a> ")
End If
%>
The actual paging text displayed on the ASP page looks something like:
1 2 3 4 5 6 7 8 9 Next 9 of 26 pages ' for offset = 0 -> 1 is colored red (#FF0000) and is unclickable
.Previous 9 pages 3 4 5 6 7 8 9 10 11 Next 9 of 26 pages ' for offset = 90 -> 7 is colored red and unclickable now
.Previous 9 pages 21 22 23 24 25 26 ' for offset = 375 - the last of the records in the recordset -> 26 is red & unclickable
Hope this helps!
They way the numeric paging works now is that when the next/previous page is clicked on there's a variable called offset that gets appended to the URL that changes with the page #. For instance, when the page first loads (page 1) the offset is 0. For the next page (page 2) with the next set of records, the offset is 15, the next page offset is 30 (in line with the 15 results per page setup) and so forth.
When a user sorts the results of the DB by the column "company", for instance, they have keep paging thru the recordset to get to a company beginning with a certain letter. Even though the results can be sorted ASC or DESC, a user still has to keep flipping thru the pages to get to a company whose name begins with say letter "J". What I'd like to do is create an alphabetical index at the bottom of the ASP page, underneath the numeric page numbers, that allows users to click on a given letter (e.g. "J") and have them taken to the section of the recordset with companies that begin with letter "J"
This way I I have the offset/page jumping to wherever wherever it is in the recordset that the company starts with "J" or whatever letter has been clicked on. The offset for letter "J" is offset 201 (page 14) - which displays the results for companies that begin with J and whatever else follows in the recordset. The user can then keep paging thru the recordset numerically (using the pages) if they choose to, to see the next set of records - any left-over Js & K's and beyond.
Hardcoding the offsets to the letters would not be very feasible in the long-run b/c if new records are added, the offsets will surely change, and will no longer be accurate. Is there any way to programmatically assign a letter to an offset/page #, such that when the letter is clicked on, the user can "jump" to the section of the recordset where the records begin with the clicked on letter?
Any assistance with this (esp with examples) will greatly be appreciated!
I've included the numeric paging code (a dreamweaver extension by tom muck) which might be useful in helping to see how the paging (and use of offset) works:
VARIABLES USED IN THE CODE:
MM_Size = rsProposals_numRows
rsProposals_numRows = rsProposals_numRows + Repeat1_numRows
Repeat1_numRows = 15 'i.e. 15 records per page
MM_keepMove basically keeps track of what URL parameters are to be maintained - in this case the DB column to be sorted by and the direction of sort - ASC or DESC
Note: 9 pages are displayed at a go - i.e. one can choose to view/browse the results of up to 9 pages at a go
'This section shows/navigates back to the Previous 9 pages
<%
If MM_offset > MM_size * 9 Or MM_offset/MM_size - int(9/2) > 0 Then
TFM_Previous = MM_offset - 10 * MM_size
If TFM_Previous < 0 then TFM_Previous = 0
Response.Write(".<a href=""" & Request.ServerVariables("U
Response.Write("Previous " & "9" & " pages</a> ")
End If
%>
''This section shows the current 9 pages a viewer can click on to view results for : i.e. e.g. -> 10 11 12 13 14 15 16 17 18
<%
TFM_MiddlePages = 9 ' # of pages to be displayed at a time
TFM_delimiter = " " ' a space separates the page #s
TFM_startLink = MM_offset + 1 - MM_size * (int(TFM_middlePages/2))
If MM_offset > 0 Then TFM_LimitPageEndCount = int(TFM_startLink/MM_size)
If TFM_startLink < 1 Then
TFM_startLink = 1
TFM_LimitPageEndCount = 0
End If
TFM_endLink = MM_size * TFM_MiddlePages + TFM_startLink - 1
If TFM_endLink > rsProposals_total Then TFM_endLink = rsProposals_total
For i = TFM_startLink to TFM_endLink Step MM_size
TFM_LimitPageEndCount = TFM_LimitPageEndCount + 1
if i <> MM_offset + 1 Then
Response.Write("<a href=""" & Request.ServerVariables("U
Response.Write(TFM_LimitPa
else
Response.Write("<font color='#FF0000'> ") ' change color of currently displayed page (which is now unclickable)
Response.Write(TFM_LimitPa
End if
if(i <= TFM_endLink - MM_size) then Response.Write(TFM_delimit
Next
%>
'This section shows/navigates to the Next 9 pages
<%
If MM_offset < rsProposals_total - MM_size * 9 Then
Response.Write(" <a href=""" & Request.ServerVariables("U
Response.Write("Next " & "9" & " of " & rsProposals_TFMtotalPages & " pages</a> ")
End If
%>
The actual paging text displayed on the ASP page looks something like:
1 2 3 4 5 6 7 8 9 Next 9 of 26 pages ' for offset = 0 -> 1 is colored red (#FF0000) and is unclickable
.Previous 9 pages 3 4 5 6 7 8 9 10 11 Next 9 of 26 pages ' for offset = 90 -> 7 is colored red and unclickable now
.Previous 9 pages 21 22 23 24 25 26 ' for offset = 375 - the last of the records in the recordset -> 26 is red & unclickable
Hope this helps!
ASKER
if I use a WHERE clause in my select statement, only the records beginning with the chosen letter will be displayed... The user should be able to keep paging thru (both fwd and backward) the entire universe of records after jumping to records beginning with a particular letter in the recordset. For instance, after jumping to letter "J" the user should be able to click on the next/previous page # to see the records K and beyond - or I and before.... Using a WHERE clause in the SQL precludes this...
hello Corrupticus,
first of all, very long questions with less points doesn't encourage experts to read and solve ur problem.
second:
through my whole years of experience in web surfing and development, i have never such an idea.
using offsets for paging is nice, and also static that will not be changes.except u decided to change no of results per page.
but as a concept. if there were an alphabetical index in any page, that means a small query that searches all records in the database that starts "only" with this alphabet.
u can make a survey for people around u and ur friends, ask them if u found this alphabetical index, what will it indecate.
1) just all records starting with this alphabet
2) all records starting with this alphabet and alphabets larger than it.
and let me know
first of all, very long questions with less points doesn't encourage experts to read and solve ur problem.
second:
through my whole years of experience in web surfing and development, i have never such an idea.
using offsets for paging is nice, and also static that will not be changes.except u decided to change no of results per page.
but as a concept. if there were an alphabetical index in any page, that means a small query that searches all records in the database that starts "only" with this alphabet.
u can make a survey for people around u and ur friends, ask them if u found this alphabetical index, what will it indecate.
1) just all records starting with this alphabet
2) all records starting with this alphabet and alphabets larger than it.
and let me know
ASKER
To encourage experts to participate, I'm doubling the points...
Kanary, the answer to your question would be a modified 2) - i.e. Index should allow the display of all records starting with the given letter and alphabets larger (if you hit the next page link) or smaller (if you hit the previous page link) than it. I basically need a way to find out what the offset is for the first record that begins with the given letter - this way, from this offset, a user can page both front and back as needed.
My aim is to:
1) Find the first record(s) begining with the clicked on letter
2) Get the offset for that record
3) Assign that offset to the respective letter
I'm trying to achieve 1) using a regular expression, but it's not quite working though because I'm not very good at using regular expressions.
Here is my code so far...
VARIABLES USED
rsProposals is the recordset
<%
delimiter = " "
Dim recSetCol, urlString, results
recSetCol = rsProposals("company") 'the column in the recordset that I am looking for a particular letter in
function findRecords(ltr,rSetCol)
dim re,matches,letter
letter = ltr
set re = new RegExp
re.pattern = "^"&letter&".*?" ' I'm trying to look for the beginning of the company name starting with the letter in question
re.IgnoreCase = True
set matches = re.execute(rSetCol)
if matches.count then
findRecords = matches(0) I need help using this - I'm trying to print out the results of the match - this shows 1st letter only
else
findRecords = ""
end if
end function
For i = 65 to 90 ' prints A-Z - the letters that a user can click on to see the company names begining with that letter clicked on
TFM_character = Chr(i)
results = findRecords(TFM_character,
Response.Write("<font color='#FF0000'> ")
Response.Write(results & "</font> ") 'trying to see what my regexp did - it actually prints out the first letter corresponding to the recordset results on the current page - i.e. prints out D, if the records on page are D records (there are some C records however), E if the next page shows E records (although there are some D records) - so it at least works somewhat....
Response.Write("<a href=""" & Request.ServerVariables("U
Response.Write(""">" + TFM_character + "</a>")
If(i<90)Then Response.Write(delimiter)
Next
%>
Kanary, the answer to your question would be a modified 2) - i.e. Index should allow the display of all records starting with the given letter and alphabets larger (if you hit the next page link) or smaller (if you hit the previous page link) than it. I basically need a way to find out what the offset is for the first record that begins with the given letter - this way, from this offset, a user can page both front and back as needed.
My aim is to:
1) Find the first record(s) begining with the clicked on letter
2) Get the offset for that record
3) Assign that offset to the respective letter
I'm trying to achieve 1) using a regular expression, but it's not quite working though because I'm not very good at using regular expressions.
Here is my code so far...
VARIABLES USED
rsProposals is the recordset
<%
delimiter = " "
Dim recSetCol, urlString, results
recSetCol = rsProposals("company") 'the column in the recordset that I am looking for a particular letter in
function findRecords(ltr,rSetCol)
dim re,matches,letter
letter = ltr
set re = new RegExp
re.pattern = "^"&letter&".*?" ' I'm trying to look for the beginning of the company name starting with the letter in question
re.IgnoreCase = True
set matches = re.execute(rSetCol)
if matches.count then
findRecords = matches(0) I need help using this - I'm trying to print out the results of the match - this shows 1st letter only
else
findRecords = ""
end if
end function
For i = 65 to 90 ' prints A-Z - the letters that a user can click on to see the company names begining with that letter clicked on
TFM_character = Chr(i)
results = findRecords(TFM_character,
Response.Write("<font color='#FF0000'> ")
Response.Write(results & "</font> ") 'trying to see what my regexp did - it actually prints out the first letter corresponding to the recordset results on the current page - i.e. prints out D, if the records on page are D records (there are some C records however), E if the next page shows E records (although there are some D records) - so it at least works somewhat....
Response.Write("<a href=""" & Request.ServerVariables("U
Response.Write(""">" + TFM_character + "</a>")
If(i<90)Then Response.Write(delimiter)
Next
%>
hello Corrupticus again,
i have thinked in ur problem well, im going to provide a soluition to ur problem but bot the code, if u have any problems with the code i think u should ask in the asp forum, ok.
here's the scenario:
1) define no of results per page at first, let's say: pagesize=20
2) now u search the database, let's say the result were 1000 records reurned.
suppose u were searching for certain companies, the search result returns company specifications and locations, let the search result return company IDs also.
now u have the company ID's that have been returned from the search, and these companies are the one who should be indexed by it's first Alphabet.
3) create an array of integers and store all the company IDs into it, and store this array into session array.
now this session array will be in ur hand while browsing next or previous as u wish.
4) create another session variable telling u the page number u r in, say: pageno=1
when the pageno = 1 u list the first 20 records (0 -> 20*1) from the Company ID's array
when pageno = 2 u list the next 20 records (20*1 -> 20*2) which is (pagesize* pageno) and so on.
using this way, add as many records as u wish, change no of result per page as u wish, u won't have to store a certain offset value per each page at all.
also this will solve ur problem, how?
now u wana index the result alphabetically, suppose u clicked the alphabet "C". now u will search the result array for the first occurance of the "C". as long as we have 1000 company IDs resulted from the search, suppose that the first occurance of the "C" is at record no 105 from the 1000.
using calculations u will get that if the page size is 20, so we will have to press next 5 times to get to index 105.
and the page no will be no 6 pageno=6.
pageno=Mod(1000/105)
i have thinked in ur problem well, im going to provide a soluition to ur problem but bot the code, if u have any problems with the code i think u should ask in the asp forum, ok.
here's the scenario:
1) define no of results per page at first, let's say: pagesize=20
2) now u search the database, let's say the result were 1000 records reurned.
suppose u were searching for certain companies, the search result returns company specifications and locations, let the search result return company IDs also.
now u have the company ID's that have been returned from the search, and these companies are the one who should be indexed by it's first Alphabet.
3) create an array of integers and store all the company IDs into it, and store this array into session array.
now this session array will be in ur hand while browsing next or previous as u wish.
4) create another session variable telling u the page number u r in, say: pageno=1
when the pageno = 1 u list the first 20 records (0 -> 20*1) from the Company ID's array
when pageno = 2 u list the next 20 records (20*1 -> 20*2) which is (pagesize* pageno) and so on.
using this way, add as many records as u wish, change no of result per page as u wish, u won't have to store a certain offset value per each page at all.
also this will solve ur problem, how?
now u wana index the result alphabetically, suppose u clicked the alphabet "C". now u will search the result array for the first occurance of the "C". as long as we have 1000 company IDs resulted from the search, suppose that the first occurance of the "C" is at record no 105 from the 1000.
using calculations u will get that if the page size is 20, so we will have to press next 5 times to get to index 105.
and the page no will be no 6 pageno=6.
pageno=Mod(1000/105)
sorry, delete the last line.
page no = 105/20 = 5.25
that means the page after pafe 5 , yet to page 6
so will return the same result if u were not searching for a certain alphabet, except for that u will display page 6 at first, that means u have searched the result according to the charachter selected and the next and previous navigation are as normal as the ordinary search.
what do u think
page no = 105/20 = 5.25
that means the page after pafe 5 , yet to page 6
so will return the same result if u were not searching for a certain alphabet, except for that u will display page 6 at first, that means u have searched the result according to the charachter selected and the next and previous navigation are as normal as the ordinary search.
what do u think
don't u think this idea worth 500 points :))
why not completely do away with this search step and just use a dynamic drop down list on the search form page? this way the user can just type in or choose the company and not have to deal with paging through to get what they want.
hello Corrupticus,
i have given u an excellent solution to ur problem.
so i think u should reward the points and end the question by now.
waiting ur action here.
thx a lot.
i have given u an excellent solution to ur problem.
so i think u should reward the points and end the question by now.
waiting ur action here.
thx a lot.
ASKER
Kanary, first of all, I don't know why you assume that you gave me an "excellent solution" that you think is worth 500 points. I don't understand how you arrived at this conclusion. To cut to the chase and make the long story short, I'll reiterate, from my previous comments above, what I was looking for:
comment 1:
-> Is there any way to programmatically assign a letter to an offset/page #, such that when the letter is clicked on, the user can "jump" to the section of the recordset where the records begin with the clicked on letter?
-> Any assistance with this (esp with examples) will greatly be appreciated!
-> I've included the numeric paging code (a dreamweaver extension by tom muck) which might be useful in helping to see how the paging (and use of offset) works
If u read my comments and take a closer look at the code I provided, you will realize I already have in place a navigation scheme that defines, calculates and displays the number of records per page - 15 records per page. Which means I always know what page I'm on - even if I manually change the offset parameter (which gets translated to a page number by the code). I would also have know what the total # of records returned is (which is how the total number of pages is determined) Hence - I don't need to go thru steps 1 & 2 of your 'solution' or redundantly use session arrays as you suggested in points 3 & 4 of your comment. Why create a superfluous array to search thru, when I can search thru my recordset??
Again, from my comments above:
My aim is to:
1) Find the first record(s) begining with the clicked on letter
2) Get the offset for that record
3) Assign that offset to the respective letter
I have figured out how to find the first record beginning with whatever letter is clicked on. What's left is finding out what offset/page number the record is on and dynamically assign that offset to the respective letter each time the page loads - such that when the user clicks on that letter, they are taken straight to the respective page. If you or anyone else can help me find a way to do this, (and not ask that I go to the asp forum for help with code - why reward the same question twice in different forums) then we can start talking points. Till then, feel free to share any meaningful ideas you may have - thanks!
comment 1:
-> Is there any way to programmatically assign a letter to an offset/page #, such that when the letter is clicked on, the user can "jump" to the section of the recordset where the records begin with the clicked on letter?
-> Any assistance with this (esp with examples) will greatly be appreciated!
-> I've included the numeric paging code (a dreamweaver extension by tom muck) which might be useful in helping to see how the paging (and use of offset) works
If u read my comments and take a closer look at the code I provided, you will realize I already have in place a navigation scheme that defines, calculates and displays the number of records per page - 15 records per page. Which means I always know what page I'm on - even if I manually change the offset parameter (which gets translated to a page number by the code). I would also have know what the total # of records returned is (which is how the total number of pages is determined) Hence - I don't need to go thru steps 1 & 2 of your 'solution' or redundantly use session arrays as you suggested in points 3 & 4 of your comment. Why create a superfluous array to search thru, when I can search thru my recordset??
Again, from my comments above:
My aim is to:
1) Find the first record(s) begining with the clicked on letter
2) Get the offset for that record
3) Assign that offset to the respective letter
I have figured out how to find the first record beginning with whatever letter is clicked on. What's left is finding out what offset/page number the record is on and dynamically assign that offset to the respective letter each time the page loads - such that when the user clicks on that letter, they are taken straight to the respective page. If you or anyone else can help me find a way to do this, (and not ask that I go to the asp forum for help with code - why reward the same question twice in different forums) then we can start talking points. Till then, feel free to share any meaningful ideas you may have - thanks!
hello again,
i won't be able to help as im too bust these days.
u can ask a new question inasp withoit doubling the points.
open a new questio in asp and paste the url of this forum,
if u find an answer there u can ask the administrators to delete this question
wishing u good luck.
kanary.
i won't be able to help as im too bust these days.
u can ask a new question inasp withoit doubling the points.
open a new questio in asp and paste the url of this forum,
if u find an answer there u can ask the administrators to delete this question
wishing u good luck.
kanary.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mypage.asp?offset=0 - displays initial set of 15 records according to the alphabetic sort order (page 1)
mypage.asp?offset=15 - next 15 records (page 2)
mypage.asp?offset=30 - next 15 (page 3)
mypage.asp?offset=45 - etc (page 4)
From page 4, one can go (by typing in or clicking on the page links) to mypage.asp?offset=201 (page 14) which shows the results for companies beginning with the letter J. One can then hit the back button on the browser to take them back to page 4 (offset=45) and keep sequentially paging thru the recordset to page 5 (mypage.asp?offset=60)
or keep going forward from page 14 (offset 201) to page 15 (offset=216) and beyond.
This is the sort of flexibility I'm looking to have, by dynamically (not hardcoding) assigning letters to page #s (offset#s)