SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pagination to display results of MySQL

    Hi all.

    I need your help.

    Use this pagination to display results of MySQL, but the link
    Code:
    <a href="?page=<%=currentPage+1%>">Next</a>
    is always active even when there are no results to display:


    Code:
    <%
       pageSize = 25
       
       if(len(Request.QueryString("page"))=0)then
          currentPage = 1
       else
          currentPage = CInt(Request.QueryString("page"))
       end if
    
       If Not IsNumeric (currentPage) Then currentpage = 1
       If currentPage < 1 Then currentPage = 1
    
       start = ((currentPage - 1) * pageSize)
       If start < 0 Then start = 0
    %>
    
    <% SQL = "SELECT * FROM _myTbl LIMIT " & start & ", " & pageSize %>
    <% Set RS = Server.CreateObject("ADODB.Recordset") %>
    <% RS.Open SQL, cn %>
    
    <% if Not RS.eof then %>
    <% while not RS.eof %>
    
    <!-- my resultset -->
    
    <% RS.movenext() %>
    <% wend %>
    <% end if %>
    <% RS.Close() : Set RS = Nothing %>
    
    <% if currentPage > 1 then %>
    <a href="?page=<%=currentPage-1%>">Prev</a> 
    <% else %>
    Prev<% end if %>
    | 
    <% if cInt(start) <> cInt(pageSize) then %>
    <a href="?page=<%=currentPage+1%>">Next</a>
    <% else %>
    Next
    <% end if %>
    Hope that helps.
    ts

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <% if cInt(start) <> cInt(pageSize) then %>
    That check will almost always evaluate as true. What you need to get is the total record count that the query can return (without LIMIT) then ...
    Code:
    <% if cInt(start+pageSize) < cInt(recordCount) then %>
    Hence, if start+pageSize >= recordCount then NEXT is not shown.

    HTH
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    Code:
    <&#37; if cInt(start) <> cInt(pageSize) then %>
    That check will almost always evaluate as true. What you need to get is the total record count that the query can return (without LIMIT) then ...
    Code:
    <% if cInt(start+pageSize) < cInt(recordCount) then %>
    Hence, if start+pageSize >= recordCount then NEXT is not shown.

    HTH
    OK, I understand... but I need cInt(recordCount) ... or not?

  4. #4
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many thanks x your reply.
    I have my new pagination to display results of MySQL.


    Code:
    <&#37;
       pageSize = 5
       
       if(len(Request.QueryString("page"))=0)then
          currentPage = 1
       else
          currentPage = CInt(Request.QueryString("page"))
       end if
    
       If Not IsNumeric (currentPage) Then currentpage = 1
       If currentPage < 1 Then currentPage = 1
    
       start = ((currentPage - 1) * pageSize)
       If start < 0 Then start = 0
    %>
    
    <% SQL = "SELECT SQL_CALC_FOUND_ROWS * FROM " %>
    <% SQL = SQL & " _myTbl  LIMIT " & start & ", " & pageSize %>
    <% Set RS = Server.CreateObject("ADODB.Recordset") %>
    <% RS.Open SQL, objConn %>
    
    <% if Not RS.eof then %>
    
    <% SQL1 = "SELECT FOUND_ROWS() AS rC;" %>
    <% Set RS1 = objConn.Execute(SQL1) %>
    <% recordCount = RS1("rC") %>
    <% Set RS1 = Nothing %>
    
    <% while not RS.eof %>
    <!-- start my resultset -->
    <% response.write RS("TITLE") & "<br>" %>
    <!-- end my resultset -->
    <% RS.movenext() %>
    <% wend %>
    
    <% end if %>
    
    <% RS.Close() : Set RS = Nothing %>
    
    <% if currentPage > 1 then %>
    <a href="?page=<%=currentPage-1%>">Prev</a> 
    <% else %>
    Prev<% end if %>
    | 
    <% if cInt(start+pageSize) < cInt(recordCount) then %>
    <a href="?page=<%=currentPage+1%>">Next</a>
    <% else %>
    Next
    <% end if %>

  5. #5
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want this in my pagination asp and mysql (same to this site): ?

    Code:
    Page 1 of 221	1	2	3	4	5	11	51	101	>	Last 

  6. #6
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Sorry but your post makes no sense at all. Are you making a statement? Are you asking a question? What are you actually saying/asking?
    Ian Anderson
    www.siteguru.co.uk

  7. #7
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry... I asking a question.

    If you want in my pagination this:

    Code:
    Page 1 of 221 1 2 3 4 5 11 51 101 > Last &#187;
    How do I change the code ASP ?

  8. #8
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    numpages = Fix (recordCount / pageSize) + 1 'Gets the total number of pages - e.g. 221 in your example

    I'll let you work out the rest - it's just mathematics, no ASP magic.
    Ian Anderson
    www.siteguru.co.uk

  9. #9
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru View Post
    numpages = Fix (recordCount / pageSize) + 1 'Gets the total number of pages - e.g. 221 in your example

    I'll let you work out the rest - it's just mathematics, no ASP magic.
    Yes... it's just mathematics, no ASP magic

    Code:
    <!-- #include virtual="/include/conn_mysql.asp" -->
    
    <html>
    
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>Pagination</title>
    </head>
    
    <body>
    
    <%
    
       Function excess(argValue)
    	   if not (int(argValue) = argValue) then argValue = int(argValue)+1		
    	   excess = argValue
       end Function
    
       pageSize = 10
       
       if(len(Request.QueryString("page"))=0)then
          currentPage = 1
       else
          currentPage = CInt(Request.QueryString("page"))
       end if
    
       If Not IsNumeric (currentPage) Then currentpage = 1
       If currentPage < 1 Then currentPage = 1
    
       start = ((currentPage - 1) * pageSize)
       If start < 0 Then start = 0
    %>
    
    <% SQL = " SELECT SQL_CALC_FOUND_ROWS * FROM _myTbl " %>
    <% SQL = SQL & " ORDER BY ID DESC " %>
    <% SQL = SQL & " LIMIT " & start & ", " & pageSize %>
    <% Set RS = Server.CreateObject("ADODB.Recordset") %>
    
    <% response.write SQL & "<br><br>" %>
    <% RS.Open SQL, objConn %>
    
    <% if Not RS.eof then %>
    
    <% SQL1 = "SELECT FOUND_ROWS() AS rC;" %>
    <% Set RS1 = objConn.Execute(SQL1) %>
    <% recordCount = RS1("rC") %>
    <% pageCount = excess(clng(recordCount) / clng(pageSize)) %>
    <% Set RS1 = Nothing %>
    
    <% response.write Recordcount & "<br><br>" %>
    
    <% while not RS.eof %>
    
    <!-- start my resultset -->
    <% response.write RS("TITLEl") & "<br>" %>
    <!-- end my resultset -->
    
    <% RS.movenext() %>
    <% wend %>
    
    <% end if %>
    <% RS.Close() : Set RS = Nothing %>
    
    <% iPageCount = PageCount %>
    <% iPageCurrent = currentPage %>
    
    <br>
    Page <b><%=currentPage%></b> of <b><%=cInt(pageCount)%></b>
    <br>
    
    <% if iPageCurrent-2 > 0 and iPageCurrent > 2 then%>
    <a href="?page=1"> First</a>&nbsp;
    <%end if%>
    
    <% if currentPage > 1 then %>
    <a href="?page=<%=currentPage-1%>">Prev</a>&nbsp; 
    <% else %>
    Prev<% end if %>
    							<%if iPageCount > 2 then
    								if iPageCurrent-2 < 1 then da_pag = 1 else da_pag = iPageCurrent-2
    								if iPageCurrent+2 > iPageCount then fino_a_pag = iPageCount else fino_a_pag = iPageCurrent+2
    							else
    								da_pag = 1
    								fino_a_pag = iPageCount
    							end if%>
    							<%for i = da_pag to fino_a_pag%>
    								<%if i = iPageCurrent then%>
    								    &nbsp;[<%=i%>]&nbsp;
    								<%else%>	
    									&nbsp;<a href="?page=<%=i%>">[<%=i%>]</a>&nbsp;
    								<%end if%>	
    							<%next%>
    
    <% if cInt(start+pageSize) < cInt(recordCount) then %>
    <a href="?page=<%=currentPage+1%>">Next</a>&nbsp;
    <% else %>
    Next
    <% end if %>
    
    <%if iPageCurrent+1 < iPageCount then%>
    <a href="?page=<%=iPageCount%>">Last </a>&nbsp;
    <%end if%>
    
    </body>
    
    </html>
    
    <!-- #include virtual="/include/close_conn_mysql.asp" -->


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
  •