SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: SQL Question?
-
Nov 22, 2001, 01:01 #1
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
-
Nov 22, 2001, 01:51 #2
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
That's it. I hope I have been easy to understand.
Husain.
-
Nov 22, 2001, 01:56 #3
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.
-
Nov 23, 2001, 11:01 #4
- 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
-
Nov 23, 2001, 11:11 #5
- 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 - ]
-
Nov 23, 2001, 13:53 #6
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.
-
Nov 27, 2001, 17:57 #7
- 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=882SiteTell.com: Get the best viral marketing tool on the planet and watch as the number of unique visitors to your site soars!
-
Nov 27, 2001, 19:17 #8
- 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
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
-
Dec 3, 2001, 10:17 #9
- 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
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Dec 3, 2001, 16:00 #10
- 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.
-
Dec 3, 2001, 16:14 #11
- 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.
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?
Code:if @sortby = "somecol" SELECT ... ORDER BY somecol else if @sortby = "someothercol" SELECT ... ORDER BY someothercol else SELECT ... ORDER BY defaultcol
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.Guess I was wrong!
Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Dec 3, 2001, 17:01 #12
- 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.
-
Dec 4, 2001, 08:49 #13
- 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 ...
Bookmarks