redho home | products | services

Programming Forums


Community for Java, PHP, Perl C, ASP and Python programmers
Monday 15 October 2018 22:21

Ask your IT question here

How do I build a query with optional parameters?



 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Programming Forums -> ASP in depth
View previous topic :: View next topic  
Author Message
van
Guest Programmer




How do I build a query with optional parameters?
Reply with quote
 
Fong foo
Guest Programmer




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!

Reply with quote
 
Page 1 of 1
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Programming Forums -> ASP in depth


Dubai Forums - Expat Help | Vegan Forum | Java Programming | 3d Design Resources | 3d Forum | 3D Jobs | 3D Textures | Paris Forum | Europe Forum
Dubai Classifieds | Dubai Property | Jobs in Dubai | Free London Classifieds | Jobs in London UK

High Quality, Custom 3d animation and Web Design solutions Royal Quality Web Hosting Services Vegetarian and Animal Rights news

© 2018 RedHo