Senior Member
Posts: 376
Joined: Feb 2005
|
Hello -
I have this weird problem - for whatever reason I have been getting 2 of the same records being displayed every time I click my "search" button.
What I am trying to do is this, Search 2 tables (Listings, Vacancys) I want to say,
Select All records from Listings, Only if Vacancys.ListingID has a record with the same "Listings.ListingID"
I get some results, but not the kind of results I am hoping for.
http://www.getrenting.ca/city.cfm?c=Brockville%20&ID=816
Try this link - and do the search default search. The default on the page will bring back every record from Listings, that has the same CityID. Once you click search, it by default should being back the same amount of records, becuase everything is checked. And it does - but it bring back 2 of the same record. So I am thinking it has to do with the .RecordCount
Here is Code for the Query
<cfif not IsDefined("URL.Search")>
<cfquery name="li" datasource="#Application.databaseDatasource#" username="#Application.databaseUsername#" password="#Application.databasePassword#">
SELECT *
FROM Listings
WHERE CityID = #URL.ID#
AND Status = 1
</cfquery>
<cfelse>
<cfquery name="Li" datasource="#Application.databaseDatasource#" username="#Application.databaseUsername#" password="#Application.databasePassword#">
SELECT *
FROM Listings, Vacancys
WHERE Listings.CityID = #URL.ID#
AND Vacancys.ListingID = Listings.ListingID
AND Listings.Status = 1
<cfif IsDefined("form.RentMin2")>
AND Vacancys.Min_Rent >= #Form.RentMin2#
</cfif>
<cfif IsDefined("form.RentMax2")>
AND Vacancys.Min_Rent <= #Form.RentMax2#
</cfif>
<cfif IsDefined("form.Low_rise")>
AND Listings.Building_Type = 'Low_rise'
</cfif>
<cfif IsDefined("form.High_Rise")>
OR Listings.Building_Type = 'High_Rise'
</cfif>
<cfif IsDefined("form.Condo")>
OR Listings.Building_Type = 'Condo'
</cfif>
<cfif IsDefined("form.House")>
OR Listings.Building_Type = 'House'
</cfif>
<cfif IsDefined("form.Townhouse")>
OR Listings.Building_Type = 'Townhouse'
</cfif>
<cfif IsDefined("form.Multi")>
OR Listings.Building_Type = 'Multi'
</cfif>
<cfif IsDefined("form.Room")>
OR Listings.Building_Type = 'Room'
</cfif>
</cfquery>
</cfif>
<cfquery name="city" datasource="#Application.databaseDatasource#" username="#Application.databaseUsername#" password="#Application.databasePassword#">
SELECT *
FROM City
WHERE CityID = #URL.ID#
AND Status = 1
</cfquery>
<cfset MaxRows_li=5>
<cfset StartRow_li=Min((PageNum_li-1)*MaxRows_li+1,Max(li.RecordCount,1))>
<cfset EndRow_li=Min(StartRow_li+MaxRows_li-1,li.RecordCount)>
<cfset TotalPages_li=Ceiling(li.RecordCount/MaxRows_li)>
<cfset QueryString_li=Iif(CGI.QUERY_STRING NEQ "",DE("&"&XMLFormat(CGI.QUERY_STRING)),DE(""))>
<cfset tempPos=ListContainsNoCase(QueryString_li,"PageNum_li=","&")>
<cfif tempPos NEQ 0>
<cfset QueryString_li=ListDeleteAt(QueryString_li,tempPos,"&")>
</cfif>
Here is the code from the form.
<form name="search_submit" action="city.cfm?c=<cfoutput>#URL.c#&ID=#URL.ID#</cfoutput>&Search=True" method="post">
<h3><img src="images/bn1.png" alt="selected cities"> Your selected city</h3>
<div class="dpp">
<b style="width: 190px; float: left;"><cfoutput>#City.City#, #replace("#City.Province#","_"," ","All")#</cfoutput></b>
<a href="Search.cfm" id="infrmlink" onclick="search_process()">Choose another city</a>
</div>
<h3><img src="images/bn2.png" alt="price range"> Set your price range ($)</h3>
<div class="dpp">
<input id="RentMin" name="RentMin2" size="4" maxlength="4" value="200" type="text"> to
<input id="RentMax" name="RentMax2" size="4" maxlength="5" value="900" type="text">
</div>
<h3><img src="images/bn3.png" alt="property type"> Choose property type</h3>
<div class="dpp">
<ul class="CtrlList" id="TypeCon">
<li><input name="Low_rise" checked="checked" type="checkbox">Low rise apartments</li>
<li><input name="High_Rise" checked="checked" value="High rise" type="checkbox">High rise apartments</li>
<li><input name="Condo" checked="checked" value="Condo" type="checkbox">Condo</li>
<li><input name="House" checked="checked" value="House" type="checkbox">House</li>
<li><input name="Townhouse" checked="checked" value="Townhouse" type="checkbox">Townhouse</li>
<li><input name="Multi" checked="checked" value="Multiunit house" type="checkbox">Multi-unit house</li>
<li><input name="Room" checked="checked" value="Room" type="checkbox">Room for rent</li>
</ul>
</div>
<h3><img src="images/bn4.png" alt="number of bedrooms"> Set min. # of bedrooms</h3>
<div class="dpp">
<select name="Beds" id="BedsCon">
<option selected="selected" value="A">All</option>
<option value="B">Bach/Studio</option>
<option value="J">1 Bedroom Jr.</option>
<option value="1">1 Bedroom</option>
<option value="2">2 Bedrooms</option>
<option value="3">3 Bedrooms</option>
<option value="4">4 Bedrooms</option>
<option value="5">5+ Bedrooms</option>
</select>
</div>
<div class="dpp"><input name="submit" type="submit" value="submit"></div>
</form>
You can also look at some of the code - by going to that URL, and clicking View Source.
Sirseth
|
Sirseth
08-24-2008 @ 11:05 AM
