SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Feb 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Error executing database query - filters

    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:

    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?
    Find cocktails at Dr. Mixology, the new mixology community

  2. #2
    SitePoint Member
    Join Date
    Feb 2008
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it... forgot the INNER JOINs
    Find cocktails at Dr. Mixology, the new mixology community


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •