Store dynamic form fields in database

Question

Hello. I have read other answers on this (or at least near to this) subject but I couldn't get a clear view of it so I'm asking for help again.

I have a complex dynamic HTML form that I would like to submit to database using PHP. The form is split into multiple tabs and in each tab I got checkboxes that trigger other parts of the form. Example: at a point in my form I got a checkbox group that has options of: "hotel" and "restaurant". If I check hotels, I get another part of the form displayed, specific for "hotels". Same thing for "restaurant". So it's very dynamic here and I don't know which would be the best approach for storing every form field in database. Because it could contain 15 fields or 20, depending on the selection. Any example would be appreciated as I'm not that advanced with database design.

Thank you!

Answer 1

You didn't specify how you can manage this dynamic form. Can you edit it's PHP/HTML source? One great thing would be if you can label your different variables like hotel[], restaurant[], etc.

If your submitted form is clear enough (i mean semantically correctly structured) you can store the whole submitted form serialized.

Note: this method only working when you don't need to search for specific items in your database.

Edit: maybe i'm misunderstood your problem.

You can create a 'metadata' table like this:

form_id  |  option_name  | option_value
---------------------------------------
1        |  hotel        | true
1        |  restaurant   | false

Answer 2

So it's very dynamic here and I don't know which would be the best approach for storing every form field in database.

I apologise if I have misunderstood you here but I believe that you should design the database according to the data and not the form. It is difficult to comment without knowing the exact details of your situation so here is an example:

If you usually dump all the data from a form into a single table, but because sometimes this will involve submitting 5 values and other times this will involve submitting 10 and so you are unsure how many columns your table should have, then I think the problem is in the database design.

Work out what pieces of data are dependent on other pieces of data. For example, you mention checking "hotel" might open up more fields specific to that choice. Let's assume this involves things like "en-suite", "bed type" etc. Then you should have 3 tables, a registration table (assuming the user is using the form to buy these services), a hotel table and a registration_hotel table. The registration table will record a number of details specific to the registration only such as the customer's name and a unique id number. The hotel table will hold information specific to the hotel only, such as how many rooms have en-suite. The registration_hotel table will hold details specific to that registration at that hotel. You might want a column of type bool to record whether the user requested "en-suite".

When submitting the form, check which pieces the user entered with if(isset($_POST['hotel']) && !empty($_POST['hotel'])). Then only send stuff to the registration_hotel table if that condition is true.

If this design results in making too many separate calls to the database, you might want to look into transactions which will help you to manage the speed and security of these calls.

If you can post in a specific example of something you don't know how to do, that would be useful.