Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CFML - Weird Issue with a search form

Posted on 2011-02-18
28
Medium Priority
?
954 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

 
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
 
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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

721 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