Store data in MySQL

Question

I have a form to fill which has several input fileds and 20 checkbox My question is how to organize the records of the selected checkbox in mysql and what is the best way to search for them with php. Can you give me an example?

Answer 1

You could use a single BIGINT field, letting each bit represent one of the property's characterizations.

Let's say you define the properties as follows

 1 = furnished (000001b)
 2 = with garage (000010b)
 4 = new construction (000100b)
 8 = ... (001000b)
16 = ..... (010000b)

So if the field's first bit is 1, that means the property is furnished, if the 3rd bit is 1, then it's a new construction, and so on.

You use the bitwise operators to check for each characterization, i.e.

SELECT * FROM property WHERE characterization & 2

will return the properties with garage. Similarly

SELECT * FROM property WHERE characterization & 5

will return properties that are new constructions and are also furnished (eh.. well, you get the idea :-) ).

Keep in mind that BIGINT is 64bits, so you can't have more than 64 different characterizations in the same field.

Answer 2

If they are not in different groups but many options then you could store them in a string like that |option1:yes|option2:no|.... and you could easily query for different options like WHERE option LIKE '%|option1:yes|%', or something like that.

Answer 3

MySQL is a relational database, so why store all these strings?

What about something like this:

Assume your checkboxes are placed horizontally. So it´s rather intuitive to treat them like this in the database. Let´s say your forms is like:

1. What kind of food do you like (multiple answers possible) ?

   O Pizza O Pasta O Sushi O Salad

Assume someone likes everything but Sushi.Someone else might only like Sushi

So your results table in the database could look like:

id(PK)  user        question     col      answer
1       someone     1             1         1
2       someone     1             2         1
3       someone     1             3         0
4       someone     1             4         1
5       someoneelse 1             3         1

Where answer is some flag that is 1 when the box is checked and is 0 if not.

I am just guessing what you mean like everyone does, so maybe I can give a better answer, if you´d ask a little more precisely.