Sign in to follow this  
cyberalexxx

Paginação C/ Mysql

Recommended Posts

Tenho um script que faz paginação funcionando beleza com banco Access, ai o desinfeliz do cliente pediu um Banco mais robusto, migrei para o MySQL, to apanhando feito um condenado para adaptar o script.

alguém já fez paginação com BD MySQL usando as propriedades do Recordset???

Share this post


Link to post
Share on other sites

Cyber!!!

Em uma busca pela web, localizei o seguinte código:

<%
'# Written by snowboardr on www.pscode.com
'# If you like this code, please comment on planet source code!
'# And vote if its worthy enough?
'# Who doesn't want those awesome .net charts?

'# Installing MySQL :: tutorial available at PSC:
'# http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=7739&lngWId=4
'#

Dim sDatabaseConnection
sDatabaseConnection = "DRIVER={MySQL ODBC 3.51 Driver};"_
      	& "SERVER=localhost;"_
      	& "DATABASE=test;"_
      	& "UID=root;PWD=; OPTION=35;"

%>
<html>
<head>
<title>Paging</title>
<style type="text/css">
<!--
.sgrey {


	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
	font-weight: bold;
	color: #999999;
	text-decoration: none;
}
.elinks {
	color: #003399;
	text-decoration: none;
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
	font-weight: bold;
}
-->
</style>
</head>
<body>
<table width="100%" border="0" align="center" cellpadding="2" cellspacing="1">
  <tr bgcolor="#EEEEEE">
    <td colspan="4"><table width="100%"  border="0" cellspacing="0" cellpadding="0">
        <tr>
          <td><div align="left"><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif" class="bluet"><strong>New Projects</strong></font></div>
              <div align="center"></div></td>
        </tr>
      </table>
        <div align="center"></div>
        <div align="left"></div>
        <div align="center"> </div></td>
  </tr>
  <tr bgcolor="#CCCCCC" class="greyt">
    <td colspan="4"><img width="0" height="0"></td>
  </tr>
  <tr bgcolor="#EEEEEE" class="greyt">
    <td colspan="4"><font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif">Project Subject
    </font>      <div align="center"></div></td>
  </tr>
  <%
'######################################################################################
'# COUNT RECORDS FIRST
'######################################################################################
    Dim sQueryStatus
    
    Dim intRecordsPerPage
    Dim intPages
    Dim sLimitPart 'LIMIT 0,1  -----> LIMIT {START_NUMBER,RECORDS_PERPAGE}
    Dim sStartQuery
    Dim sMaxStart
    Dim sProjectPrev
    Dim sProjectNext
    intRecordsPerPage = 10  'How many records to show per page

  	Dim sqlProjectCount
  	Dim connCount, rsCount
  	Dim sProjectCount
  	sqlProjectCount = "SELECT COUNT(pid) FROM projects"
  	'Response.Write(sqlProjectCount)
  	Set connCount = Server.Createobject("ADODB.Connection")
  	Set rsCount = Server.CreateObject("ADODB.Recordset")
  	connCount.open sDatabaseConnection
  	Set rsCount = connCount.Execute(sqlProjectCount) 
  	If rsCount.eof then
    sProjectCount = 0
    	Else
    sProjectCount = rsCount.Fields(0)  'number of records
  	End If
  	connCount.close
  	set connCount=nothing
  	set rsCount=nothing
  	'Response.Write(sProjectCount)
  	
