creating a SQL query based on user search criteria

[Erikxxx]

Hi,
I'm building a simple form in HTML for users to use to query the database (ora 9i).The initial version of the HTML form has got 4 input fields.

City (default value is all cities)
Category (such as OLD or NEW vehicles. Default value is BOTH)
minimum_price (default value is no preference)
max_price (default value is no preference)

The problem I have comes to generating the query string for the user input.
I have a PL/SQL procedure that take all values from my HTML form but I just can't get the logic in
my procedure to work in terms of generating the WHERE clause and the 'AND' operators. I'm always ending up with an 'AND' operator too much. They way I'm trying to write my sql query is:

If the user only choose to select a particular city and leaving the other options intact. The SQL query should be something like
SELECT * FROM view_searchresult WHERE city = in_city;

On the other hand if the user choose to run a query where he/she searches on a particular City and Category the SQL query should be written like this
SELECT * FROM view_serachresult WHERE city = in_city AND category = in_category;

The way Im ending up writing my code seem to be very complicated and probably also inefficient.

Any help would be much appreciated

Erik

 

[cheguvera]

Hi,

I did something similiar long ago, I will try to describe.

One basic assumption was, users will use the wildcard '%' as we use it in like-clause in SQL, in their search string if required.
Another assumption was, default relation between fields was AND. Means, if user inputs 2 out 4 text fields, it means he want to search for criterian field1 AND field2. If 3 fields he enter, means, field1 AND field2 AND field3.
Use can input data in any case, but our data stored was in all capitals, so I will convert all user inout to upper case, before constructing my query.

Then comes my simple logic,

where_clause_string = "" (nothing initially)

if ( field1 is NOT NULL, after removing all spaces )
if ( field1 has % sign in it )
then
where_clause_string = where_clause_string || "field1 like " || single_quote || field1 || single_quote || (one space)
else
where_clause_string = where_clause_string || "field1 = " || single_quote || field1 || single_quote || (one space)
end if

where_clause_string = where_clause_string || " AND "

end if

After this, repeat the above if-statement for field2, 3 and 4. Also, append one " AND " to the where-clause after field2 and 4, not after field4.

Can this solve your purpose??

Regards

 

[Erikxxx]

Hi Che,

Thanks for your code. A few questions though.

1. I don't understand why you have a where_clause_string before the last END IF statement in your code. This statement would duplicate the data you alread have added in your inner IF statement. Correct me if I'm wrong here.

2. If the user only choose to provide data in field1 (all other fields NULL), only the IF statement for field1 will be executed. Since the last line in the IF statement adds an "AND" to the where_clause_string wouldn't this fail the logic?

Thanks for your time

Erik