MySQL RecordCount Problems

[SD2001]

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 =10If RSPage =1Then Offset =0If 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)EndIfIfNot CInt(TotalPages)= CInt(RSPage)ThenIf TotalRecords < Max Then
PageSwitch = False
Else
PageSwitch = True
EndIfEndIf

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

[bronko85]

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.