I've been working on a system where one of the requirements is the ability to perform simple searches based on filling out the parts of a form that are known. Basically what most Web sites would classify as "Advanced Search."
At first this struck me as a problem that had likely been solved once or twice in a really compelling way, like how Markdown and Textile have solved the problem of text-to-HTML translation.
On the other hand nearly every Web site's full-text search has a slightly different query system, so perhaps this isn't a completely solved problem. Also, full-text search isn't exactly what I had in mind: I needed a more structured system. And sending in straight or filtered SQL is just too much of a security risk (in a perfect world, this wouldn't be a problem, but we're talking PHP on Oracle here).
Eventually I settled on the following solution:
- Fields left blank don't affect the query
- Fields whose value isn't one of the "special values" below must be a full, exact, and case-sensitive match (SQL
- Fields whose value begins with a tilde (~) create a case-insensitive
like clause (
% matches one or more characters, and
_ matches exactly one character)
- Fields that begin with a greater-than or less-than symbol constrain the value accordingly
- Fields that are a single quote are constrained to be null
- Any of these constraints (except a blank field) can be negated by prepending an exclamation mark (!)
It seems to do the trick, though it's far from perfect. Two obvious shortcomings are that it doesn't support "between" clauses, or indeed multiple constraints on any one column, and that there is currently no way to escape fields that actually start with a !, ', >, < or ~ (though this could easily be fixed with some sort of escape character). It's also perhaps a bit much to expect a non-technical person to grasp.
So first off, does anyone know of some other widely-deployed system that solves this problem? Secondly, are there any shortcomings of the above system (for the purpose of structured search of a table) that I haven't noticed?