| Posted By |
Discussion Topic: Simple search help.
|
|
trist@r |
12-08-2004 @ 1:21 AM |
|
|
New Member
Posts: 20
Joined: Jan 2003
|
Hi,
I am simply trying to make a search function which displays results that match the string entered into a form input box.
My form code is:
<form action="searchresult.cfm?Business=#searchform.Search#" method="post">
<input name="Search" type="text" id="keyword" />
<input type="submit" name="Submit" value=" GO " />
</form>
ShowResults code:
<cfset Form.Business = "#Business#">
<cfquery name="sResult" datasource="proactive">
SELECT *
FROM Organisation
WHERE Business Like '%#Business#%'
</cfquery>
<p>The name you choose was: <cfoutput query="sResult">#Business#</cfoutput></p>
<table border="0" cellpadding="2" cellspacing="1" width="98%" align="center" class="tableborder">
<tr class="headingtext">
<td width="108">OrgID</td>
<td width="117">Classification</td>
<td width="96">Status</td>
<td width="187">Industry</td>
<td width="290">Business Name</td>
<td width="104">Postcode</td>
<td width="264">Alerts</td>
</tr>
<cfoutput query="sResult">
<tr bgcolor="###iif(currentrow MOD 2,DE('e1e1e1'),DE('cccccc'))#">
<td class="contenttext">#sResult.OrgID#</td>
<td class="contenttext">#sResult.Classification#</td>
<td class="contenttext"><cfif sResult.Status eq "Dormant"><font color="red">#sResult.Status#</font><cfelse>#sResult.Status#</cfif></td>
<td class="contenttext">#sResult.Industry#</td>
<td class="contenttext"><a href="detail-active.cfm?OrgID=#sResult.OrgID#">#sResult.Business#</a></td>
<td class="contenttext">#sResult.Postcode#</td>
<td class="contenttext"><cfif #sResult.AlertDt# eq "#DateFormat(Now(), "mm/dd/yy")# #TimeFormat(Now(), "12:00")# am">#sResult.AlertTxt#<cfelse></cfif></td>
</tr>
</cfoutput>
</table>
The thing is, it just displays all the results from my database.
Not sure what i am missing here.
Thanks.
Tristan
|
|
Newton |
|
12-08-2004 @ 1:32 AM |
|
|
Senior Member
Posts: 302
Joined: Jul 2003
|
Really you should always have the variable first = to the value so....
<cfset Form.Business = "#Business#">
change to
<cfset "#Business#" = Form.Business >
but that still isnt right you also need to drop the #'s and the quotes since buisness is your variable
<cfset Business = Form.Business >
you can try that but really whay set a variable to a variable?
Just lose that whole line and do this with your query
<cfquery name="sResult" datasource="proactive">
SELECT *
FROM Organisation
WHERE Business Like '%#Form.Business#%'
</cfquery>
But in actuallity that probably wont work ewither
unless you have percentage signs in your database....
so the pull them wildcards out of the quotes
<cfquery name="sResult" datasource="proactive">
SELECT *
FROM Organisation
WHERE Business Like %'#Form.Business#'%
</cfquery>
My MYSQL is a bit dusty but that should work for you
And the fourth angel poured out his vial upon the sun
and power was given unto him to scorch men with fire
Revalations 16:8
This message was edited by Newton on 12-8-04 @ 1:33 AM
|
|
trist@r |
|
12-08-2004 @ 2:21 AM |
|
|
New Member
Posts: 20
Joined: Jan 2003
|
Thanks Newton. I actually got an error when i put the percentage signs in the SQL query outside the quotes.
However, i got it to work after removing the cfset as you recommended, here is the new code for anyone who has the same problem in the future.
Form to search:
<form action="searchresult.cfm" method="post">
<input name="Search" type="text" id="keyword" />
<input type="submit" name="Submit" value=" GO " />
</form>
Display Results
<cfquery name="sResult" datasource="proactive">
SELECT *
FROM Organisation
WHERE Business Like '%#Form.Search#%'
ORDER BY Business ASC
</cfquery>
<p>The name you choose was: <cfoutput query="sResult">#Form.Search#</cfoutput></p>
<table border="0" cellpadding="2" cellspacing="1" width="98%" align="center" class="tableborder">
<tr class="headingtext">
<td width="108">OrgID</td>
<td width="117">Classification</td>
<td width="96">Status</td>
<td width="187">Industry</td>
<td width="290">Business Name</td>
<td width="104">Postcode</td>
<td width="264">Alerts</td>
</tr>
<cfoutput query="sResult">
<tr bgcolor="###iif(currentrow MOD 2,DE('e1e1e1'),DE('cccccc'))#">
<td class="contenttext">#sResult.OrgID#</td>
<td class="contenttext">#sResult.Classification#</td>
<td class="contenttext"><cfif sResult.Status eq "Dormant"><font color="red">#sResult.Status#</font><cfelse>#sResult.Status#</cfif></td>
<td class="contenttext">#sResult.Industry#</td>
<td class="contenttext"><a href="detail-active.cfm?OrgID=#sResult.OrgID#">#sResult.Business#</a></td>
<td class="contenttext">#sResult.Postcode#</td>
<td class="contenttext"><cfif #sResult.AlertDt# eq "#DateFormat(Now(), "mm/dd/yy")# #TimeFormat(Now(), "12:00")# am">#sResult.AlertTxt#<cfelse></cfif></td>
</tr>
</cfoutput>
</table>
Thanks.
|
