Creating dynamic forms with MySQL

Yii 1.1 General Discussion
1

Hi folks,

I am currently building a newsletter management system with templates. In a nutshell each template can have different form fields (that will be loaded dynamically when you select one). The issue is that MySQL does not allow to store fields dynamically in a table.

So I was thinking about having a table structure like that:

template

–id

–name

–data

–extraFields

Where extraFields will store a serialized array with the following structure:


 array ('fieldname1' => array('CHtmlType' => 'Checkbox', 

    'lablel' => 'labelName',

    'htmlOptions'> array(),

    ''));

Same for the data field, it will store a serialized array like:


array('fieldname1' => 'data')

So when you request a template it will grab the extraFields column and build and add fields dynamically to the form, not sure yet how this will work (for validation and so in the CActiveRecord object)

What do you think of that idea? Do you have any other approach that you would recommend in my case?

Thanks :)

2

I am not sure I get this exactly, but I am not much of a fan in storing arrays inside database columns. Maybe in very large systems you would have to.

In your occasion I think I would try to have a table which contains in every row (each row has an id) the characteristics of a field. And for the specific form, I would have in a column, stored all the ids which correspond to that form.

How do you think of that? :)

3

Hey pligor,

Thanks for your answer.

So just to be sure I fully understood what you mean. In my case you will have the following structure:

table extraFields

->id

->name

->CHtmlType

->etc

table template

->id

->name

->extraFields

And storing all the specific ids that belong to the template in the extraFields (like separated by commas?)

It does not look like a bad idea to be honnest :-), it is just that I am not a huge fan of storing (either arrays or data separated by comma), but with MySQL I do not think we have a choice and I am definitely not going to install couchDB just for that :)

The only issue I can see right now with your solution is that I will struggle to validate data (one more row for each validation attribute?)

Cheers

4

anyone else having ideas? :slight_smile:

5

use serialize function to add the array with options to the database in some field, for example dataField

the revert proccess is to unserialize the variable so you can use it normally

6

yeah thats what I am currently doing.

I created a Form model for each email template (I finally found that I will not add new template that often) and I am storing the serialised array in a DB field.