Hello ranchers. I have created an application that logs incidents to a database. I now need to provide the user with an advanced search form for searching these incidents by many optional fields. I'd like to implement something that looks similar to google's advanced search form where the user can search by any number of optional parameters. My solution is to create a prepared statement and set any non-selected fields (which will denote that they don't care about this so I should somehow say ANY of these fields) to the sql wildcard value of '%'. This way I can use one prepared statement for any possible combination of the fields the user elects to search by. So for example if I narrow down to two fields (there will actually be many more):
either way it doesn't matter and the same prepared statement can handle both. My Question: Is this the best approach or is it just a hack? This seems to be a boiler plate feature so I'd like to hear from others that have tackled this. Thanks! [ February 28, 2008: Message edited by: nico dotti ]
Nico, While that would work, it would be more efficient to leave the columns out of the query if they aren't being searched by.
This results in a different number of "?" for different queries. This is ok because you will have repetition with a large number of queries. The driver and database are smart enough to realize they have already prepared that particular SQL statement.
So would I have to dynamically build the WHERE clause depending on the user choices for search fields? Given that I have about a dozen fields that match up to columns that would be a lot of combinations if I don't do it dynamically. LMK what approach you're suggesting. Thanks.
Nico, Yes. That is what I am suggesting. You don't have to create all the SQL strings though. You can build up just the one that is needed at that moment. The driver will take care of recognizing duplicates for you.
I guess that doesn't mean doing it dynamically. My point was to keep using the "?" even though you are building the SQL upon request.
Also, please try to avoid the internet acronyms and UseRealWords. They are hard on native English speakers and people using screen readers. I had to lookup LMK (let me know) and I am an native English speaker.
Sorry, I won't use those abbreviations anymore. Also, thank you for giving my question your time.
I deeply apologize if I'm just being an idiot, but feel as though I must not be making myself clear on my goal because in order to do this the way I believe you're suggesting I WOULD have to do it dynamically (if I follow your suggestion of only using fields that the user actually chooses to search by to improve efficiency).
Notice that the user can input whatever fields they want (one, a few, many, all, it doesn't matter to in respect to what google is requiring of the user). So I have something similar where I have about a dozen fields and I don't want to restrict the user on which combinations of fields they use.
As far as I know, I have to provide the column names and the values can be dynamic using the IN parameters like: name=?, age=?, etc., but I can't do: ?=?, ?=?. I'm correct on this aren't I? This is from looking at several examples and having always done it this way myself. However, if I'm wrong and there's an easy way to do this that would be great.
So given my problem statement how would I deal with the multiple combinations of 'chosen' fields by the user without doing this dynamically? (You mentioned in your last reply that I wouldn't need to do this dynamically so that's why I ask).
So for example with only three fields WITHOUT doing it dynamically I'm conceiving that I'd have to go through this mess:
So for 10 fields it's going to get ridiculous unless I do it dynamically, or use my first idea of having one PreparedStatement and setting empty fields to '%' so I could just do: col1=? AND col2=? AND col3=?.
However, I agree with you that this would not be the most efficient way, so I am quite keen to find another solution. I have already done a lot of dynamic building of sql so it doesn't bother me to do so if I have to but I'm just wondering if there's a better way before I commit to that solution. Thanks for your time and again I apologize again if I'm not getting what you're trying to convey to me, but I still don't quite have the answer I'm looking for and I'm going to start coding this tomorrow.
Well thanks for your input. I would have loved for there to be something in the PreparedStatement API to do setEmpty or setNone but of course that would be making it too easy. They do have setNull which has come in handy but doesn't help me here because then it would just look for NULLs.