INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Optimization

Simple way to implement an complex search form. by shaddow
Posted: 4 Mar 04 (Edited 31 May 08)

I've seen a few time ago somone asking help for an complex search form. I cant remember what post but the search page was to complex to at least be shure that you wont miss something from the search.

I've been using an simple idee for some time and it seems that it works for any amount of search fields.

I'll make this example with 4 parameters.

CODE

database - clients
id        - numeric
name      - varchar(text)
ocupation - varchar(text)
age       - numeric

i'll assume that the search will be after name,ocupation and age with and display order


I've received a good sugestion to add some warnings about how vulnerable is this code to "SQL injection attacks". This is true and the code presented here should be used as guideline and should be propper sanitized against SQL injection attacks.


search.html
<form action="search.asp" method=post>
Name:<input name="name"><br>
Ocupation:<input name="ocupation"><br>
Age:<input name="age"><br>
Order:<select name="order">
       <option value="">No order
       <option value="name">Name
       <option value="ocupation">Ocupation
       <option value="age">Age
      </select>
</form>

search.asp -  i'll explain only the important code here
<%
'objConn - database connection
'rs      - recordset object
'using Request("fieldname") will solve the post or get paramaters

'getting search variables
name=Request("name")
ocupation=Request("ocupation")
age=Request("age")
order=Request("order")

'initializing query variables
sql_name=""
sql_ocupation=""
sql_age=""
sql_order=""

'setting up the query parts
'if name it's empty it search for every name
if name<>"" then
 sql_name=" AND name like '%"&name&"%'"
end if

'if ocupation it's empty it search for every ocupation
if ocupation<>"" then
 sql_ocupation=" AND ocupation like '%"&ocupation&"%'"
end if

'if age it's empty it search for every age
if age<>"" then
 sql_age=" AND age="&age
end if

if order<>"" then
 sql_order=order
else
 'default order
 sql_order="id"
end if

'building up the main query simple and easy
sql="select * from clients where 1=1 " & sql_name & " " & sql_ocupation & " " & sql_age & " order by " & sql_order

rs.Open sql,objConn,3,3

%>

This should solve all the problems and complications in your search criteria.
The mai trick is using "where 1=1" thing which gives you the liberty to add to each condition easy in your main sql statement.
 

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close