Build SQL search string

[JP SIngh]

Hi There

I am creating a search page and need help writing the code to build the sql
string.

I have 3 fields on the main page which a user can choose to enter search
terms in any of the 3 fields. My question is how do I write the logic so it
only includes the fields in the search criteria where the user has entered
anything.

Fields are

Production No
Production Title
Synopsis

The user may enter any, all or some of the search teams in the fields.

On the search page I am using lots of if conditions to build my sql string
like

strSQL = "Select * from Productions where "

now after where there are lots of permutations and combination like
production no could be empty or it could be filled in and the other fields
might be empty or filled in as well.

Does someone has a simple logic to build the correct sql String?

All fields are text.

Thanks

 

[CJM]

A simple solution would be:

sSQL = "Select * from Productions where ProductNo like '%" & sProductNo &
"%'," & _
"ProductTitle like '%" & sProductTitle & "%'," & _
"Synopsis like '%" & sSynopsis & "%',"

I havent tested it, and it also depends on if you are allowing fuzzy
searches (using LIKE) rather than exact searches. I've assumed fuzzy
searching.

Chris

 

[Ken VdB]

Hi,

First of all I think it is a bad idea to use "SELECT *" as this will return
more data then you are going to consume on the web page. The client (in
this case your web page) should only ever select data that it is going to
use. This is good practice.

However I would do something like this

strSQL = "SELECT Field1, Field2, Field3 "
strSQL = strSQL & "FROM Productions "
strSQL = strSQL & "WHERE (1 = 1) "
If Not strProductionNo = "" Then strSQL = strSQL & "AND (ProductionNo = '" &
strProductionNo & "') "
If Not strProductionTitle = "" Then strSQL = strSQL & "AND (ProductionTitle
LIKE '%" & strProductionTitle & "%') "
If Not strSynopsis = "" Then strSQL = strSQL & "AND (Synopsis LIKE '%" &
strSynopsis & "%') "
strSQL = strSQL & "ORDER BY Field1"

This way the user can fill in as many of the search fields as he or she
wishes in order to narrow down the results. You need part of your WHERE
clause to be unconditional so I often use (1=1) because this never
eliminates any rows. Realize however if the user does not put in any search
criteria that all the rows in the table will be returned. For this reason
you may wish to put some kind of logic in place that will not perform the
search if all the fields are empty. Also, particularly when doing search
engines, it is a good idea to use a TOP clause to restrict the maximum
number of results. You should not rely on the underlying table/view to have
a reasonable number of rows. It may have 100's of thousands of rows. No
one really wants to scroll through 500 results from their search engine
anyway. Using a TOP 500 statement is a good idea to reduce overhead on your
server and if the user gets that many results back it's time to narrow the
search down anyway.

Cheers,

Ken.

 

[JP SIngh]

What happens incase used did not enter anything in the second and third
fields?

 

[CJM]

If the user doesnt enter a value for any of the fields, the query will
search for '%%', which is equivalent to '%', which clearly will select all
values...

I would also point you in the direction of Ken's suggestion. I was going to
suggest that solution but apathy got the better of me(!).

His is different in that his solution does extra work on the web server, by
building a more efficient SQL statement. This solution, minimises the work
done in ASP, but makes the DB Server do a bit more work.

In all but the most demanding environments, it probably wont make a blind
bit of difference!

Chris