Ad:

MySQL Discussion Thread
Marked Solved
Views: 682

Nov 2nd, 2009

SQL statement for easier db a search

Expand Post »

Hi.
I have a web application whereby I perform a search through 4 fields, namely:book serial no.,tel no.,gender and publishing date.In my jsp page variables picking the values respectively are:serial,tel,gender and datez.These are submitted to the java class.Currently, I perform my searches as follows(in java class):
/***********************************************/

Java Syntax (Toggle Plain Text)
//......after connecting to db and all that
 
 
if(serial.equals("") )
 {
 if(!tel.equals("") )//
 {
    query = "SELECT * FROM tbooks WHERE tbooks.tel=' "+tel+" ' ";
 }
  else if(gender.equals(""))
 {
   query = "SELECT * FROM tbooks WHERE tbooks.gender=' "+gender+" ' ";
 }
      }//end if
else if (!serial.equals(""))
{
query = "SELECT * FROM tbooks WHERE tbooks.serial=' "+serial+" ' ";
}
 
//query is the statement to be executed later as  
//ResultSet rs = statement.executeQuery(query);
// more code goes here.......
//......after connecting to db and all that


if(serial.equals("") )
 {
 if(!tel.equals("") )//
 {
    query = "SELECT * FROM tbooks WHERE tbooks.tel=' "+tel+" ' ";
 }
  else if(gender.equals(""))
 {
   query = "SELECT * FROM tbooks WHERE tbooks.gender=' "+gender+" ' ";
 }
      }//end if
else if (!serial.equals(""))
{
query = "SELECT * FROM tbooks WHERE tbooks.serial=' "+serial+" ' ";
}

//query is the statement to be executed later as  
//ResultSet rs = statement.executeQuery(query);
// more code goes here.......

/********************************************/
The main question here is, Is there an SQL statement that works well in place of my nested if statements? (i.e if serial no is present ,tel is absent, gender is present and date is absent, then it brings results considering the present values only)....Kind of bringing results of ..

sql Syntax (Toggle Plain Text)
SELECT * FROM tbooks WHERE SERIAL = ' "+serial+" ' AND tel= ' "+tel+" ' AND gender= ' "+gender+" ' AND datez= ' "+datez+" '
SELECT * FROM tbooks WHERE serial = ' "+serial+" ' AND tel= ' "+tel+" ' AND gender= ' "+gender+" ' AND datez= ' "+datez+" '

....even if one variable is null it considers only the non-empty variables.Thank you so much in advance.

Last edited by peter_budo; Nov 2nd, 2009 at 9:42 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

Similar Threads

SQL statement help! in Visual Basic 4 / 5 / 6
PHP variable in SQL statement in PHP
is there something wrong with my SQL statement? in PHP
Question about SQL statement in MS SQL
SQL statement error in ASP.NET

dangari

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

73 posts
since Aug 2009

Permalink

Nov 3rd, 2009

Re: SQL statement for easier db a search

Hey.

How about something like:

java Syntax (Toggle Plain Text)
// Create a list object for the conditions, and the query string.
List conditions = new List();
String query = "SELECT * FROM tbooks";
 
// Add each condition to the list if it is present.
if(!serial.equals("")) {
    conditions.add("serial='" + serial +"'");
}
if(!tel.equals("")) {
    conditions.add("tel='" + tel +"'");
}
if(!gender.equals("")) {
    conditions.add("gender='" + gender +"'");
}
if(!date.equals("")) {
    conditions.add("date='" + date +"'");
}
 
// If any conditions were present, compile a WHERE clause.
if(conditions.size() > 0) {
    query += " WHERE ";
    Iterator i = conditions.iterator();
    int cLength = conditions.size();
    while(i.hasNext())
    {
        query += i.next().toString();
        if(cLength > 1) { // We want to skip the last loop, so > 1
            query += " AND ";
        }
        --cLength;
    }
}
 
// And you should have your query.
// Create a list object for the conditions, and the query string.
List conditions = new List();
String query = "SELECT * FROM tbooks";

// Add each condition to the list if it is present.
if(!serial.equals("")) {
    conditions.add("serial='" + serial +"'");
}
if(!tel.equals("")) {
    conditions.add("tel='" + tel +"'");
}
if(!gender.equals("")) {
    conditions.add("gender='" + gender +"'");
}
if(!date.equals("")) {
    conditions.add("date='" + date +"'");
}

// If any conditions were present, compile a WHERE clause.
if(conditions.size() > 0) {
    query += " WHERE ";
    Iterator i = conditions.iterator();
    int cLength = conditions.size();
    while(i.hasNext())
    {
        query += i.next().toString();
        if(cLength > 1) { // We want to skip the last loop, so > 1
            query += " AND ";
        }
        --cLength;
    }
}

// And you should have your query.

My Java is a bit rusty, but you get the idea.

Is that what you are trying to do?

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Nov 3rd, 2009

Re: SQL statement for easier db a search

Atli!You are the kind of people that I personally refer to as programming genious.This has to be the best idea that I have seen since I joined Daniweb.Fantastic stuff!!

dangari

Reputation Points: 10

Solved Threads: 3

Junior Poster in Training

Offline

73 posts
since Aug 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: SET datatype

Next Thread in MySQL Forum Timeline: What does mysql return when the selection doesnt exist?

DaniWeb Message

Cancel Changes

User Name
Password