SQL Question?

[cjam4]

I have seen some site use something like this in their URLs:

http://www.website.com/default.asp?start=1&end=20

This in turn would return the first 20 records from a database. Then there would be a link that would have the following URL:

http://www.website.com/default.asp?start=21&end=40

This would return the next 20 records.

How do you actually inplement this? What is the correct query to do something like this?

Thanks

[Husain]

Here are the steps to do that:

1. First count the number of records in your table
2. Divide that by the number of records you want to show on each page. For example if you have 100 records in your database, and you want to show 10 on each page, 100/10 = 10 pages.
3. Query your database like you would normally (SELECT * FROM MyTable)
4. Read the querystring called 'start' and move the pointer to that record. For example:

        Offset = request.querystring("offset")
        set rec = server.createobject("ADODB.recordset")
        rec.open "SELECT * FROM MyTable", conn
        rec.move Offset

5. Do a while loop that will loop 10 times to show the next 10 records from your table.

That's it. I hope I have been easy to understand.

Husain.

[Husain]

You are doing step 2 so that you can display the list of pages your visitors can then select.
Like Page: 1 | 2 | 3 | 4

Each number will be linked like this:
<a href="<%= request.servervariables("URL") %>?offset=10">1</a>

For page 2, the offset value will be incremented by 10 (so it will be 20)

Hope this helps.

[addstravel]

What about returning only the pages recordset to avoid returning all 100 records when only 10 are required?

I know this is possible...but can you do this without writing a temporary table?

cheers

[CrazyCrane]

On certain databases it's easily possible - Oracle for example would let you use something like "... WHERE ROWNUM BETWEEN 20 and 31" or whatever, while MySQL has the LIMIT clause which performs a similar function. MS SQL only has the TOP clause, which obviously only selects the first records.

Within ASP itself you can page recordsets, but all the records are still brought back :/.

Soooo... as far as I know, with certain databases you're pretty limited. If you can't think of a specific where clause to limit your records, you need to bring them all back or use a temporary table or view to limit it further.

If anyone knows a solution though, I'd love to see it .

[Husain]

Yup, it depends on the database. Access, for example does not support returning limited records. So for databases that do not support limited record, the only solution I can think of is getting all the records and displaying only the ones you want. This will obviously mean compromising performance.

[SpyderMan]

hey,
You can easily do it on MS SQL Server and MySQL as well. For mySQL use the limit keyword:

"SELECT * FROM MyTable LIMIT 11, 10"

This would select rows 11-21

In MS SQL Server, You set it up by modifying the AbsolutePage and PageSize values of the recordset object, here's a good link: http://www.freevbcode.com/ShowCode.Asp?ID=882

[dhtmlgod]

I take that no one has heard of RS Paging?

Here is the code I use:

if isempty(request.querystring("PageNo")) OR request.querystring("PageNo") = "" then
		CurrPage = 1
	else
		CurrPage = cint(request.querystring("PageNo"))
	end if
	Set news = Server.CreateObject("ADODB.Recordset")
	news.CursorLocation = 3
	news.Open "SQL", connectionString
	news.PageSize = 3 ' Number of records per page	news.AbsolutePage = CurrPage
	DO UNTIL news.AbsolutePage <> CurrPage OR news.EOF
		' Loop records here
		news.MoveNext
	LOOP
