Forums
This topic is locked
SQL Searching
Posted 15 Mar 2004 05:45:36
1
has voted
15 Mar 2004 05:45:36 Graham Bitodssi posted:
Can anybody please help with how I can add another column from the database to this query so that it will search 3 columns. (still using only the two (+ sign seperated) search criteria))?Cheers & Thanks
My code so far is:
Form Page:
form.cfm
<html>
<head>
<title>Search Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
Insert two keywords separated by "+" sign.<br>
<form action="search.cfm" method="post" name="form" id="form">
<input name="keyword" type="text" id="keyword">
<input type="submit" name="Submit" value="GO">
</form>
</body>
</html>
and the search page is:
<!-----search.cfm----->
<html>
<head>
<title>Search Results</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<cfset dsn="CarWanted">
<cfif isDefined ("form.keyword"
><!--- // IF THE KEYWORD SUBMITTED DOES NOT CONTAIN + SIGN THEN JUST DO A BASIC QUERY // --->
<cfif form.keyword DOES NOT CONTAIN "+">
<cfquery name="qUsers" datasource="#dsn#">
SELECT DEALER, LOCATION FROM AllDealers WHERE DEALER LIKE '%#form.keyword#%'
OR LOCATION LIKE '%#form.keyword#%'
</cfquery>
<p>The name you choose was:</p>
<cfoutput query="qUsers">#qUsers.dealer# - #qUsers.location#<br></cfoutput>
<!--- // IF THE KEYWORD DOES CONTAIN A + THEN BREAK THE KEYWORD INTO TWO VARIABLES
AND SEARCH USERS USING "LIKE" & "OR" FUNCTIONS // --->
<cfelse>
<cfset search_1 = trim(ListFirst(form.keyword, '+'))>
<cfset search_2 = trim(Listlast(form.keyword, '+'))>
<cfoutput>Search word 1 is: #search_1#</cfoutput><br>
<cfoutput>Search word 2 is: #search_2#</cfoutput>
<br><br>
<cfquery name="qUsers" datasource="#dsn#">
SELECT *
FROM AllDealers
WHERE 0+1 AND DEALER LIKE '%#search_1#%' AND LOCATION LIKE '%#search_2#%'
</cfquery>
<cfoutput query="qUsers">#qUsers.dealer# - #qUsers.location#<br></cfoutput>
</cfif>
</cfif>
</body>
</html>
Thanks to you all