Hi. I have a gallery that shows 9 records at a time, which
you can then move on to subsequent pages. I added filters to narrow
down the record search, however, I am receiving query errors. This
is my code:
<!----- This is setting the initial defaults for the filter
variables ---->
<cfparam name="form.ART_CATEGORY_ID" default="">
<cfparam name="form.ART_TYPE_ID" default="">
<cfparam name="form.FINE_ART_CLASS_ID" default="">
<cfparam name="form.searchfield" default="">
<!----- Populate the CATEGORIES filter drop-down ---->
<cfquery name="categories" datasource="nancy">
SELECT *
FROM CATEGORIES
ORDER BY ART_CATEGORY_ID
</cfquery>
<!----- Populate the TYPES filter drop-down ---->
<cfquery name="types" datasource="nancy">
SELECT *
FROM TYPE
ORDER BY ART_TYPE_ID
</cfquery>
<!----- Populate the CLASS filter drop-down ---->
<cfquery name="class" datasource="nancy">
SELECT *
FROM CLASS
ORDER BY FINE_ART_CLASS_ID
</cfquery>
<!----- Create the filter drop-down selections ---->
<form method="post" action="root_fineart.cfm">
<select name="ART_TYPE_ID">
<option value="" <cfif trim(form.ART_TYPE_ID) eq
"">selected
</cfif>> All </option>
<cfoutput query="types">
<option value="#ART_TYPE_ID#"
<cfif form.ART_TYPE_ID eq
types.ART_TYPE_ID>selected</cfif>>#ART_TYPE#</option>
</cfoutput>
</select>
<select name="ART_CATEGORY_ID">
<option value="" <cfif trim(form.ART_CATEGORY_ID) eq
"">selected
</cfif>> All </option>
<cfoutput query="categories">
<option value="#ART_CATEGORY_ID#"
<cfif form.ART_CATEGORY_ID eq
categories.ART_CATEGORY_ID>selected</cfif>>#ART_CATEGORY#</option>
</cfoutput>
</select>
<select name="FINE_ART_CLASS_ID">
<option value="" <cfif trim(form.FINE_ART_CLASS_ID) eq
"">selected
</cfif>> All </option>
<cfoutput query="class">
<option value="#FINE_ART_CLASS_ID#"
<cfif form.FINE_ART_CLASS_ID eq
class.FINE_ART_CLASS_ID>selected</cfif>>#FINE_ART_CLASS#</option>
</cfoutput>
</select>
<!----- This also creates a SEARCH capability ---->
<input name="searchfield" type="text" size="10"
value="#form.searchfield#" /></cfoutput> </label>
<input name="submit" type="submit" value="Submit" />
<!----- I got this part of the code from a site, that allows
me to display the 9 records at a time in my gallery ---->
<cfparam name="start" type="numeric" default="1">
<cfparam name="step" type="numeric" default="9">
<!----- My query to retrieve the records... This may be where
I have something wrong... ---->
<cfquery datasource="nancy" cachedwithin=".01"
name="queryResults">
SELECT *
FROM ART
WHERE 1 = 1
<cfif val(form.ART_CATEGORY_ID) GT 0>
AND ART_CATEGORY_ID = <cfqueryparam
value="#form.ART_CATEGORY_ID#" cfsqltype="cf_sql_integer">
</cfif>
<cfif val(form.ART_TYPE_ID) GT 0>
AND r.ART_TYPE_ID = <cfqueryparam
value="#form.ART_TYPE_ID#" cfsqltype="cf_sql_integer">
</cfif>
<cfif val(form.FINE_ART_CLASS_ID) GT 0>
AND i.FINE_ART_CLASS_ID = <cfqueryparam
value="#form.FINE_ART_CLASS_ID#" cfsqltype="cf_sql_integer">
</cfif>
<cfif trim(form.searchfield) neq "">
AND ART_TITLE LIKE '%#form.searchfield#%'
</cfif>
ORDER BY ART_TITLE ASC
</cfquery>
<!----- This is also code I got somewhere else to display the
gallery ---->
<cfif queryResults.recordcount gt 0>
<cfoutput>
<p class="style1">
<!--- if past start --->
<cfif (start-step-step) gt 1>
<a href="#cgi.SCRIPT_NAME#?start=1"><img
src="images/Beginning_blue.png" alt="Beginning" width="31"
height="21" align="absbottom" /></a>
</cfif>
<cfif start gt 1>
<a href="#cgi.SCRIPT_NAME#?start=#start-step#"><img
src="images/previous_blue.png" alt="Previous" align="absbottom"
/></a>
</cfif>
<strong>#start# - #iif(start * 3 + step gt
queryResults.recordcount,queryResults.recordcount,start + step-1)#
of #queryResults.recordcount# records</strong>
<!--- if still some not displayed --->
<cfif (start + step) lte queryResults.recordcount>
<a href="#cgi.SCRIPT_NAME#?start=#start+step#"><img
src="images/next_blue.png" alt="Next" align="absbottom"
/></a>
</cfif>
<cfif (start+step+step) lte queryResults.recordcount>
<a
href="#cgi.SCRIPT_NAME#?start=#queryResults.recordcount-step+1#"><img
src="images/end_blue.png" alt="End" align="absbottom"
/></a>
</cfif>
</p>
</cfoutput>
</cfif>
<cfloop query="queryResults" startrow="#start#"
endrow="#start + step/3-1#">
<table width="100%" border="1" bordercolor="#FFFFFF"
cellspacing="0" cellpadding="2">
<tr> <cfoutput query="queryResults"
startrow="#start#" maxrows="3">
<td width="33%"
class="style1">#queryResults.ART_TITLE#</td>
</cfoutput>
<cfset #start# = #start# + 3>
</tr>
</table>
</cfloop>
</td>
</tr>
</table></form>
It seems that the CATEGORIES filter is the only one that does
not return a ERROR EXECUTING DATABASE QUERY error. However, it does
not filter all the records... it only seems to filter and return
the first 4 or 5 records.
Any thoughts?
1
Reply
AdChoices