How do I build a query with optional parameters?
|
| View previous topic :: View next topic |
| Author |
Message |
van
Guest
|
| How do I build a query with optional parameters? |
| |
 |
|
|
|
Fong foo
Guest
|
Given this search page:
| Quote: |
<form method=post action=find.asp>
<input type=text name=keywords>
<p>
<select name=kind>
<option value=' AND '>All words
<option value=' OR '>Any word(s)
</select>
<p>
<input type=submit>
</form>
|
The following ASP script will split up the search terms and perform an AND or OR search appropriately:
| Code: |
<%
SQL = "SELECT <column_list> FROM table"
keywords = trim(replace(Request.Form("keywords"),"'","''"))
if len(keywords)>0 then
sqlExtra = " WHERE "
kind = Request.Form("kind")
keywordArray = split(keywords)
for i = 0 to ubound(keywordArray)
keyword = keywordArray(i)
if i > 0 then sqlExtra = sqlExtra & kind
' if you want to match entire words only - note spaces!
sqlExtra = sqlExtra & " (column LIKE '% " & keyword & " %')"
' if you want to find each string inside of other words
' sqlExtra = sqlExtra & " (column LIKE '%" & keyword & "%')"
next
end if
response.write sql & left(sqlExtra,len(sqlExtra)-len(kind))
%> |
Note that you may want to make your search case sensitive (for SQL Server, see Article #2152). You also might want to have a list of noise words, such as 'a' and 'the' - particularly if you don't use an EXACT PHRASE search. For example, a search for 't' might return your entire table! Yes, that's the user's fault, but it's still YOUR server doing all that extra work. See Article #2502 for some sample code, and see these search results to see that code in action! |
| |
|
|
|
|
FAQ
Archive
Log in
Register
