• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to minimize the search

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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..
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Jeanne
 
So there I was, trapped in the jungle. And at the last minute, I was saved by this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic