This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JSP and the fly likes Multi option HTML search form Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Multi option HTML search form" Watch "Multi option HTML search form" New topic
Author

Multi option HTML search form

matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I'm trying to put together a form that allows a user to search a table with multiple fields e.g. searching either forename or surname or both at the same time. I want it to be able to search the database according to whatever is entered into the fields without conflicting if only some of the fields have input. How can i do this? The search form has six fields but the actual table has more fields.

Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
Create a <HTML:OPTION> configure a reloadMethod to reload the page, invoking the action, to populate the option field, that will forward to the same page, but with new contents in the form.
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
How would i do that?
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
matthew irving wrote:How would i do that?


Use the onChange, from the HTML:option tag, to invoke a javascript that will invoke you action... You will need a method reload,
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I still don't understand. Why would I reload the page with an option tag?
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
matthew irving wrote:I still don't understand. Why would I reload the page with an option tag?


In order to populate the other OPTION box, with the olnly allowed options.

Would be something like a form with STATE and CITY, to show only New York state cities, when the NY is selected.

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

I'm still not sure what you are trying to accomplish.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I think you have the wrong idea here. I want the user to be able to enter values in any number of the text boxes and query for the result so any of them could have text in them or just one have text.
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
oh.. got it...

which framework are you using for DB?

but for starter you can have if clauses filtering to pass only valid fields to query

if case field and 2 are null you will query only for fields 3, 4 and 5
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
Framework? I'm using jsp, html and sql.
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
matthew irving wrote:Framework? I'm using jsp, html and sql.


ok. if you were using framework this would be easier,

do something like this


if field1 is null
query without field 1
if filed 2 is null
query with field 1 3 4 5
if field 1 and field2 are null
query with only field 3, 4, 5

and so on...
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
So do i use a new prepared statement for each if statement?
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
Is there anyway to shorten the number of If statements?
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I think the code below would work but i can't figure out how to get the st.executeQuery() to recognise which case to display.

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

I'm still not sure what it is you are trying to do. Is this a JSP or JDBC question?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

It looks to me like it's a JDBC problem: "How do I create an SQL statement to query when only some of the possible parameters are input?" but it's getting confused because it has to be put into a JSP.

I would suggest putting the JSP aside for a while and writing a plain old Java command-line application for the purpose of developing the JDBC code. (And then I'd put that code into a proper modern JEE environment instead of a scriptlet in a JSP, but that's a separate issue.)

So it's a design problem. Focus on the SQL and nothing else. Generating some examples would be useful: for example if you only have data for casenumber and membername, what does your SQL look like? What if you have everything but caseworker? What if you don't have anything?
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I have six fields. Any of the fields can have a search term in them. I want the code to be able to understand how to handle a query where some of the search terms can be null.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

In that case, when you create the query, leave out any terms for params that haven't been supplied. If it's not that simple. what am I missing?

And yeah, doing this in a JSP is like digging yourself a hole that will collapse in you at any moment. Ditch the obsolete scriptlets and do Java in Java classes where it belongs.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Okay. I always find that examples are useful to help me figure out what I should do. That's why I suggested the examples.
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
Would this sql work?

SELECT * FROM cases where CaseNumber=? OR MemberNumber=? OR MemberName=? OR Caseworker=? OR Descriptor=? OR Problem=?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

Leave off any that don't have params.

And do you want any to match (or), all all to match (and)?
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I want any that HAVE matches to match and any null to not be searched against.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

Bear Bibeault wrote:Leave off any that don't have params.

matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
Would the statement eliminate the need for a large number of if statements?
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
you can also work with stringbuilder

checking if parameter is null or not, and adding if necessary, you will have arround 10 if clauses instead of over 20, but using the stringBuilder you have to have a very well coded to build the parameters into the Select. also make sure all AND are add only when necessary...


matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
Can you please provide an example?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

What's the issue with if's? When you have conditionals, you need to use if statements (or ternary expressions). I think you are tilting at windmills here.

Using StringBuilder won't let you eliminate the conditionals; it just gives you a (maybe) more efficient way to build the SQL expression.
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
int fieldcount =0; --> moved to the code tag

fixed as per Bear suggestion removed quotes


and so on... Make sure you cover all the cases to add the AND

Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

That SQL syntax is incorrect. The question marks should not be quoted.
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I think i have it.

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Yes, that's looking like a good start. I think you'll find it isn't quite right, but it's not bad. I would suggest appending "AND" only if (a) you're about to append some condition and (b) you already appended one or more conditions. And don't forget to leave whitespace or your SQL won't come out right. A little bit of testing will clear that sort of thing up, though.
Rafael Prado Oliveira
Greenhorn

Joined: Apr 30, 2013
Posts: 28
you have to add some parenthsis, in the if condition that filters for the AND, its some conditions AND next parameter
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
you have to add some parenthsis, in the if condition that filters for the AND, its some conditions AND next parameter


I'm sorry I don't fully understand what you mean could you clarify please?
matthew irving
Ranch Hand

Joined: Apr 28, 2013
Posts: 38
I almost have it but it won't execute the string.


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Multi option HTML search form
 
Similar Threads
Retaining values on a search form after submission
Struts 1.3: A good way to save the latest search parameters
Parsing files and text box values
Search best way
Regarding Multiple fields form search page