Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    4

    Unanswered: MySQL RecordCount Problems

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    4
    Many thanks r937, worked first time.

    Anthony

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •