SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: SQL Question?

  1. #1
    SitePoint Enthusiast cjam4's Avatar
    Join Date
    Jul 2001
    Location
    NJ
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Guru Husain's Avatar
    Join Date
    Sep 2001
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru Husain's Avatar
    Join Date
    Sep 2001
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


  5. #5
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 .
    Nick Wilson [ - email - ]

  6. #6
    SitePoint Guru Husain's Avatar
    Join Date
    Sep 2001
    Posts
    620
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Enthusiast
    Join Date
    Aug 2001
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    SiteTell.com: Get the best viral marketing tool on the planet and watch as the number of unique visitors to your site soars!

  8. #8
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    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.

    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!

  12. #12
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    SitePoint Zealot
    Join Date
    Oct 1999
    Location
    France
    Posts
    141
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ...
    --
    Veronica Yuill
    Archetype IT


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •