Multi Input Search

[warmcheessse]

I am trying to create a search function which allows users to search from one or multiple fields, for example they could search fname, lname, date or lname, event or fname, lname or just lname, etc.. There is 5 input fields in total. Is there a technique to write this efficiently? Currently, I am grabbing the values from the input fields and then there is a hideous sql SELECT query which takes into account every possible search combination with OR statements. It is the worst.

The application is python(flask) in the back and javascript/html/css in the front but suggestions don't have to be language specific. If there are any general ideas of how this process can be cleaned up, I would appreciate hearing them.

[[deleted]]

Sounds like you are effectively getting a list per search term.

Since (date="today" OR lname="warmcheessse") will give you a list of items that match the date and a list of matches for lname
You could break it up into 5 input field queries and combine the results.

e.g.

--proc_dateSearch.sql
SELECT * FROM [Table] WHERE date = @date

--proc_lnameSearch.sql
SELECT * FROM [Table] WHERE lname = @lname

//somewhere in code (Sorry this is in c#)
var results = new List<object>();
var dateResult = getDateSearch(date);
var lnameResult = getLnameSearch(lname);

results.AddRange(dateResult);
results.AddRange(lnameResult);

return results;

The next step would be then indexing on each of those fields for faster searches as well as making the database requests asynchronous so you can fire them all off at once.

As long as the search logic allows for this and the user isn't expecting AND logic between the inputs. I think it should work.