Solved

CFML - Weird Issue with a search form

Posted on 2011-02-18
28
943 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Bang-O-Matic
  • 15
  • 13
28 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 34931162
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?



0
 

Author Comment

by:Bang-O-Matic
ID: 34931238
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931241
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.


0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931247
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

0
 

Author Comment

by:Bang-O-Matic
ID: 34931260
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>

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931268
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>      
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931275
In real code I'd use cfqueryparam for all the values. But I left it out for brevity.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931279
ie   AND ApproxAge BETWEEN (... nothing here  ... )

So what is the proper way to handle it?   " "  not ""
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931289
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.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931294
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.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931297
and even more thanks for writing some example code!  lol ...programming is tough business for a non programmer. I do apreciate it.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931320
No problem. I always enjoy helping out a fellow cf-er :)
0
 

Author Comment

by:Bang-O-Matic
ID: 34931370
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?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931382
<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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 52

Expert Comment

by:_agx_
ID: 34931393
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.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931402
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
0
 

Author Comment

by:Bang-O-Matic
ID: 34931416
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  
 
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931420
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"?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931435
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.

0
 

Author Comment

by:Bang-O-Matic
ID: 34931440
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#">

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931448
No, you did it perfectly.  Our posts just clashed. So I didn't see you'd posted it before I responded ;-)

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34931463
So to recap.  Try changing the Price, Bedroom and FullBath comparisons as follows:

<cfif IsNumeric(form.minimumprice) and IsNumeric(form.maximumprice)>
       AND Price BETWEEN #val(form.minimumprice)# AND #val(form.maximumprice)#
</cfif>
<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>
0
 

Author Comment

by:Bang-O-Matic
ID: 34931478
Problem solved!!! many thanks agx.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931485
Great.   Just let me know if you want to tackle adding in the cfqueryparam's.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931493
Yes I do! but I dont really know where to begin on that issue.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931498
Id be glad to open another question if you like
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34931514
Yeah, just to keep things clean I'd open a new question.
0
 

Author Comment

by:Bang-O-Matic
ID: 34931560
I just opened a new question , but I wont be back til later.. im on the east coast...zzzz

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/Cold_Fusion_Markup_Language/Q_26833112.html
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now