SQL Question?

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

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:

Code:

---

        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.

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.

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

:(

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 :).

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.

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

I take that no one has heard of RS Paging?

Here is the code I use:

Code:

---

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):

Code:

---

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.

:)

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:

Code:

---

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.

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.

Quote:

---

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.

Quote:

---

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:

Code:

---

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.

Quote:

---

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!

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.

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 ...