I've run into this one before and it sucks. Your best bet, if you want the
number of unique property ids is to run another query that does a
SELECT COUNT DISTINCT propertyID AS pCount...
But whether or not it will work to use this number as the recordcount is
anyone's guess. You may have to keep track manually.
+-----------------------------------------------+
Bryan Love
Database Analyst
Macromedia Certified Professional
Internet Application Developer
TeleCommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-----Original Message-----
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 11:22 AM
To: CF-Talk
Subject: Pagination (Next n records) for an inner join and grouped
output...
I've got an inner join that I'm trying to make work with paging the records
and I *think* I see what the problem is, but I don't know how to proceed...
The inner joins are working correctly. In fact, when I go to output, I
simply group them by property ID, and I get the right number of Properties,
and the right rates for each property...but the pagination is ALL wonky. I'm
using Dreamweaver's Next N results, which is fairly easy to understand, and
normally works great...
Here's my query:
<cfif IsDefined("URL.Bedrooms")>
<cfquery name="getMyListings" datasource="#Application.DSN#">
SELECT *
FROM (tblRentalLocations INNER JOIN tblRentalProperties ON
tblRentalLocations.LocationID=tblRentalProperties.frn_LocationID)
INNER JOIN tblRentalRates ON
tblRentalProperties.PropertyID=tblRentalRates.frn_PropertyID
<cfif URL.Bedrooms NEQ 'ANY'>
WHERE tblRentalProperties.PropertyBedrooms = #URL.Bedrooms#
</cfif>
ORDER BY tblRentalProperties.PropertyName,
tblRentalRates.RatesOrder,
tblRentalRates.RatesLength Desc
</cfquery>
</cfif>
My Next N code looks like this:
<cfset MaxRows_getMyListings=10>
<cfset
StartRow_getMyListings=Min((PageNum_getMyListings-1)*MaxRows_getMyListings+1
,Max(getMyListings.RecordCount,1))>
<cfset
EndRow_getMyListings=Min(StartRow_getMyListings+MaxRows_getMyListings-1,getM
yListings.RecordCount)>
<cfset
TotalPages_getMyListings=Ceiling(getMyListings.RecordCount/MaxRows_getMyList
ings)>
<cfset QueryString_getMyListings=Iif(CGI.QUERY_STRING NEQ
"",DE("&"&CGI.QUERY_STRING),DE(""))>
<cfset
tempPos=ListContainsNoCase(QueryString_getMyListings,"PageNum_getMyListings=
","&")>
<cfif tempPos NEQ 0>
<cfset
QueryString_getMyListings=ListDeleteAt(QueryString_getMyListings,tempPos,"&"
)>
</cfif>
Now, here's where it gets wonky. On the query, the actual recordcount is the
total number of rows returned, when I use the grouped output and nest my
cfoutput tags, it works perfectly, but it gives me a wrong "recordCount" and
that, in turn, throws the next n browsing all to heck and back. I THINK I
should somehow be totalling the number of unique PropertyIDs there are in
the query, and use that instead of "recordCount"...am I on the right track?
I tried COUNT(PropertyID) AS PropertyCount but that didn't work, it won't
let you use that AND a * in the same query, and when I went to all the
trouble to place all my fieldnames in there, it didn't work either...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
