vendredi 20 novembre 2015

How to structure MySQL db for storing multiple checkbox form data and later do statistics in php?

I am working on a form for storing information about the themes of the queries we receive in our company.

I am using php/MySQL to store radio button data such as:

  • Name of employee
  • Medium of query (e-mail/phone/in person)

With radio button data such as this, I can easily use PHP to query the database and e.g. count the numbers of queries answered by e-mail by each employee.

The trouble I am having is with data regarding the theme of the query. This is checkbox data with a lot of different checkboxes (50+). We would like to be able to add or remove checkboxes from time to time, though not very often.

I used to store this data just as comma-separated values in a single cell in the database and then export to excel to work with the data, but now I'd like to use another PHP form to generate statistics on the themes.

My research has lead me two to ways of doing this, that may be possible:

  1. Creating a separate table in my database for my themes with one column for each possible theme, so that I'd have as many columns in my database as the number of checkboxes in my form.
  2. Use the php functions serialize to store the checkbox data in one cell in my database and then later using unserialize to work with the data in php.

I am an absolute beginner, so with both options I'm unsure how I'd actually implement it:

  1. With this option I am unsure whether my MySQL columns should just be "theme 1", "theme 2", etc., or whether they should have the names of my checkbox values, e.g. "money", "personal problems", "practical issues", etc. I have not been able to find a good resource on how to store the checkbox data in the right way, when the user may sometimes have clicked just 1 theme, and in other instances may have clicked 10 themes.
  2. With this option I am unsure how I could populate a dropdown with unique values, and how I could later count instances of a unique value across the rows in my database.

Any help you can give me on this, including links to tutorials or questions similar to this on stackoverflow, would be much appreciated. I haven't been able to find anything about this that I could understand, other than I am thinking option 1 is probably the right way to go.




Aucun commentaire:

Enregistrer un commentaire