• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to minimize the search

 
badri nath
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I guess my subject line makes my requirement clear, its easy but can any one suggest good SQL Query.I am using MySql.

To explain in detail, my requirement is something like this..

I have four fields to make the search
1)typeissue 2)priority 3)status 4)issuedate

Requirements:
A)If the user dont enter any thing to make search then all records should be displayed

B)If the user enters typeissue/priority/status/issuedate then search condition should contain one or all the fields respectively.
i.e., If user enters more fields search should be minimized.

I have tried with some thing like this but it is not working fine(my logic is not fine ;) )

StringBuffer sbQuery = new StringBuffer();
sbQuery.append("SELECT * FROM AddDetails ");
if(SelectedIssue != "" || SelectedPriority != "" || SelectedStatus != "" || OpenedOn != "")
{
sbQuery.append("WHERE ");
sbQuery.append("typeissue=? ");
if(typeissue != "")
{
sbQuery.append("AND ");
}else
{
sbQuery.append("OR ");
}
sbQuery.append("priority=? ");

if(priority != "" )
{
sbQuery.append("AND ");
}else
{
sbQuery.append("OR ");
}

sbQuery.append("status=? ");

if(status != "")
{
sbQuery.append("AND ");
}else
{
sbQuery.append("OR ");
}
sbQuery.append("issuedate=? ");
}

/* Prepared Statement followed by this */


It is working fine if immediate fields are not null...but if i try on various conditions it is blunder...

Can any suggest better SQL syntax or good java code.
Thnx in advance..
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Badri,
The "or" part is making things more complicated than necessary and I think is what is causing the logic problem.

Think about what you want the query to look like:
No fields selected: SELECT * FROM AddDetails
Only type issue selected: SELECT * FROM AddDetails where typeissue = ?
First two fields selected: SELECT * FROM AddDetails where typeissue = ? and priority = ?
All fields selected: SELECT * FROM AddDetails where typeissue = ? and priority = ? and status = ? and issuedate = ?

So the idea would be something like:

[ September 08, 2006: Message edited by: Jeanne Boyarsky ]
 
badri nath
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic