MySQL RecordCount Problems

sd2001 · #1 (**permalink**) 05-02-04, 06:47

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?

Anthony

r937 · #2 (**permalink**) 05-02-04, 07:25

Quote:

Originally Posted by sd2001

The String above works fine on its own, but not with COUNT(*).

probably because you had other stuff in the SELECT besides count(*)

also, no need to use ORDER BY or LIMIT, as there will be only 1 row with 1 count in it

Code:

select count(*)
  from tblsearch 
 where active=-1 
   and match (url,description,name) 
       against ('" & search & "' in boolean mode)

sd2001 · #3 (**permalink**) 05-02-04, 07:32

Many thanks r937, worked first time.

Anthony

Internet Services

Web Development

Digital Marketing

Consumer Tech