Multi criteria search by sql?

Databases
1

Hi, I am developing a travel agent software, and I am stocked at a search function, I am letting user to search “tour” table either by “depart_day” or “destination” or “tour_name”, I never know user will search by using one of the criteria, or they will use any two of them at the same time, or they will search using all the three criteria. The more criteria they use, they can narrow down the search and get exactly what they want. Below is my plan, but I think this is way too complicated, if I want to expand more criteria for user, the code will be too large, I don’t know if there is some programming language technique especially to solve these kind of problems?

[code]method parameters: pTourName as string = “”, pDepartDay as string = “”, pDestination as string = “”

Dim sql as String

IF pTourName <> “” and pDepartDay = “” and pDestination = “” Then
sql = “SELECT * FROM tour WHERE ‘tour_name’ LIKE '%”+pTourName+"%’;"
ELSEIF pTourName = “” and pDeartDay <>"" and pDestination = “” Then
sql = “SELECT * FROM tour WHERE ‘depart_day’ = “+pDepartDay+”;”
ELSEIF pTourName = “” and pDepartDay = “” and pDestination <> “” Then
sql = “SELECT * FROM tour WHERE ‘pDestination’ = “+pDestination+”;”
ELSEIF pTourName <> “” and pDepartDay <> “” and pDestination = “” Then
sql = “SELECT * FROM tour WHERE ‘tour_name’ LIKE '%”+pTourName+"%’ AND ‘depart_day’ = “+pDepartDay+”;"
ELSEIF pTourName <> “” and pDepartDay = “” and pDestination <> “” Then
sql = “SELECT * FROM tour WHERE ‘tour_name’ LIKE '%”+pTourName+"%’ AND ‘dDestination’ = “+pDestination+”;"
ELSEIF pTourName = “” and pDepartDay <> “” and pDestination <> “” Then
sql = “SELECT * FROM tour WHERE ‘depart_day’ = “+pDepartDay+” AND ‘dDestination’ = “+pDestination+”;”
ELSEIF pTourName <> “” and pDepartDay <> “” and pDestination <> “” Then
sql = “SELECT * FROM tour WHERE ‘tour_name’ LIKE '%”+pTourName+"%’ AND ‘depart_day’ = “+pDepartDay+” AND ‘dDestination’ = “+pDestination+”;"
END IF[/code]

2

try one sql statement:

sql = "SELECT * FROM tour WHERE tour_name LIKE '%" + pTourName + "%' AND  depart_day LIKE '%" + pDepartDay + "%'  AND dDestination LIKE '%" + pDestination + "%'"
3

@Rich Hatfield , you solved it, exactly, thank you.

4

But when the criteria is being supplied by the users, use a prepared statement instead of calling that sql directly. Otherwise you will open yourself up to SQL injection or just errors.

5

@Kem Tekinay , prepared statement I am still learning, but for this project I am not too worry about SQL injection, this is for internal use in the company, not open to the public.

6

I’d still use a prepared statement. If your user searches for something like “O’malley”, they will get an error. Since you will have to sanitize the input anyway, you should let the SQL engine to it for you as it will always be right.

7

“O’malley”??, I haven’t think about that situation, now I understand, let me see if I can convert the whole project to prepared statement.

8

You can use the exact SQL with minor modifications that make it easier to read:

sql = "SELECT * FROM tour WHERE tour_name LIKE '%' || ? || '%' AND  depart_day LIKE '%' || ? || '%'  AND dDestination LIKE '%' || ? || '%'"
dim ps as PreparedSQLStatement = db.Prepare( sql )
// Specify the bind types according to the type of database it is —  see docs
dim rs as RecordSet = ps.SQLSelect( pTourName, pDepartDay, pDestination )
9

why there is || next to the question mark?

10

The question mark is a placeholder for your search criteria, so the first one will stand in for pTourName, the next for pDepartDay, and the last for pDestination. But you want the wildcard surrounding those search terms, so || will concatenate the strings.

For example, if pTourName is “U2”, when it’s all done, it will be exactly as if you had typed “LIKE ‘%’ || ‘U2’ || ‘%’”, and that is exactly as if you had typed “LIKE ‘%U2%’”.

11

wow, all these techniques should be in a book or documents.