RSPrevPage = CurrPage -1
	RSNextPage = CurrPage + 1
	next10 = getNext10(currPage)
	prev10 = getPrev10(currPage)
	if Next10 > news.PageCount then
		next10 = news.PageCount
	end if
	if prev10 = 1 AND next10 - 1 < 10 then
		start = 1
	else
		start = Next10 - 10
		if right(start, 1) > 0 then
			start = replace(start, right(start, 1), "0")
			start = start + 10
		end if
	end if

	if news.PageCount > 1 then
		if next10 > 10 then
			response.write("<p class=""para1""><a href=""" & request.servervariables("SCRIPT_NAME") & "?PageNo=" & Prev10 & " &searchText=" & server.urlencode(request("searchtext")) & """ " & linkStyle & "><<</a>   ")
		end if
		if not RSPrevPage = 0 then
			response.write("<a href=""" & request.servervariables("SCRIPT_NAME") & "?PageNo=" & RSPrevPage & "&searchText=" & server.urlencode(request("searchtext")) & """ " & linkStyle & "><</a>   ")
		end if
		for P = start to Next10
			if not P = CurrPage then
				response.write("<a href=""" & request.servervariables("SCRIPT_NAME") & "?PageNo=" & P & "&searchText=" & server.urlencode(request("searchtext")) & """ " & linkStyle & ">" & P & "</a> ")
			else
				response.write(" <b>" & P & " </b>")
			end if
		Next
		if not RSNextPage > news.PageCount then
			response.write("<a href=""" & request.servervariables("SCRIPT_NAME") & "?PageNo=" & RSNextPage & "&searchText=" & server.urlencode(request("searchtext")) & """ " & linkStyle & ">></a>  ")
		end if
		if not Next10 = news.PageCount then
			response.write("   <a href=""" & request.servervariables("SCRIPT_NAME") & "?PageNo=" & Next10 & "&searchText=" & server.urlencode(request("searchtext")) & """ " & linkStyle & ">>></a>")
		end if
	end if
	SET news = nothing

There is a little crap there that I'm sure you can take out, if you can't I'll do it tomorrow.

Now the two functions (getNext10 & getPrev10):

function getNext10(num)
	pageLen = len(num)
	if pageLen = 1 then
	next10 = 10
	elseif pageLen = 2 then
		pageRem = 10
		pageTen = right(num, 1)
		next10 = num + pageRem - pageTen
	elseif pageLen > 2 then
		pageRem = 10
		pageTen = right(num, 1)
		next10 = num + pageRem - pageTen
	end if
	getNext10 = next10
end function
function getPrev10(num)
	pageLen = len(num)
	if pageLen = 1 then
	prev10 = 1
	elseif pageLen = 2 then
		firstDig = left(num, 1)
		secondDig = right(num, 1)
		prev10 = num - secondDig - 10
	elseif pageLen > 2 then
		firstDig = right(num, 2)
		secondDig = right(num, 1)
		prev10 = num - secondDig - 10
	end if
	if prev10 = 0 then
		prev10 = 1
	end if
	getPrev10 = prev10
end function

Hope that helps, if you need help with it, let me know.

[MattR]

If you're using MS SQL Server or Sybase I would (and have) do it in a stored procedure. One that takes the next page to show would be fine:

CREATE PROCEDURE dbo.sp_get_whatever
    @pagenumber INT,
    @perpage INT
WITH RECOMPILE
AS

   IF @pagenumber = 1
   BEGIN

    SET ROWCOUNT @perpate

    SELECT *
      FROM table
   END
   ELSE
   BEGIN
   DECLARE @min_postid NUMERIC( 8, 0 ),
           @position   INT

   SELECT @position = @perpage *
          ( @pagenumber - 1 ) + 1

   SET ROWCOUNT @position

   -- What happens here is it will select through the rows and order the whole set.
   -- It will push tableid into @min_tableid until it hits your ROWCOUNT and does
   -- this out of the ordered set (a work table).

   SELECT @min_id = tableid
     FROM table

   SET ROWCOUNT @perpage

   -- we know where we want to go (say the 28th item in a set of 50).
    SELECT *
      FROM table
     WHERE tableid >= @min_tableid
  END

This relies upon Sybase T-SQL; for MS SQL you can get rid of 'SET ROWCOUNT' and then alter the SELECT statements to use SELECT TOP x. As I said before, this is far preferable to select all 100 rows and then only display a certain subset. My method also relies upon a definable primary key -- in this case it is an IDENTITY column (incrementing integer) so that you know where to start. If you don't have an identity column then you would have to utilize a temp table and some other tricks which are less efficient than this method.

[shane]

An interesting method Matt. The only method of doing paging in SP's that I'd seen used a temp table, which I didn't like, and couldn't think of any other way myself.

There's still one downside of using SP's to get your RS and that's that you can't use a variable in the ORDER BY clause. As I often use clickable data table headings, to re-sort the data, I often just use SP's to retrieve the RS and then apply the ADO RS's sort property and then page the RS. Any ideas on a better approach?

Also, on SQL Server you can't use a variable with the TOP keyword so you'd still have to use the SET ROWCOUNT n statement.

[MattR]

Originally posted by shane
An interesting method Matt. The only method of doing paging in SP's that I'd seen used a temp table, which I didn't like, and couldn't think of any other way myself.

My method basically says "If you are on page one, simply grab the first X rows ORDER BY something". "If not, find the ID value of the first record that should be displayed by counting up to number of pages * perpage. Then grab the first X rows where the ID value is greater than or equal to the id value of the first one to show". It is kind of hard to work around the logic but it works well and is fairly efficient. It breaks down if you do not have a primary key or other method to determine 'what number is next' sort of thing.

Originally posted by shane
There's still one downside of using SP's to get your RS and that's that you can't use a variable in the ORDER BY clause. As I often use clickable data table headings, to re-sort the data, I often just use SP's to retrieve the RS and then apply the ADO RS's sort property and then page the RS. Any ideas on a better approach?

Well, I know with Sybase if you use Dynamic SQL you can have ORDER BY @var. Otherwise I do something like this:

if @sortby = "somecol"
  SELECT ... ORDER BY somecol
else if @sortby = "someothercol"
  SELECT ... ORDER BY someothercol
else 
  SELECT ... ORDER BY defaultcol

Key here is to use WITH RECOMPILE since, depending on how complex it is, it may forget that you have different ORDER BY's and only cache the first query that you execute. I know Sybase ASE 12.5 appears to work OK without the RECOMPILE but I am not sure about other versions or MS SQL Server.

Originally posted by shane
Also, on SQL Server you can't use a variable with the TOP keyword so you'd still have to use the SET ROWCOUNT n statement.

Ok, my bad. I didn't want to reinvent the wheel on MS SQL so I used my same T-SQL code but figured you could use TOP X somehow. Guess I was wrong!

[shane]

I got the logic Matt. It was just that the only method I'd seen was like this and your's stuck me as pretty nice.

I'll have to try the WITH RECOMPILE statement. The 'IF ELSE...' or 'CASE ...' methods would get kinda ugly catoring for every field in the SELECT clause.

[veronicay]

Here's a link you guys might find useful:

http://www.vbrad.com/pf.asp?p=source/src_page_records.htm

It's actually a VB site but the same principles apply when using ASP/VBScript, and it's a good explanation, with pros and cons given for each method. I tend to use RS paging myself ...