Multiple Checkbox Options Stored Into A Single Field In A Database

I'm using HTML in a .jsp and Java in a servlet to process the jsp. I have a form for Skills, with 12 checkbox options to choose from. Obviously more than one can be selected, and I

Solution 1:

As Jrod pointed out using the LIKE keyword will likely satisfy your needs.

However on a side note there are other ways that might be better in the long run.

1-N relationship

If you have your skills in a separate table you can then connect them in a one to many relationship, that would mean you could query by skill and then join the relevant people to the query. This would enable you to have more complex queries such as being able to search for more than one matching skill or conditional matching skill but not another skill.

Bit flags

As you say they are checkboxes I’m assuming your skills are limited (by the number of checkboxes) another approach is to use an integer an use bit flags – for example:

• Java = 1
• C++ = 2
• Lisp` = 4
• haskell = 8
• Python = 16

Adding up the accumulative value from all your checkboxes and storing this as an integer would allow you to query the database using a bitwise AND (&)

Solution 2:

Try using the LIKE operator instead of the = in your prepared statement

PreparedStatementstatement= con.prepareStatement ("SELECT * FROM servlet WHERE skill LIKE '%" + Skills + "%'AND year ='"+ Years+"'");

To select based on two skills, you can chain like statements:

PreparedStatementstatement= con.prepareStatement ("SELECT * FROM servlet WHERE skill LIKE '%" + Skill1 + "%'AND skill LIKE '%" + Skill2 + "%'AND year ='"+ Years+"'");

Solution 3:

you could use

 StringBuffer sql = new StringBuffer();
String skills[]=req.getParameterValues("skills");
sql.append("select * from skillRelation where ");

for(int i=0;i<skills.length;){
  sql.append(" skill like '%").append(skills[i]).append("%'");
  if(++i < skills.length) {
    sql.append(" and ");
  }
}