'######################################################################################
'# SIMPLE PAGING
'######################################################################################
  	sStartQuery = Request.QueryString("start")
  	
  	'Get total pages
  	If intRecordsPerPage < sProjectCount then
      intPages = sProjectCount / intRecordsPerPage
  	End If
  	
  	'here we modify the number if it has a decimal, a better solution maybe would be to use formatnumber
  	Dim instrIntPages
  	instrIntPages = Instr(intPages,".")
  	
  	'If after the decimal there is a 0 then we need to add a page
  	If instrIntPages > 0 then
    intPages = Left(intPages,instrIntPages) + 1
  	End If

  	'Lets create the limit for the sql
  	'LIMIT 0,1  -----> LIMIT {START_NUMBER,RECORDS_PERPAGE}
  	If sStartQuery <> "" AND isNumeric(sStartQuery) then
    	sLimitPart = "LIMIT " & sStartQuery & "," & intRecordsPerPage
      Else
    	sLimitPart = "LIMIT " & "0," & intRecordsPerPage
  	End If
  	
  	'Lets figure out what the max start number is
  	sMaxStart = (intPages*intRecordsPerPage)-intRecordsPerPage
  	
  	If sStartQuery <> "" AND isNumeric(sStartQuery) then
    	sStartQuery = CINT(sStartQuery)
      Else
    	sStartQuery = 0
  	End If


  	'Now lets create our previous / next buttons and disable them if they are not needed.
  	If sMaxStart < 0 then sMaxStart = 0

  	If sStartQuery = "" or sStartQuery = "0" then 'PREVIOUS DISABLED
    sProjectPrev = "<a span class='sgrey'>Previous</span>"
    	ElseIf sStartQuery > 0 then 'PREVIOUS ENABLED
      sProjectPrev = "<a class='elinks' href='?start=" & sStartQuery - intRecordsPerPage & "'>Previous</span>"
  	End If

  	'Next
  	If sMaxStart = sStartQuery then 'NEXT DISABLED
    sProjectNext = "<a span class='sgrey'>Next</span>"
    	ElseIf sStartQuery  < sMaxStart then 'NEXT ENABLED
      sProjectNext= "<a class='elinks' href='?start=" & sStartQuery + intRecordsPerPage & "'>Next</span>"
  	End If
  	
  	
  	'Now its time to select our projects
  	Dim connP, rsP
  	Dim sqlProjects
  	
  	Dim sPid
  	Dim sSubject

  	sqlProjects = "SELECT "_
  	& "pid,"_ 
  	& "psubject "_
  	& " FROM projects ORDER by pid ASC " & sLimitPart

  	
  	'Response.Write(sqlProjects)
  	Set connP = Server.Createobject("ADODB.Connection")
  	Set rsP = Server.CreateObject("ADODB.Recordset") 
  	connP.open sDatabaseConnection
  	Set rsP = connP.Execute(sqlProjects) 
    If NOT rsP.eof then
    Do while not rsP.eof
    sPid = rsP("pid")
    sSubject = rsP("psubject")
    %>
  <tr>
    <td colspan="4" bgcolor="#FFFFFF"><font color="#003366" size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=sSubject%></font></td>
  </tr>
  <%
    rsP.movenext
    loop
    %>
  <tr>
    <td colspan="4" bgcolor="#FFFFFF" class="sgrey"><div align="right">
        <table width="100%"  border="0" cellspacing="0" cellpadding="0">
          <tr bgcolor="#EEEEEE">
            <td width="29%" height="23">
              <div align="left" class="greyt">Total: <%=sProjectCount%></div></td>
            <td width="71%"><div align="center">
                <%  Dim queryPageOn
    	queryPageOn = Request.QueryString("page")
    	If NOT isNumeric(queryPageOn) AND NOT sStartQuery = "0" then
      queryPageOn = "1" 
    	End If
      queryPageOn = CINT(queryPageOn)
    	Response.Write(sProjectPrev & "&nbsp;&nbsp;")
    	'# Create Page Numbers & Links
       Dim iPages, iPagesTemp
       For iPages=1 to intPages
       iPagesTemp = iPages-1  	
        If NOT queryPageOn=iPages then	
      	Response.write "<a href='?start=" & iPagesTemp*intRecordsPerPage & "&page="& iPages & "' class='elinks'>" & iPages & "</a>&nbsp; "
          Else
      	Response.write "<span class='greyt'>[</span><a href='?start=" & iPagesTemp*intRecordsPerPage & "&page="& iPages & "' class='elinks'>" & iPages & "</a><span class='greyt'>]</span>&nbsp; "
      End If
       Next
       Response.Write("&nbsp;&nbsp;"&sProjectNext)
       %>
            </div></td>
          </tr>
        </table>
    </div></td>
  </tr>
  <%Else%>
  <tr>
    <td bgcolor="#FFFFFF" class="sred"> <font color="#FF0000" face="Verdana, Arial, Helvetica, sans-serif">No projects under this category at this time. </font></td>
    <td width="22%" bgcolor="#FFFFFF"><div align="center"></div></td>
    <td bgcolor="#FFFFFF">&nbsp;</td>
  </tr>
  <%End IF
    connP.close
    set connP=nothing
    set rsP=nothing 
 '######################################################################################### 
%>
</table>
</body>
</html>

Veja se pode lhe ajudar....

Todos os créditos são de seu devido autor.

Share this post


Link to post
Share on other sites

Syntax

The syntax for the SELECT LIMIT statement in SQL is:

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];


Example - Using LIMIT keyword
Let's look at how to use a SELECT statement with a LIMIT clause in SQL.

For example:

SELECT contact_id, last_name, first_name
FROM contacts
WHERE website = 'TechOnTheNet.com'
ORDER BY contact_id DESC
LIMIT 5;

 

 

 

 

Rent a car in Dubai

https://RentalCarsUAE.com
Edited by rentalcarsuae

Share this post


Link to post
Share on other sites

This blog is really helpful to deliver updated educational affairs over internet which is really appraisable. I found one successful example of this truth through this blog. I am going to use such information now.
 

Visit Us :- sell car online
 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this