Well here is the offending code. Some of it is quite long especially the zip
locator, so I will attempt to shorten it for the lists sake. Maybe someone
can show me the best ways to handle this.
First the zip locator runs and selects all zip codes that are within the
specified criteria. 0-500 miles of point X. So say someone types in their
zipcode and selects 500 miles, then the radius search returns all zip codes
within 500 miles of that zip. At this point another query is ran and does a
search of Advertisements where the Advertisements zipcode is IN the list of
zipcodes returned by the radius search.
So...
Part of the radius search that is ran first.
<CFQUERY name="results" dbtype="query">
select * from qSquareSearch where dist < <CFQUERYPARAM value="#radius#"
cfsqltype="CF_SQL_INTEGER">
</CFQUERY>
<CFRETURN results>
Query to get advertisements..
SELECT #APPLICATION.DBPRE#Advertisements.AdID,
#APPLICATION.DBPRE#Advertisements.quantity_option,
#APPLICATION.DBPRE#Advertisements.MemberID,
#APPLICATION.DBPRE#MemberProfile.RscreenName,
#APPLICATION.DBPRE#Advertisements.CategoryID,
#APPLICATION.DBPRE#Advertisements.SCategoryID,
#APPLICATION.DBPRE#Advertisements.ItemPhoto,
#APPLICATION.DBPRE#Advertisements.title,
#APPLICATION.DBPRE#Advertisements.Price,
#APPLICATION.DBPRE#Advertisements.AQuantity,
#APPLICATION.DBPRE#Advertisements.State,
#APPLICATION.DBPRE#Advertisements.zipcode,
#APPLICATION.DBPRE#Advertisements.County,
#APPLICATION.DBPRE#Advertisements.City
FROM #APPLICATION.DBPRE#Advertisements
JOIN #APPLICATION.DBPRE#MemberProfile
ON #APPLICATION.DBPRE#Advertisements.MemberID =
#APPLICATION.DBPRE#MemberProfile.MemberID
WHERE #APPLICATION.DBPRE#Advertisements.Status =
<CFQUERYPARAM cfsqltype="CF_SQL_BIT" value="#Val(ARGUMENTS.Status)#">
AND #APPLICATION.DBPRE#Advertisements.AdExpires >=
<CFQUERYPARAM cfsqltype="CF_SQL_CHAR" value="#Trim(ARGUMENTS.AdExpires)#">
This is where the problem is since the radius returns thousands of results
if someone chooses like 500 mile radius
<CFIF IsDefined("form.zip") AND form.zip neq 0 AND
IsDefined("form.radius") AND form.radius neq 0 AND len(trim(form.zip)) eq 5>
AND ( #APPLICATION.DBPRE#Advertisements.zipcode IN
(#ListQualify(ValueList(results.zip),"'")#)
OR(#APPLICATION.DBPRE#Advertisements.city IN
(#listQualify(ValueList(results.city),"'")#)
AND #APPLICATION.DBPRE#Advertisements.State
IN(#listqualify(valuelist(results.State), "'")#)))
</CFIF>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268930
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
