asked on

Help with moving a query to a .cfc

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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

earwig75

ASKER

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.

_agx_

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.

earwig75

ASKER

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.

_agx_

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)

_agx_

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