Search Result Problems

[RobHat]

Hi guys me again.
I have a really annoying problem with a search facility in my site. I have done loads of these before which work fine and have referenced them to find out what is wrong with this search interface but cannot locate the problem.

I have a search form which searches a table for apartments. It passes to a results page and lists the results, nice and simple. The only slight difference is that the form is displaying options from 2 tables and the results are based on these 2 tables.

Search Form Code:

In The Header:
<cfquery name="rs_unittype" datasource="datasource">
SELECT DISTINCT Type
FROM dbo.table1
ORDER BY Type ASC
</cfquery>
<cfquery name="rs_unitlocation" datasource="datasource">
SELECT country, region, area
FROM dbo.table2
ORDER BY country ASC, region ASC, area ASC
</cfquery>
<cfquery name="rs_unitbeds" datasource="datasource">
SELECT DISTINCT bedrooms
FROM dbo.table1
ORDER BY bedrooms ASC
</cfquery>

The Form:
<form action="results.cfm" method="get" name="unitsearch" id="unitsearch">
Beds: <select name="selectbedrooms" id="selectbedrooms">
<option value="%">Any</option>
<cfoutput query="rs_unitbeds">
<option value="#rs_unitbeds.bedrooms#">#rs_unitbeds.bedrooms#</option>
</cfoutput>
</select>
Type: <select name="selecttype" id="selecttype">
<option value="%">Any</option>
<cfoutput query="rs_unittype">
<option value="#rs_unittype.Type#">#rs_unittype.Type#</option>
</cfoutput>
</select>
Location:<select name="selectcountry3" id="selectcountry3" onChange="TCN3_reload(this)">
<option value="%">Any</option>
</select>
<select name="selectregion3" id="selectregion3" onChange="TCN3_reload(this)">
<option value="%">Any</option>
</select>
<select name="selectarea3" id="selectarea3" onChange="TCN3_reload(this)">
<option value="%">Any</option>
</select>
<input name="button3" type="submit" id="button3" value="Search New Builds" />
</form>

(there is some code missing from here for instance there is a large amount of script for the dependant dropdowns in location select but I thought you would not need to see this as it works fine.)

On the results page I have this query that joins the 2 tables and the where clause is aimed at the URL for the information it needs.

In the Head:
<cfparam name="URL.selectbedrooms" default="%">
<cfparam name="URL.selecttype" default="%">
<cfparam name="URL.selectcountry3" default="%">
<cfparam name="URL.selectregion3" default="%">
<cfparam name="URL.selectarea3" default="%">

<cfquery name="rs_unitsearchresults" datasource="datasource">
SELECT uni.DevREF, uni.BlockNo, uni.FloorNo, uni.UnitNo, uni.BuiltSize, uni.Views, uni.Type, uni.Bedrooms, uni.EURO, uni.Status,
dev.DevREF, dev.area, dev.country, dev.region
FROM dbo.dev_unitlist AS uni LEFT JOIN dbo.developments AS dev ON uni.DevREF = dev.DevREF
WHERE Bedrooms = <cfqueryparam value="#URL.selectbedrooms#" cfsqltype="cf_sql_varchar"> AND Type = <cfqueryparam value="#URL.selecttype#" cfsqltype="cf_sql_varchar"> AND Country = <cfqueryparam value="#URL.selectcountry3#" cfsqltype="cf_sql_varchar"> AND Region = <cfqueryparam value="#URL.selectregion3#" cfsqltype="cf_sql_varchar"> AND area = <cfqueryparam value="#URL.selectarea3#" cfsqltype="cf_sql_varchar">
ORDER BY EURO ASC
</cfquery>

For results I have a basic repeat region to dump the results. All of this works ok if the user selects something that matches the db records perfectly. If they select any then it returns nothing.

I have replicated a working search and it still will only bring me back the specific results. I am wondering whether this is because of the table join.

Any ideas appreciated.

Rob

 

[RobHat]

sorry I left the table names in the results query, dbo.dev_unitlist would be table1 and dbo.developments would be table2.

 

[RobHat]

Ok I solved it, In the results query I changed the Where clause to use LIKE rather than = and it works fine. how annoying I have been trying this all day and thats the one thing I did'nt think it would be.

Regards

Rob