Using Jquery Formbuilder

2 Jul 2020

, ,


jQuery formBuilder is a lovely stylish formbuilding tool, with all the front-end features a developer could desire. But the documentation is a little short of examples for integration with a backend system + database, and the gitter chat is completely full of people struggling with the basics.

I’m writing down some integration notes to keep it clear for myself in my head.

I’ve tried to keep this code as simple and as framework agnostic as I can.

Example code = dirty code. Do not use in real-life.

Database Structure

Each form gets its own table.

CREATE TABLE `forms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I take the json returned from the formbuilder and split it down into form fields. This makes it easier to match up the form fields and user input.

CREATE TABLE `formfields` (
  `key` varchar(100),
  `formId` int(11) DEFAULT NULL,
  `label` varchar(200) DEFAULT NULL,
  `sortorder` smallint(6) NOT NULL DEFAULT 0,
  `json` text DEFAULT NULL,
  PRIMARY KEY (`key`,`formId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Many people are going to be filling in these forms online. Each completed form gets its own record, and any form-specific data (name, date completed, user ID etc) can be added as a field.

CREATE TABLE `formcompleted` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `formId` int(11) DEFAULT NULL,
  `dateCreated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY (`formId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is the input from the the completed form – in this example each field gets saved as a separate record.

CREATE TABLE `formcompletedfields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `formCompletedId` int(11) DEFAULT NULL,
  `key` varchar(100),
  `value` text,
  PRIMARY KEY (`id`),
  KEY (`formCompletedId`),
  KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1) Create form – save to database

Here’s our form. Basic formbuilder integration.

When the user clicks the Save button, the script grabs the form data (formatted as json) and saves it into a hidden field on the form before submitting the form. It’s a quick and easy way to get the formbuilder gubbins PLUS any other fields which you may wish the user to set while they are making their forms

<form accept-charset="utf-8" autocomplete="off" method="post" action="/formbuilder/create" class="form-horizontal">
  <label>Form Name</label>
  <input type="text" name="formname" value="">

  <div id="formbuilder"></div>

  <button id="btnSave" type="button" class="btn btn-success"> Save</button>

  <input type="hidden" name="json" id="json" />
</form>

<script src="/resources/plugins/form-builder/form-builder.min.js"></script>
<script src="/resources/plugins/jquery-ui-sortable/jquery-ui.min.js"></script>

<script>
    var options = {
      dataType:'json'
    };
    var formBuilder = $('#formbuilder').formBuilder(options);

    $('#btnSave').click(function() {
        var json = formBuilder.actions.getData('json');
        $('#json').val(json);
        $(this).closest('form').submit();
    });
</script>

Now, it is possible to save the form as one record, but I’ve found it makes my life easier if I split the form up into separate fields, and save them as individual records.

I am using the field ‘name’ as the record key

Here’s the sample backend PHP code to take the data submitted as a form and save it to the database.

<?php 

function saveFormBuilder() {
    $query = $pdo->prepare("INSERT into forms (name,json) values (:name,:json)");
    $query->execute([
        'name' => $_POST['name'],
    ]);
    $formId = $pdo->lastInsertId();

    $jsonfields = json_decode($_POST['json'],true) ;
    foreach ($jsonfields as $i => $jsonfield) {
        $sql = "INSERT into formfields 
            (key, formId, sortorder, label, json) 
            values 
            (:key,: formId, :sortorder, :label, :json)" ;
        $data = [
            'formId' => $formId,
            'key' => $jsonfield->name
            'sortorder' => $i,
            'label' => $jsonfield->label
            'key' => json_encode($jsonfield)
        ];
        $query = $pdo->prepare($sql);
        $query->execute($data);
    }
}

2) Read the form from the database and show it on the website

<?php
function readJsonForForm($formId) {
    $query = $pdo->prepare("SELECT * from formfields where formId=:formId");
    $query->execute(['formId' => $formId]);
    $formfields = $query->fetchAll();

    $json = [] ;
    foreach ($formfields as $formfield) {
        if ($fields) foreach ($fields as $field) {
            $json[] = json_decode($formfield['json']);
        }
    }
    return json_encode($json);
}

$jsonForForm = readJsonForForm($formId) ;
?>

We’re now using the form-render javascript library to display the form

<form accept-charset="utf-8" autocomplete="off" method="post" action="/forms/save/<?=$formId?>" class="form-horizontal">
    <h3><?=$formName?></h3>

    <div id="formrender"></div>

    <button type="submit" class="btn btn-success"> Save</button>
</form>

<script src="/resources/plugins/form-builder/form-render.min.js"></script>
<script>
  var container = $('#formrender');
  var options = {
    container,
    dataType:'json',
    formData:'<?=$jsonForForm?>'
  };
  container.formRender(options);
</script>
<?php
function saveUserInput($formId) {

    $query = $pdo->prepare("INSERT into formcompleted (dateCreated) values (now()");
    $query->execute();
    $formCompletedId = $pdo->lastInsertId();

    foreach ($_POST as $key => $value) {
        $data = [
            'formCompletedId' => $formCompletedId,
            'key' => $key,
            'value' => $value
        ];
        $query = $pdo->prepare("INSERT INTO formcompletedfields (formId,key,value) values (:formId,:key,:value)");
        $query->execute($data);
    }
?>

4) View the User Data

<?php
function getUserInput($formCompletedId) {

    $sql = "SELECT formfields.label, formcompletedfields.key, formcompletedfields.value
        from formcompletedfields
        LEFT JOIN formfields on formfields.key = formcompletedfields.key and formfields.formId=:formId
        where formcompletedfields.formCompletedId = :formCompletedId " ;

    $query = $pdo->prepare($sql);
    $query->execute(['formCompletedId'=>$formCompletedId]);
    $rows = $query->fetchAll() ;
}
?>

And to output as a table of results..

...
<table>
    <?php foreach ($rows as $row) { ?>
    <tr>
        <td><?=$row['label']?></td>
        <td><?=$row['value']?></td>
    </tr>
    <?php } ?>
</table>

5) Output form again – with the user data

For each field in the form, we have a (json-encoded) object like this:
{"type":"text","label":"Enter your name","className":"form-control","name":"fullname"}

We want to read the relevant data from the database and add it to the json string like so:
{“type":"text","label":"Enter your name","className":"form-control","name":"fullname","userData":["Bronwen Reid"]}

<?php
function readJsonForCompletedForm($completedFormId) {

    // get Form ID
    $query = $pdo->prepare("SELECT formId from completedforms where id=:completedFormId");
    $query->execute();
    $formId = $query->fetchColumn();

    $sql = "SELECT formfields.*, formcompletedfields.value
        FROM formfields
        LEFT JOIN formcompletedfields on formfields.key = formcompletedfields.key
        WHERE formfields.formId=:formId " ;
    $query = $pdo->prepare($sql);
    $query->execute(['formId' => $formId]);
    $formfields = $query->fetchAll();

    $json = [] ;
    foreach ($formfields as $formfield) {
        $obj = json_decode($formfield['json']);
        $obj->userData = [ $formfield['value'] ] ;
        $json[] = $obj ;
    }
    return json_encode($json);
}
$jsonForCompletedForm = readJsonForCompletedForm($formId) ;
?>

That should do it.