SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to do this in sql

    hi,
    i have a long recordset that i want to page through. can sql pass back say, 5 records from 20 to 25 out of 1000?
    i can then pass 2 variables between the pages to dynamically generate the sql and thus page through the results. can someone post up a simple example of this if its possible?
    thanks

  2. #2
    SitePoint Zealot trekmp's Avatar
    Join Date
    Dec 2005
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why do we exist? What is our purpose?
    >H2O Developments
    >USS Endeavour
    >Hosted@Servage

  3. #3
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for those, do you have any jscript examples?

  4. #4
    SitePoint Zealot trekmp's Avatar
    Join Date
    Dec 2005
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No sorry don't use jscript, but I don't think it would be much to convert the code
    Why do we exist? What is our purpose?
    >H2O Developments
    >USS Endeavour
    >Hosted@Servage

  5. #5
    SitePoint Enthusiast Northern Star's Avatar
    Join Date
    Aug 2006
    Location
    Cheshire, UK
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the script I use for paging... it's by no means perfect, and I know there are other ways of achiving the same code but it allows for plenty of additional tinkering...

    Code:
    'GET THE TOTAL NUMBER OF RECORDS
    SQL = "SELECT * FROM Vehicles"
    Set RS = DB.Execute(SQL)
    								
    intPage = 0
    While Not RS.EOF
    intPage = intPage + 1
    RS.MoveNext
    Wend
    
    
    	
    'SET THE NUMBER OF RECORDS PER PAGE [intPPage]
    intPPage = 10
    intSPage = intPPage - 1
    
    'WORK OUT THE NUMBER OF PAGES
    k = 1
    t = intPage / intPPage
    If InStr(t,".") > 0 Then
         j=Int(t) + 1
    Else
         j=Int(t)
    End If
    
    'WRITE OUT THE PAGES AS LINKS
    Response.Write "<p>There are currently <strong>" & intPage & "</strong> vehicles in the database.</p>"
    
    Response.Write "<p>Page: "
    	
         aClass = ""
    	
         For i = 1 to j
    	
              If Int(Request.QueryString("page")) = (i*intPPage-1)-intSPage Then
                   aClass = "pgactive"
              Else
                   aClass = ""
              End If
    		
              Response.Write "<a href=""?page=" & (i*intPPage-1)-intSPage & "&pp=" & intPPage & "&sortby=" & Request.QueryString("sortby") & "&order=" & Request.QueryString("order") & """ class=""" & aClass & """>" & k & "</a>, "
    
         k = k + 1
    	
         Next
    
    Response.Write "</p>"
    	
    'GO TO THE FIRST RECORD (THE RECORDSET IS STILL OPEN FROM OUR PREVIOUS COUNT)
    RS.MoveFirst						
    	
    If Request.Querystring("page") = "" then
         page = 0
    Else
         page = Request.Querystring("page")
    End If
    		
    If page > 0 Then
    
         'MOVE THE RECORDS ON TO THE APPROPRIATE PAGE
         For i = 1 to page
              RS.MoveNext
         Next
    
    End If
    		
    intPage2 = 0
    	
    rClass = ""
    
    'WRITE OUT THE 10 RECORDS (AS TABLE ROWS)    
    While Not RS.EOF AND intPage2 <= intSPage
         intPage2 = intPage2 + 1
    	
         rOutput = rOutput & "<tr class=""" & rClass & """>" & vbCrlf & _
    	      "  <td colspan=""2""><a href=""detail.asp?vID=" & RS("vID") & """>" & RS("vDescription") & "</a></td>" & vbCrlf & _
    	      "  <td colspan=""2"">" & RS("mName") & "</td>" & vbCrlf & _
    	      "  <td>" & RS("vYear") & "</td>" & vbCrlf & _
    	      "  <td>" & FormatNumber(RS("vMileage"),0) & "</td>" & vbCrlf & _
    	      "  <td>" & FormatCurrency(RS("vPrice"),0) & "</td>" & vbCrlf & _
    	      "  <td colspan=""2"">" & FormatDateTime(RS("vDateAdded"),0) & "</td>" & vbCrlf & _
    	      "  <td colspan=""2"" class=""" & RS("sClass") & """ width=""80px"">" & RS("sStatus") & "</td>" & vbCrlf & _
    	      "</tr>"
    	
         If rClass = "" Then
              rClass = "dark"
         Else
              rClass = ""
         End If
    	
    RS.MoveNext
    Wend
     
    RS.Close
    Set RS = Nothing
     
    Response.Write rOutput
    This will show 10 results per page. If you want to change the number of results per page then alter the values shown in red.
    "If it ain't broken, don't fix it!"
    ----
    Northern Star - Web design, stategy & development.

  6. #6
    SitePoint Evangelist
    Join Date
    Jul 2005
    Posts
    456
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    many thanks.

  7. #7
    SitePoint Wizard
    Join Date
    Nov 2004
    Location
    Nelson BC
    Posts
    2,310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Depends on your database, for example with mySQL you can do:
    SELECT * FROM myTable LIMIT 100,10

    to return the first 10 records starting at record 100

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    LIMIT 100,10 returns 10 rows starting at row 101 (the first parameter is the number of rows to skip over)

    of course, it doesn't make much sense without an ORDER BY

    as for paging, if you are actually retrieving all the rows in the table, but showing only a small portion of them, your app is inefficient and will hardly scale very well

    gBay, if this is for SQL Server, see this article -- Server-side Paging with SQL Server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •