attach a CFv5 form to a mySQL table

Hi Guys

I created a multipage form that essentially consists of 240 multiple choice questions. When I tried to create a table for this form from within CF, the tool default all the question inputs to VARCHAR with length of 255 with utf8 formatting. As you can imagine, this exceeds the maximum row width permitted by MySQL. The obvious solution is to change VARCHAR to TINYINT or SMALLINT, but come on, doing that by hand for 240 times! So, I managed to extract the error message and the MySQL statement (which truncates the question array from 240 down to 110), modified the MySQL. And I plan to create the table from phpMyAdmin. But it's not clear to me how to attach the form to this table from the form manager. I guess I would have to create some php code and then install the php code at Setup, maybe at onLoad? Any advice?

Thank you for your help.

SH

Hi studiocardo,

There's nothing special about the tables that ChronoForms creates - if you create a table using PHPMyAdmin in the same database then ChronoForms will see it and connect to it. The only real requirement is that the table has a numeric primary key set - a normal id column (though it's better not to call it id, I use cf_id instead).

Bob

PS I would also suggest that you think about not having a table with 240 columns but use two linked tables: one with the submission info and the second with the answers each in a separate record. Both methods will work, the second can prove easier to use and modify over time.

I forgot about the DB Save Event… Now it's obvious.

Is there any example on the Event JS Client Validation? I assume example validation JS code has already been written and all I need to do is to specify the conditions? Any example on the syntax is much appreciated.

SH

Hi studiocardo,

What do you need to validate? There are a bunch of standard validations available on the elements in the Preview tab that you can utilise by adding classes to you your custom HTML. If you create a temporary form using the validation you need you can check the form HTML to see the classes. It will look something like this

<input name="email" id="email" value="" placeholder="" maxlength="" size="30" class="validate['required','email'] form-control A" title="" style="" data-inputmask="" data-load-state="" data-tooltip="" type="text" readonly="readonly">

and you need this class: validate['required','email']

Bob

Bob

Hi Bob

Back to the original issue of binding the form to a table. It turned out, according to my developer, that CF rejects '[' and ']' in the name field (see example below), although the markup passed the HTML5 validator. How else do you assign a bunch of inputs into arrays? Did my developer make the valid claim? If yes, then could you advise how the names should be marked?

Thx,
SH

<div class="uk-form-row">
<label class="uk-form-label" >22. xxxxxxxxxxxxxxxxxxxx:</label>
<div class="uk-form-controls uk-form-controls-text">
<input type="radio" id="Q[22][0]" name="Q[22]" value="0" > <label for="Q[22][0]"> 0</label>
<input type="radio" id="Q[22][1]" name="Q[22]" value="1" > <label for="Q[22][1]"> 1</label>
<input type="radio" id="Q[22][2]" name="Q[22]" value="2" > <label for="Q[22][2]"> 2</label>
<input type="radio" id="Q[22][3]" name="Q[22]" value="3" > <label for="Q[22][3]"> 3</label>
</div>
</div>

Hi SH,

As far as I know ChronoForms is OK with [] in input names. They are required for multi-choice inputs.

BUT

+ I'm pretty certain that HTML does not permit [] in IDs so they should be something like id="Q_22_0",
+ And MySQL won't accept [] in column names - though presumably your column name is something like Q ??

What exactly is the problem that you are seeing?

Bob

Hi Bob

Yep and yep. I think what happened is that CF tried to create columns with the field names that contain [] and barfed. So by avoiding creating the table with column names that contain [], the problem is avoided. It's silly to replicate table column per field names verbatim… So I created an associative array and implode them into a string, which is then shoved into a regular column of text type… It's just some times very confusing trying to communicate w/ developers…

Thx,
SH

HI SH,

That should work fine. I've also used json_encode() to crunch up whole datasets to save into a TEXT column as form backup, that works very well.

Bob

The disadvantage of putting everything in one record is that you can't use SQL functions anymore.
With a large table from questionnaire you need SQL to access the data.

Making a Table is very easy.

Just make a text file with the SQL statement en put that in phpMyAdmin or any SQL admin program and run it.

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

If the result is not what you expected, just delete the table, change de code and run it again.

Rob