Paginação C/ Mysql

cyberalexxx · Janeiro 14, 2005

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???

ursolouco · Janeiro 14, 2005

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.

cyberalexxx · Janeiro 14, 2005

Valeu urso!!

Acho que através das propriedades do recordset não tem jeito mesmo, vou tentar como esse código utilizando LIMIT no SELECT.

T+

Entrar

Paginação C/ Mysql

Pergunta

cyberalexxx

Link para o comentário

Compartilhar em outros sites

2 respostass a esta questão

Posts Recomendados

ursolouco

Link para o comentário

Compartilhar em outros sites

cyberalexxx

Link para o comentário

Compartilhar em outros sites

Participe da discussão

Estatísticas dos Fóruns

Navegação

Atividades