| | |
MySQL RecordCount Problems
Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
![]() |
•
•
Join Date: May 2004
Posts: 1
Reputation:
Solved Threads: 0
Having built a work around for the counting feature not working, in any of the normal ways Ihave the following problem.
This works perfectly:
<%
RSPage = TRIM(Request.QueryString("page"))
If Len(RSPage)=0Then RSPage =1
Max =10
If RSPage =1Then Offset =0
If RSPage > 1Then Offset =(RSPage-1)* Max
Set CConn = Server.CreateObject("ADODB.Connection")
CConn.Open strADO
Set CRs = Server.CreateObject("ADODB.RecordSet")
CRs.Open "SELECT COUNT(*) AS PAGECOUNT FROM tblsearch WHERE Active=-1 AND Reference=" & strRefLink, CConn
TotalRecords = CRs("PAGECOUNT")
If(Round((TotalRecords/Max)))= Round((TotalRecords/Max),2)Then
TotalPages = Round((TotalRecords/Max),2)
Else
TotalPages = Round((TotalRecords/Max)+1)
EndIf
IfNot CInt(TotalPages)= CInt(RSPage)Then
If TotalRecords < Max Then
PageSwitch = False
Else
PageSwitch = True
EndIf
EndIf
CRs.close
CConn.close
%>
I also need it to work with the following SQL but have failed in all attempts so far.
"SELECT *, MATCH (url,description,name) AGAINST ('" & Search & "' IN BOOLEAN MODE) AS score FROM tblsearch WHERE Active=-1 AND MATCH (url,description,name) AGAINST ('" & Search & "' IN BOOLEAN MODE) ORDER BY name ASC LIMIT " & Offset & "," & Max
The MATCH command seems to be causing a problem when combined with the COUNT(*) feature,
The String above works fine on its own, but not with COUNT(*).
Can anyone help? :rolleyes:
Anthony
This works perfectly:
<%
RSPage = TRIM(Request.QueryString("page"))
If Len(RSPage)=0Then RSPage =1
Max =10
If RSPage =1Then Offset =0
If RSPage > 1Then Offset =(RSPage-1)* Max
Set CConn = Server.CreateObject("ADODB.Connection")
CConn.Open strADO
Set CRs = Server.CreateObject("ADODB.RecordSet")
CRs.Open "SELECT COUNT(*) AS PAGECOUNT FROM tblsearch WHERE Active=-1 AND Reference=" & strRefLink, CConn
TotalRecords = CRs("PAGECOUNT")
If(Round((TotalRecords/Max)))= Round((TotalRecords/Max),2)Then
TotalPages = Round((TotalRecords/Max),2)
Else
TotalPages = Round((TotalRecords/Max)+1)
EndIf
IfNot CInt(TotalPages)= CInt(RSPage)Then
If TotalRecords < Max Then
PageSwitch = False
Else
PageSwitch = True
EndIf
EndIf
CRs.close
CConn.close
%>
I also need it to work with the following SQL but have failed in all attempts so far.
"SELECT *, MATCH (url,description,name) AGAINST ('" & Search & "' IN BOOLEAN MODE) AS score FROM tblsearch WHERE Active=-1 AND MATCH (url,description,name) AGAINST ('" & Search & "' IN BOOLEAN MODE) ORDER BY name ASC LIMIT " & Offset & "," & Max
The MATCH command seems to be causing a problem when combined with the COUNT(*) feature,
The String above works fine on its own, but not with COUNT(*).
Can anyone help? :rolleyes:
Anthony
The COUNT(*) method was used only with GROUP BY statement.
So, if you have some colums in SELECT statement YOU MUST insert also a GROUP BY statement in your sql string.
I've solved this problem, deleting all columns in select statement [inserting only count(*)], and manteining the other part the same.
Bye.
PS. I'm italian and i've not a lot of experience with english language.
Studing it its different to write it.
So, if you have some colums in SELECT statement YOU MUST insert also a GROUP BY statement in your sql string.
I've solved this problem, deleting all columns in select statement [inserting only count(*)], and manteining the other part the same.
Bye.
PS. I'm italian and i've not a lot of experience with english language.
Studing it its different to write it.
FILANNINO MICHELE
-----------------------------
Studente presso il Dipartimento di
Informatica e Tecnologie per la produzione del software.
Bari (IT)
-----------------------------
http://www.filanninomichele.com/
info@filanninomichele.com
-----------------------------
Studente presso il Dipartimento di
Informatica e Tecnologie per la produzione del software.
Bari (IT)
-----------------------------
http://www.filanninomichele.com/
info@filanninomichele.com
![]() |
Similar Threads
- MySQL Memory Problems When Sorting Multiple Columns (MySQL)
- Update Mysql Blob Column Error (Visual Basic 4 / 5 / 6)
- Whats Wrongin folloowind code for connecting with mysql (JSP)
- MySQL query's (MySQL)
- mysql php problems (PHP)
- MySQL drivers not loading? (Java)
Other Threads in the MySQL Forum
- Previous Thread: Connections in loops
- Next Thread: MySQ-help...
| Thread Tools | Search this Thread |
agplv3 alfresco amazon api artisticlicense aws bizspark breathalyzer camparingtocolumns changingprices cmg communityjournalism contentmanagement contractors copyright count court crm data database design developer development distinct drupal dui ec2 email enter eudora facebook form foss gartner gnu government greenit groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple music mysql mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset saas select simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization





