Link to home
Start Free TrialLog in
Avatar of Dusty
DustyFlag for United States of America

asked on

CFML - Weird Issue with a search form

I have a results page with a search form on it, when I submit the form the first time it shows 0 records returned, when I submit it again it returns all my records. Can someone have a look at my code and tell me where the problem lies?  also the url is: http://www.warner-robins-real-estate.com/results-idx.cfm if you need to see the problem in action.
As always any help is appreciated! 
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>


<cfparam name="form.FullBaths" default="">
<cfparam name="form.Bedrooms" default="">
<cfparam name="form.City" default="">
<cfparam name="form.ApproxAge" default="">
<cfparam name="form.ApproxAcreage" default="">
<cfparam name="form.Subdivision" default="">


<cfif IsDefined("url.minimumPrice")>
  <cfset form.minimumPrice = url.minimumPrice>
</cfif>
<cfif IsDefined("url.maximumPrice")>
  <cfset form.maximumPrice = url.maximumPrice>
</cfif>
<cfif IsDefined("url.Bedrooms")>
  <cfset form.Bedrooms = url.Bedrooms>
</cfif>
<cfif IsDefined("url.FullBaths")>
  <cfset form.FullBaths = url.FullBaths>
</cfif>
<cfif IsDefined("url.ApproxAge")>
  <cfset form.ApproxAge = url.ApproxAge>
</cfif>
<cfif IsDefined("url.ApproxAcreage")>
  <cfset form.ApproxAcreage = url.ApproxAcreage>
</cfif>
<cfif IsDefined("url.City")>
  <cfset form.City = url.City>
</cfif>
<cfif IsDefined("url.ListID")>
  <cfset form.ListID = url.ListID>
</cfif>
<!---form prefill minimumPrice--->
<cfif isDefined("Form.minimumPrice")>
  <cfset CLIENT.minimumPrice = Form.minimumPrice>
  <cfset prefillminimumprice = Form.minimumPrice>
  <cfelseif isDefined("CLIENT.lastminimumPrice")>
  <cfset prefillminimumPrice = CLIENT.lastminimumPrice>
  <cfelse>
  <cfset prefillminimumPrice = " ">
</cfif>
<!---form prefill maximumPrice--->
<cfif isDefined("Form.maximumPrice")>
  <cfset CLIENT.maximumPrice = Form.maximumPrice>
  <cfset prefillmaximumprice = Form.maximumPrice>
  <cfelseif isDefined("CLIENT.lastmaximumPrice")>
  <cfset prefillmaximumPrice = CLIENT.lastmaximumPrice>
  <cfelse>
  <cfset prefillmaximumPrice = " ">
</cfif>
<!---form prefill Bedrooms--->
<cfif isDefined("Form.Bedrooms")>
  <cfset CLIENT.Bedrooms = Form.Bedrooms>
  <cfset prefillBedrooms = Form.Bedrooms>
  <cfelseif isDefined("CLIENT.lastBedrooms")>
  <cfset prefillBedrooms = CLIENT.lastBedrooms>
  <cfelse>
  <cfset prefillBedrooms = " ">
</cfif>
<!---form prefill Baths--->
<cfif isDefined("Form.FullBaths")>
  <cfset CLIENT.FullBaths = Form.FullBaths>
  <cfset prefillFullBaths = Form.FullBaths>
  <cfelseif isDefined("CLIENT.lastFullBaths")>
  <cfset prefillFullBaths = CLIENT.lastFullBaths>
  <cfelse>
  <cfset prefillFullBaths = "">
</cfif>
<!---form prefill ApproxAge--->
<cfif isDefined("Form.ApproxAge")>
  <cfset CLIENT.ApproxAge = Form.ApproxAge>
  <cfset prefillApproxAge = Form.ApproxAge>
  <cfelseif isDefined("CLIENT.lastApproxAge")>
  <cfset prefillApproxAge = CLIENT.lastApproxAge>
  <cfelse>
  <cfset prefillApproxAge = "">
</cfif>
<!---form prefill ApproxAcres--->
<cfif isDefined("Form.ApproxAcreage")>
  <cfset CLIENT.ApproxAcreage = Form.ApproxAcreage>
  <cfset prefillApproxAcreage = Form.ApproxAcreage>
  <cfelseif isDefined("CLIENT.lastApproxAcreage")>
  <cfset prefillApproxAcreage = CLIENT.lastApproxAcreage>
  <cfelse>
  <cfset prefillApproxAcreage = "">
</cfif>
<!---form prefill City--->
<cfif isDefined("Form.City")>
  <cfset CLIENT.City = Form.City>
  <cfset prefillCity = Form.City>
  <cfelseif isDefined("CLIENT.lastCity")>
  <cfset prefillCity = CLIENT.lastCity>
  <cfelse>
  <cfset prefillCity = "">
</cfif>
<!---form prefill MLSNUMBER--->
<cfif isDefined("Form.ListID")>
  <cfset CLIENT.ListID = Form.ListID>
  <cfset prefillListID = Form.ListID>
  <cfelseif isDefined("CLIENT.lastListID")>
  <cfset prefillListID = CLIENT.lastListID>
  <cfelse>
  <cfset prefillListID = "">
</cfif>
<cfquery name="getCity" datasource="CGMLS">
SELECT DISTINCT City FROM cgmls.listings 
ORDER BY City ASC
</cfquery>
<cfquery name="wrmls1" datasource="CGMLS">
SELECT DISTINCT Type FROM cgmls.listings 
ORDER BY Type ASC
</cfquery>
<cfquery name="getSub" datasource="CGMLS">
SELECT DISTINCT Subdivision FROM cgmls.listings 
ORDER BY Subdivision ASC
</cfquery>

<!---Query the database--->
<cfquery name = "idx" datasource="CGMLS">
SELECT * FROM listings,seo WHERE Listings.ListID = Seo.ListID





<!---Search by City--->
<cfif form.City IS NOT "">
 AND City LIKE '#form.City#'
</cfif>
<!---Search by Age--->
<cfif form.ApproxAge IS NOT " ">
 AND ApproxAge BETWEEN #form.ApproxAge#
</cfif>
<!---Search by Acreage--->
<cfif form.ApproxAcreage IS NOT " ">
 AND ApproxAcreage BETWEEN #form.ApproxAcreage#
</cfif>
<!---Search by MLSNUMBER
<cfif form.ListID IS NOT " ">
 AND ListID LIKE '#form.ListID#'
 </cfif>
--->
<cfif isdefined("form.minimumprice") and isdefined("form.maximumprice")>
 AND Price BETWEEN '#form.minimumprice#' AND '#form.maximumprice#'
</cfif>
AND Bedrooms >= '#form.Bedrooms#'
AND FullBaths >= '#form.FullBaths#'
ORDER BY Price ASC
</cfquery>


<cfparam name="url.StartRow" default="1">
<cfset rowsPerPage = 7>
<cfset totalRows = idx.recordCount>
<cfset endRow = min(url.startRow + rowsPerPage - 1, totalRows)>
<cfset startRowNext = endRow + 1>
<cfset startRowBack = url.startRow - rowsPerPage>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<title>Warner Robins Realtor - Trinette Rosales</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link href="cbstyle.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="../js/jquery.js"></script>
<script type="text/javascript" src="../js/easySlider1.5.js"></script>
<script type="text/javascript" src="../js/slider.js"></script>
<script type="text/javascript" src="../js/bslider.js"></script>
<link href="../slider.css" rel="stylesheet" type="text/css" />
</head>
<BODY>
<div id="site">
  <cfinclude template="/header.cfm">
  <div id="main2_left">
    <h1>Search Results</h1>
    <h3>Search Results: <cfoutput>#startRow# to
        <cfif startRowNext LTE idx.RecordCount>
          #EndRow#
          <cfelse>
          #idx.RecordCount#
        </cfif>
        of #idx.RecordCount# Properties</cfoutput></h3>
    </br>
    <div>
      <cfloop query="idx" startrow="#url.startRow#" endRow="#endRow#">
        <cfoutput>
          <cfdirectory action="list" directory="C:\www\idx\cgmls\ftp\pics\" filter="#ListID#.jpg" name="files">
          <cfif files.recordCount eq 0>
            <img src="/images/nophoto.jpg" width="90" height="65" />
            <cfelse>
            <img src="http://www.kwwarnerrobins.com/ftp/thumbs/#ListID#.jpg" width="90" height="65" alt="#StreetNumber# #StreetName# #City# #State# #ZipCode#" class="alignleft" />
          </cfif>
          <a href= "#urlencodedformat(trim(ListID))#/#StrNumber#-#StrName#-#Cty#-#State#.html">#StreetNumber# #StreetName# &nbsp;#City#, #State# #ZipCode#</a> | #dollarFormat(Price)# - Bedrs: #bedrooms# - Baths: #FullBaths#
          <p>&nbsp;</p>
        </cfoutput>
      </cfloop>
      <hr class="line" />
      <br />

      <p><cfinclude template="NextNIncludeBackNext.cfm"></p>
	<p>&nbsp;</p>
<p>&nbsp;</p>
      <h2>RESULTS PAGES:</h2> <p>&nbsp;</p><p><cfinclude template="NextNIncludePageLinks.cfm"></p>
      <p>&nbsp;</p>
     
      <p>&nbsp;</p>
      <p class="maintext">&nbsp;</p>
    </div>
  </div>
  <div id="main2_right">
    <h3></h3>
    <h2>NARROW YOUR SEARCH</h2>
    <cfform action="results-idx.cfm" method="post" enctype="multipart/form-data" name="Form">
        <label for="City">City</label>
        <cfselect name = "City" query = "getCity" value = "City" queryPosition="below" selected = "#City#">
        <option value=" ">Any</option>
        </cfselect>
        <br />
	<label for="Subdivision">Subdivision</label>
        <cfselect name = "Subdivision" query = "getSub" value = "Subdivision" queryPosition="below" selected = "#Subdivision#">
        <option value=" ">Any</option>
        </cfselect>
	<br />
        <label for="minimumprice">Price Min</label>
        <cfselect name="minimumprice">
          <option value="1" <cfif prefillminimumPrice eq 1>selected</cfif>>No Min.</option>
          <option value="10000" <cfif prefillminimumPrice eq 10000>selected</cfif>>$10,000</option>
          <option value="25000" <cfif prefillminimumPrice eq 25000>selected</cfif>>$25,000</option>
          <option value="50000" <cfif prefillminimumPrice eq 50000>selected</cfif>>$50,000</option>
          <option value="75000" <cfif prefillminimumPrice eq 75000>selected</cfif>>$75,000</option>
          <option value="100000" <cfif prefillminimumPrice eq 100000>selected</cfif>>$100,000</option>
          <option value="125000" <cfif prefillminimumPrice eq 125000>selected</cfif>>$125,000</option>
          <option value="150000" <cfif prefillminimumPrice eq 150000>selected</cfif>>$150,000</option>
          <option value="175000" <cfif prefillminimumPrice eq 175000>selected</cfif>>$175,000</option>
          <option value="200000" <cfif prefillminimumPrice eq 200000>selected</cfif>>$200,000</option>
          <option value="225000" <cfif prefillminimumPrice eq 225000>selected</cfif>>$225,000</option>
          <option value="250000" <cfif prefillminimumPrice eq 250000>selected</cfif>>$250,000</option>
          <option value="275000" <cfif prefillminimumPrice eq 275000>selected</cfif>>$275,000</option>
          <option value="300000" <cfif prefillminimumPrice eq 300000>selected</cfif>>$300,000</option>
          <option value="350000" <cfif prefillminimumPrice eq 350000>selected</cfif>>$350,000</option>
          <option value="400000" <cfif prefillminimumPrice eq 400000>selected</cfif>>$400,000</option>
          <option value="450000" <cfif prefillminimumPrice eq 450000>selected</cfif>>$450,000</option>
          <option value="500000" <cfif prefillminimumPrice eq 500000>selected</cfif>>$500,000</option>
        </cfselect>
        <br />
        <label for="maximumprice">Price Max</label>
        <cfselect name="maximumprice">
          <option value="50000000" <cfif prefillMaximumPrice eq 500000000>selected</cfif>>No Max.</option>
          <option value="25000" <cfif prefillMaximumPrice eq 25000>selected</cfif>>$25,000</option>
          <option value="50000" <cfif prefillMaximumPrice eq 50000>selected</cfif>>$50,000</option>
          <option value="75000" <cfif prefillMaximumPrice eq 75000>selected</cfif>>$75,000</option>
          <option value="100000" <cfif prefillMaximumPrice eq 100000>selected</cfif>>$100,000</option>
          <option value="125000" <cfif prefillMaximumPrice eq 125000>selected</cfif>>$125,000</option>
          <option value="150000" <cfif prefillMaximumPrice eq 150000>selected</cfif>>$150,000</option>
          <option value="175000" <cfif prefillMaximumPrice eq 175000>selected</cfif>>$175,000</option>
          <option value="200000" <cfif prefillMaximumPrice eq 200000>selected</cfif>>$200,000</option>
          <option value="225000" <cfif prefillMaximumPrice eq 225000>selected</cfif>>$225,000</option>
          <option value="250000" <cfif prefillMaximumPrice eq 250000>selected</cfif>>$250,000</option>
          <option value="275000" <cfif prefillMaximumPrice eq 275000>selected</cfif>>$275,000</option>
          <option value="300000" <cfif prefillMaximumPrice eq 300000>selected</cfif>>$300,000</option>
          <option value="350000" <cfif prefillMaximumPrice eq 350000>selected</cfif>>$350,000</option>
          <option value="400000" <cfif prefillMaximumPrice eq 400000>selected</cfif>>$400,000</option>
          <option value="450000" <cfif prefillMaximumPrice eq 450000>selected</cfif>>$450,000</option>
          <option value="500000" <cfif prefillMaximumPrice eq 500000>selected</cfif>>$500,000</option>
          <option value="600000" <cfif prefillMaximumPrice eq 600000>selected</cfif>>$600,000</option>
          <option value="700000" <cfif prefillMaximumPrice eq 700000>selected</cfif>>$700,000</option>
          <option value="800000" <cfif prefillMaximumPrice eq 800000>selected</cfif>>$800,000</option>
          <option value="1000000" <cfif prefillMaximumPrice eq 1000000>selected</cfif>>$1M</option>
        </cfselect>
        <br />
        <br />
         <label for="Bedrooms">Bedrooms</label>
        <cfselect name="Bedrooms">
          <option value=" " <cfif prefillBedrooms eq "1">selected</cfif>>Any</option>
          <option value="1" <cfif prefillBedrooms eq "1">selected</cfif>>1+</option>
          <option value="2" <cfif prefillBedrooms eq "2">selected</cfif>>2+</option>
          <option value="3" <cfif prefillBedrooms eq "3">selected</cfif>>3+</option>
          <option value="4" <cfif prefillBedrooms eq "4">selected</cfif>>4+</option>
          <option value="5" <cfif prefillBedrooms eq "5">selected</cfif>>5+</option>
        </cfselect>
        <br />
        <label for="FullBaths">Baths</label>
        <cfselect name="FullBaths">
          <option value=" " <cfif prefillFullBaths eq "1">selected</cfif>>Any</option>
          <option value="1" <cfif prefillFullBaths eq "1">selected</cfif>>1+</option>
          <option value="2" <cfif prefillFullBaths eq "2">selected</cfif>>2+</option>
          <option value="3" <cfif prefillFullBaths eq "3">selected</cfif>>3+</option>
          <option value="4" <cfif prefillFullBaths eq "4">selected</cfif>>4+</option>
        </cfselect>
        <br />
        <label for="ApproxAge">Approx. Age</label>
        <cfselect name="ApproxAge">
          <option value=" " <cfif prefillApproxAge eq "">selected</cfif>>Any</option>
          <option value="0 AND 0" <cfif prefillApproxAge eq "0 AND 0">selected</cfif>>New Construction</option>
          <option value="1 AND 5" <cfif prefillApproxAge eq "1 AND 5">selected</cfif>>1 to 5 yrs</option>
          <option value="6 AND 10" <cfif prefillApproxAge eq "6 AND 10">selected</cfif>>6 to 10 yrs</option>
          <option value="11 AND 20" <cfif prefillApproxAge eq "11 AND 20">selected</cfif>>11 to 20 yrs</option>
          <option value="21 AND 49" <cfif prefillApproxAge eq "21 AND 49">selected</cfif>>21 to 49 yrs</option>
          <option value="50 AND 998" <cfif prefillApproxAge eq "50 AND 998">selected</cfif>>50 + yrs</option>
        </cfselect>
        <br />
         <label for="ApproxAcreage">Approx. Acreage</label>
        <cfselect name="ApproxAcreage">
          <option value=" " <cfif prefillApproxAcreage eq " ">selected</cfif>>Any</option>
          <option value="0 AND 1" <cfif prefillApproxAcreage eq "0 AND 1">selected</cfif>>under 1 acre</option>
          <option value="1 AND 5" <cfif prefillApproxAcreage eq "1 AND 5">selected</cfif>>1 to 5 acres</option>
          <option value="6 AND 10" <cfif prefillApproxAcreage eq "6 AND 10">selected</cfif>>6 to 10 acres</option>
          <option value="11 AND 20" <cfif prefillApproxAcreage eq "11 AND 20">selected</cfif>>11 to 20 acres</option>
          <option value="20 AND 10000" <cfif prefillApproxAcreage eq "20 AND 10000">selected</cfif>>20 + acres</option>
        </cfselect>
        <br />
        
        <br />
        <p>
          <input type="submit" name="Submit" value="Search Properties" class="button" />
        </p>
      </cfform>
  </div>
  <br />
  <div id="spacer"></div>
  <cfinclude template="/footer.cfm">
</div>
</body>
</html>

Open in new window

Avatar of _agx_
_agx_
Flag of United States of America image
I get a syntax error for that url  ... near 'AND ApproxAcreage BETWEEN

<cfquery name = "idx" datasource="CGMLS"  result="idxResults">

Add a "result" attribute and dump it.  What's the sql generated the 1st time the page loads?



Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

Hi agx I added <cfdump var="#idx#"> right after my query tag on the results page. you are right, the page wont load at all. try

http://www.warner-robins-real-estate.com/search.cfm 

This form posts to the results page.
Avatar of _agx_
_agx_
Flag of United States of America image
Ignoring the syntax error, I think some of your filters are being added when they're not supposed to ... and that's why you're getting 0 records.  

Take the comparison below.  The 1st time the page loads, #form.ApproxAge# is an empty string "" . But the comparison is checking for a (space).  

<cfif form.ApproxAge IS NOT "(space)">
 AND ApproxAge BETWEEN #form.ApproxAge#
</cfif>

So it'll still add the filter, which probably doesn't match anything.  And that's why you get 0 results.  Again, that's ignoring the syntax error.  BETWEEN requires two values SomeColumn BETWEEN #X# and #Y#

<cfif IsDefined("url.FullBaths")>
  <cfset form.FullBaths = url.FullBaths>
</cfif>

Also unless you need to use both scopes, you could simplify things by using <cfform method=GET>. Then everything would be passed in a single scope:  URL.


Avatar of _agx_
_agx_
Flag of United States of America image
Ooops, our posts crosses.

the page wont load at all.

The BETWEEN clause is malformed.  It needs 2 values, but the code only has one

        Should be:          AND ApproxAge BETWEEN #form.ApproxAge# AND #SomeValueHere#
        instead of just    AND ApproxAge BETWEEN #form.ApproxAge#

try http://www.warner-robins-real-estate.com/search.cfm 

No luck.  I'm getting an error on search.cfm too:

        variable [SUBDIVISION] doesn't exist

Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

AND ApproxAge BETWEEN #form.ApproxAge#

I handled that in my form: instead of having 2 drop down boxes I incorporated it into one:

<cfselect name="ApproxAge">
          <option value=" " <cfif prefillApproxAge eq "">selected</cfif>>Any</option>
          <option value="0 AND 0" <cfif prefillApproxAge eq "0 AND 0">selected</cfif>>New Construction</option>
          <option value="1 AND 5" <cfif prefillApproxAge eq "1 AND 5">selected</cfif>>1 to 5 yrs</option>
          <option value="6 AND 10" <cfif prefillApproxAge eq "6 AND 10">selected</cfif>>6 to 10 yrs</option>
          <option value="11 AND 20" <cfif prefillApproxAge eq "11 AND 20">selected</cfif>>11 to 20 yrs</option>
          <option value="21 AND 49" <cfif prefillApproxAge eq "21 AND 49">selected</cfif>>21 to 49 yrs</option>
          <option value="50 AND 998" <cfif prefillApproxAge eq "50 AND 998">selected</cfif>>50 + yrs</option>
        </cfselect>

Avatar of _agx_
_agx_
Flag of United States of America image
I handled that in my form:

Yeah, but it doesn't work the 1st time the page loads. Because you're defaulting  #form.ApproxAge# to ""  the result is malformed sql. That's why you're getting a syntax error

        ie   AND ApproxAge BETWEEN (... nothing here  ... )

BUT ... using the field that way is dangerous because it's open to sql injection.  If it were me, I'd make the select value a list like:   #minValue#:#maxValue.  Then extract it with list functions. Here's an example

<cfparam name="form.ApproxAge" default="">
<cfset delim = ":">

<strong>DEMO</strong><br>
<cfoutput>
<cfif listLen(form.approxAge, delim) gte 2>
      AND  ApproxAge BETWEEN
                  #val(listFirst(form.approxAge, delim))# AND
                  #val(listLast(form.approxAge, delim))#
</cfif>
</cfoutput>

<cfform method="post">
      <cfselect name="ApproxAge">
          <option value="" <cfif form.ApproxAge eq "">selected</cfif>>Any</option>
          <option value="0:0" <cfif form.ApproxAge eq "0:0">selected</cfif>>New Construction</option>
          <option value="1:5" <cfif form.ApproxAge eq "1:5">selected</cfif>>1 to 5 yrs</option>
          <option value="6:10" <cfif form.ApproxAge eq "6:10">selected</cfif>>6 to 10 yrs</option>
          <option value="11:20" <cfif form.ApproxAge eq "11:20">selected</cfif>>11 to 20 yrs</option>
          <option value="21:49" <cfif form.ApproxAge eq "21:49">selected</cfif>>21 to 49 yrs</option>
          <option value="50:998" <cfif form.ApproxAge eq "50:998">selected</cfif>>50 + yrs</option>
    </cfselect>
      <input type="submit">
</cfform>      
Avatar of _agx_
_agx_
Flag of United States of America image
In real code I'd use cfqueryparam for all the values. But I left it out for brevity.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

ie   AND ApproxAge BETWEEN (... nothing here  ... )
So what is the proper way to handle it?   " "  not ""
Avatar of _agx_
_agx_
Flag of United States of America image
Using your existing code, it could be either one.  But you need to synch everything so you're using the same value everywhere.  Right now some parts of the code use an empty string, while others use a space:

      <cfparam name="form.ApproxAge" default="">    <!--- empty string --->
      <cfif form.ApproxAge IS NOT "(space)">  <!--- space --->
      <option value="(space)" <cfif prefillApproxAge eq "">selected</cfif>>Any</option> ... both

They all need to be the same.  For example make them *all* an empty string

        <cfparam name="form.ApproxAge" default="">
        <cfif form.ApproxAge IS NOT "">
        <option value="" <cfif prefillApproxAge eq "">selected</cfif>>Any</option>

BUT ... like I said that's very dangerous sql.  I wouldn't do it that way. I'd use something like my previous example.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

Ok thanks agx let me go back and synch everything like you suggested, let me get this thing working first then I will tackle the sql injection issue, and thanks for mentioning that also.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

and even more thanks for writing some example code!  lol ...programming is tough business for a non programmer. I do apreciate it.
Avatar of _agx_
_agx_
Flag of United States of America image
No problem. I always enjoy helping out a fellow cf-er :)
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

agx, I made the changes you suggested and it seems to be working now, page is viewable, at least on my end. One more question...how can I make this page load all the results the first time you hit the page, before the form is submitted?
Avatar of _agx_
_agx_
Flag of United States of America image
<cfparam name="form.ApproxAcreage" default="">  <!--- empty string --->
<cfif form.ApproxAcreage IS NOT "(space)"> <!--- space --->
<option value="(space)" <cfif prefillApproxAcreage eq "(space)">selected</cfif>>Any</option>

Btw. You might have a similar issue with FORM.ApproxAcreage. So I'd synch up all those values too.

AND Bedrooms >= '#form.Bedrooms#'
AND FullBaths >= '#form.FullBaths#'

What is the data types of those fields? Are they VARCHAR or some type of number like INTEGER
Avatar of _agx_
_agx_
Flag of United States of America image
Posts crossed again...

how can I make this page load all the results the first time you hit the page, before the form is submitted?

Something about the default values is knocking out the results the 1st time the page loads :) We need to figure out what that is.  Can you add the "result" attribute to the query and post the generated SQL?  

<cfquery name = "idx" datasource="CGMLS" result="idxResult">.... actual query ....</cfquery>
<cfdump var="#idxResult#">

AND Bedrooms >= '#form.Bedrooms#'
AND FullBaths >= '#form.FullBaths#'


My guess is it's something about those 2 comparisons. (Are those fields numeric or strings?) But I need to see the generated SQL to confirm it.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

I took out all the "(space)" So everything is "" empty string now.

city field is varchar
subdivision field is varchar
price is decimal
bedrooms is int
fullbaths is int
approxage is int
approxacreage is int
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

Struct  
cached boolean false
 
COLUMNLIST string ListID,Class,Type,Area,Price,StreetNumber,StreetDirection,Streetname,City,State,Zipcode,Status,SaleOrRent,Level,NumLivingAreas,Bedrooms,FullBaths,Garage,MasterBedroomOnMainFloor,NumDiningAreas,SplitBedroomPlan,HalfBaths,Occupancy,AgentID,AgentName,AgentPhone,ListingOfficeID,ListingOfficeName,ListingOfficePhone,CoAgent2ID,CoAgent2Name,CoAgent2Phone,CoAgentOfficeID,CoAgent2OfficeName,CoAgent2OfficePhone,Subdivision,County,ApproxAcreage,LotDim,SQFT,SQFTBelowGrade,TotalFinishedSQFT,BelowGradeUnfinishedSQFT,SQFTSource,LeasePurchase,ConstructionStatus,YearBuilt,Pool,ApproxAge,EstCompletionDate,ElementarySch,MiddleSch,HighSch,NumFireplaces,TotalNumRooms,SecondarySuite,Kitchen,BreakfastArea,DiningRoom,DiningRoomType,Foyer,LivingRoom,GreatRoom,Den,MasterBedroom,Bedroom2,Bedroom3,Bedroom4,Bedroom5,LaundryRoom,OtherRoom1,OtherRoom2,Remarks,Amenities,VirtualTour,ListDate,StrNumber,StrName,Cty,St,Zip
 
executionTime number 15
 
RECORDCOUNT number 0
 
SQL string SELECT * FROM listings,seo WHERE Listings.ListID = Seo.ListID AND Price BETWEEN '' AND '' AND Bedrooms >= '' AND FullBaths >= '' ORDER BY Price ASC  
 
Avatar of _agx_
_agx_
Flag of United States of America image
AND Bedrooms >= '#form.Bedrooms#'
AND FullBaths >= '#form.FullBaths#'

Hmm... I'm not sure what mySQL would make of those 2 parts if the columns are integers.  I would include the filter if the form values are numeric  ie Change those 2 lines to:

<cfif IsNumeric(form.Bedrooms)>
     AND Bedrooms >= <cfqueryparam value="#form.Bedrooms#" cfsqltype="cf_sql_integer">
</cfif>
<cfif IsNumeric(form.FullBaths)>
     AND FullBaths >= <cfqueryparam value="#form.FullBaths#" cfsqltype="cf_sql_integer">
</cfif>

Also, did you run the dump of the "results"?
Avatar of _agx_
_agx_
Flag of United States of America image
LOL.  My timing is so off tonight...


AND Price BETWEEN '' AND ''
AND Bedrooms >= ''
AND FullBaths >= ''

Thanks.  One or all of those comparisons are what's causing 0 results.  There probably aren't any records with a Price between "" and "".

Try changing the price comparison to:

<cfif IsNumeric(form.minimumprice) and IsNumeric(form.maximumprice)>
       AND Price BETWEEN #val(form.minimumprice)# AND #val(form.maximumprice)#
</cfif>

Also, the bedrooms and FullBaths comparison to:

<cfif IsNumeric(form.Bedrooms)>
     AND Bedrooms >= <cfqueryparam value="#form.Bedrooms#" cfsqltype="cf_sql_integer">
</cfif>
<cfif IsNumeric(form.FullBaths)>
     AND FullBaths >= <cfqueryparam value="#form.FullBaths#" cfsqltype="cf_sql_integer">
</cfif>

After you get it working you can go back and add cfqueryparam everywhere.

Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

hmmm  I put the code in the page and ran the page copied and pasted what was dumped , maybe I didnt do it right??

<cfquery name = "idx" datasource="CGMLS" result="idxResult">.... actual query ....</cfquery>
<cfdump var="#idxResult#">

Avatar of _agx_
_agx_
Flag of United States of America image
No, you did it perfectly.  Our posts just clashed. So I didn't see you'd posted it before I responded ;-)

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

Problem solved!!! many thanks agx.
Avatar of _agx_
_agx_
Flag of United States of America image
Great.   Just let me know if you want to tackle adding in the cfqueryparam's.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

Yes I do! but I dont really know where to begin on that issue.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

Id be glad to open another question if you like
Avatar of _agx_
_agx_
Flag of United States of America image
Yeah, just to keep things clean I'd open a new question.
Avatar of Dusty
Dusty
Flag of United States of America image

ASKER

I just opened a new question , but I wont be back til later.. im on the east coast...zzzz

https://www.experts-exchange.com/questions/26833112/cfqueryparam's-question.html