Help with moving a query to a .cfc

earwig75
earwig75 used Ask the Experts™
on
I have a query like the one below. With some simple CF Code that uses the query results. I will include the query and CF code below. I'm having trouble moving this query to my .cfc page - could someone help? I've done this a 100 times before but something with this one is throwing me off. If someone could help convert the query into a function that would work with my code that would be great. Thanks.

<cfquery name="get_count" datasource="#ds#">
			 
SELECT      COUNT(IDNumber) AS records
FROM         mydb
WHERE     (Current IS NULL) AND (Person='#variables.Person#') AND (Started BETWEEN '#variables.begindt#' AND '#variables.enddt# 23:59:59')			 
</query>

Open in new window


<cfset records_per_page = 5>
<cfset total_pages = ceiling(get_count.records / records_per_page)>
<cfif url.page * records_per_page LT get_count.records>
....
<cfelse>
....
</cfif>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
You didn't mention the db type. But if all you need is the count, just return a number not a query object.  The basics are something like this.  

<cfcomponent>
     <!--- either hard code the DSN -or- pass it into an init() method (preferred) --->
     <cfset variables.dsn = "yourDatasourceName">

     <cffunction name="getQuery" returntype="numeric">
               <cfargument name="person" type="string">
               <cfargument name="startDate" type="date">
               <cfargument name="endDate" type="date">

                <cfset var get_count = "">

               <cfquery name="get_count" datasource="#variables.dsn#">
                      SELECT      COUNT(IDNumber) AS records
                      FROM        mydb
                      WHERE     Current IS NULL 
                      AND        Person = <cfqueryparam value="#arguments.Person#" cfsqltype="cf_sql_varchar"> 
                      AND       Started >= <cfqueryparam value="#arguments.startDate#" cfsqltype="cf_sql_date">
                     <!--- simpler alternative to using 11:59:59  --->
                     AND       Started < <cfqueryparam value="#dateAdd('d', 1, arguments.endDate)#" cfsqltype="cf_sql_date">
                </cfquery>

                <!--- return the number of records, not a query --->
                <cfreturn val(get_count.records)>

           </cffunction>
</cfcomponent>

Open in new window

Author

Commented:
That worked. Thank you.

Can you tell me how to get the recordcount for that query? It always returns "1" for me even when there should be more.

Thank you.
Most Valuable Expert 2015

Commented:
It always returns "1" for me even when there should be more.

That's what should happen. Since you're doing a COUNT(..) the query will only return a single record with the total count of IDNumber's.  So use that number, not the query record count.

Author

Commented:
So use that number, not the query record count.
So, can I get the recordcount from this query? or would I need to return something different? I'm sorry... I am confused and not very good with sql yet.
Most Valuable Expert 2015

Commented:
So, can I get the recordcount from this query?

Yes, but it doesn't make much sense when using COUNT because it's only supposed to return a single value.  In your case the total count of id numbers. Run the query on a separate page and dump it.  You'll see it contains only one record:

             RowNumber | record
              ------------------------------------
              1                  |  24

If you want the total idnumbers, you need to use  

              #queryName.record#     (ie returns 24)

.. which is what the function returns. Not the recordCount

             #queryName.recordCount#      (ie always returns 1)
Most Valuable Expert 2015

Commented:
If you're not familiar with COUNT, this article might help SQL COUNT() function

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial