CODEWITHSUNDEEP
javahtmlmysql
louise
louise

Reputation: 61

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 want these then sent to a database. (As an example, Adam can have Java and C++ and Bob just has Java).

I want only one field in my database to contain the information for all of the checkboxes. At the moment, I have the skills going in as one string with " " separating them, but then when I try to retrieve the info, I don't get the right info back. For example, if I search for any employee with "Java" I get back Bob's details but not Adam's, because it's part of a string "Java C++" instead of being separate.

Do you have any suggestions on how to have multiple checkbox options stored into a single field in a database, but can be separated for easy retrieval?

Upvotes: 2

Views: 3289

Answers (3)

Delta
Delta

Reputation: 547

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 ");
  }
}

Upvotes: 0

Jared
Jared

Reputation: 12524

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

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

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

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

Upvotes: 1

Steve
Steve

Reputation: 3703

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 (&)

Upvotes: 2

Related Questions