input unknown number of fields into mysql - best structure for this?

Question

I need to store data that has been sent from multiple forms - the fun bit is, as these forms will be created by a form builder I am planning on giving users access to, I have no idea how many fields will be in the form, or how many fields the user will be submitting. I have thought about a table structure similar to the following (the form itself is saved as php for include):

| FormId | FormType | InputID | InputType | LongText | Text | Date |

FormId is relevant to another table storing location of the form file etc for include (e.g below), Input ID is relevant to the input on the page.

| FormId | FormType | PathToForm | DateCompleted |...etc

input "a" from formId 1 might look like:

<input id="a" name="a" type="text" value="example"/>

whereas input "a" from formId 2 might look like:

<textarea name="a" id="a"></textarea>

Which I can write into a table like this:

| FormId | FormType | InputID | InputType | LongText |   Text  | Date |
--------------------------------------------------------------------------
|   1    |     1    |    a    |    text   |   NULL   | example | NULL |
|   2    |     2    |    a    |  textarea |  example |   NULL  | NULL |

The number of "inputs" on each form is potentially unlimited - the inputs on different form templates may be of different "type" each time.

My question (finally!) - is this the best way I can be doing this? I know storing output of a form, per line, isn't exactly ideal - however without having hundreds of tables and, without loosing the possibility of pulling the data usefully should I ever need to in the future (by "FormType") I'm not sure of any other way to structure. Not really come across anything like this before, sorry if it sounds silly!

One such product we work with is Gravity Forms. You might want to look at their data structures :) — Daan Meijer
– Daan Meijer, Commented Jan 10, 2017 at 13:11

Community · Accepted Answer · 2017-05-23 12:13:58Z

0

There is something in mysql 5.7 tailor made for this situation. The JSON type.

What you can do is convert your form to an associative array and then save that in the JSON field.

There are a few caveats though, this field though searchable isn't as efficient at searches as would a normal varchar column. Additionally if you want to use it in a join you are in deep trouble.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Jan 10, 2017 at 13:14

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

G Sim Over a year ago

That looks like just the ticket - looks like I can scrap one my tables too! Noted about the search - simple enough to keep fields I want to be searchable separate for now. Now all I need to do is wait for my shared hosting to update from 5.6 to 5.7!

e4c5 Over a year ago

oh you can switch to a company that provides 5.7 :-) anyway, all the best with your project

Collectives™ on Stack Overflow

input unknown number of fields into mysql - best structure for this?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related