Query String problem

[deddleston]

I need to make a query string that is going to be made up from checkbox values. so the more checkboxes checked the more fields will be added to the SQL that will be produced.

How can i do this? How do I get the string to add in extra characters like "," between each field?

 

[sedj]

Could you be a bit more specific - what have you done so far ? Have you already got the checkbox's and actionlisteners set up; how are you returning the values of checkboxes; does each checkbox relate to a certain field or table in the db ?

Ben

 

[deddleston]

Yeah i have set up the page with checkboxes on it. I am passing them across via posting them to the next page. The values all get there but i need to put them together. Yes each checkbox relates to a field.

Daz

 

[sedj]

//OK, as an example, the below would build an SQL statement
//which is :
//"SELECT Field1, Field2 FROM myTable WHERE Field1 = 'Mike'"
//You'll obviously have to tinker about with the mechanics
//of it, but it should give you an idea of how to build up
//an SQL staement using variables set by GUIs

//say you have three fieldname checkboxes in all, and the
//user ticks two of them. This should mean that two
//variables should have something in them, and one shouldn't

//somewhere else you would have to determine what you are
//selecting from which table and so on (say username or
//something)

String username = "Mike" //this var may have been
//determined elsewhere

String tablename = "myTable" // same with table name

//chkbxVar1 will have say "Field1", chkbxVar2 will have
//"Field2" and chkbxVar3 will be NULL / ""

//So to build up your SQL statement, start with the SQL
//statement type - SELECT or UPDATE etc :

String query = "SELECT ";

//add the fields you are selecting to the query
if (chbxVar1 != NULL) {
query = query + chkbxVar1;
}
if (chbxVar2 != NULL) {
query = query + ", " +chkbxVar2;
}
else {
query = query +" ";
}
if (chbxVar3 != NULL) {
query = query +", " + chkbxVar3 +" ";
}
else {
query = query +" ";
}

//add the FROM clause (if is SELECT statment)
query = query +"FROM ";

//add the tablename
query = query + tablename +" ";

//add the WHERE clause
query = query +"WHERE " + chkbxVar1 +" = '" +username +"'";

System.out.println(query);

//Then call your JDBC code etc with the query

 

[toolkit]

Assuming you mean HTML checkboxes - you can assign all your checkboxes to have the same NAME. For example:

<form action=&quot;NKL&quot; method=&quot;GET&quot;>
<pre>
A: <input type=&quot;checkbox&quot; value=&quot;A&quot; name=&quot;cbox&quot;/>
B: <input type=&quot;checkbox&quot; value=&quot;B&quot; name=&quot;cbox&quot;/>
C: <input type=&quot;checkbox&quot; value=&quot;C&quot; name=&quot;cbox&quot;/>
D: <input type=&quot;checkbox&quot; value=&quot;D&quot; name=&quot;cbox&quot;/>
E: <input type=&quot;checkbox&quot; value=&quot;E&quot; name=&quot;cbox&quot;/>
<input type=&quot;submit&quot;/>
</pre>
</form>

And then:

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class NKL extends HttpServlet {

  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException
  {

    String table = "foo";
    String key   = "bar";
    String param = "cbox";

    StringBuffer query = new StringBuffer();

    query.append("SELECT * FROM ");
    query.append(table);
    query.append("WHERE ");
    query.append(key);
    query.append(" IN (");

    String[] params = request.getParameterValues(param);
    for(int i = 0; i < params.length ; i++) {
      query.append(&quot; \&quot;&quot;+params[i]+&quot;\&quot;&quot; )
           .append( i < params.length - 1 ? &quot;, &quot; : &quot; &quot; );
    }

    query.append(&quot;);&quot;);

    PrintWriter out = response.getWriter();
    out.println(query);
    out.flush();
    out.close();
  }
}

Hope this helps, cheers Neil

 

[toolkit]

Note that it should be HttpServlet, and not httpServlet. For some reason the tek-tips parser modified this string...