Add Form to Database Directly

Q

How to add a new form to Form Tools database directly?

✍: FYIcenter.com

A

Adding a new form to Form Tools database directly is not recommended. But if you want to automate the form creation process, you can still do it by follow this tutorial.

1. You should create a Form Tools test database called "ft" to try this process first.

2. Run the following PHP script to create a form with default fields only.

<html><body><pre>
<?php
#- Add-Form.php
#- Copyright (c) FYIcenter.com

   if (count($argv)<2) {
      print "Error: Missing form name.\n";
      print "   Run the script again as 'php Add-Form.php form_name'.\n";
      exit;
   }

   $name = $argv[1];
   $con = mysqli_connect('127.0.0.1','formtools','f0rmt00l$','ft');

   if (is_form_exist($name)) {
     print "Error: Existing form with the same name found.\n";
     exit;
   };

   add_form($name);

   mysqli_close($con);

function add_form($name) {
   global $con;
   $rs = mysqli_query($con,"START TRANSACTION");
   $rs = mysqli_query($con,"SET autocommit = OFF");

#- insert the form record
$sql = <<<EOD
INSERT INTO ft_forms (form_type, access_type, submission_type, date_created,
   is_active, is_initialized, is_complete, is_multi_page_form, form_name,
   form_url, redirect_url, auto_delete_submission_files, submission_strip_tags,
   edit_submission_page_label, add_submission_button_label
) VALUES ('internal', 'public', 'direct', now(),
   'yes', 'yes', 'yes', 'no', '$name',
   '', '', 'yes', 'yes', 'Edit Submission', 'Add Â»'
);
EOD;
   $form_id = insert_record($sql);

#- insert the default form field records
$sql = <<<EOD
INSERT INTO ft_form_fields (form_id, field_name, field_test_value, field_size,
   field_type_id, is_system_field, data_type, field_title, col_name,
   list_order, is_new_sort_group, include_on_redirect
) VALUES ($form_id, 'core__submission_id', '', 'medium',
   1, 'yes', 'number', 'ID', 'submission_id',
   1, 'yes', 'no'
);
EOD;
   $field_id = insert_record($sql);

$sql = <<<EOD
INSERT INTO ft_form_fields (form_id, field_name, field_test_value, field_size,
   field_type_id, is_system_field, data_type, field_title, col_name,
   list_order, is_new_sort_group, include_on_redirect
) VALUES ($form_id, 'core__submission_date', '', 'medium',
   8, 'yes', 'date', 'Date', 'submission_date',
   2, 'yes', 'no'
);
EOD;
   $field_id = insert_record($sql);

$sql = <<<EOD
INSERT INTO ft_form_fields (form_id, field_name, field_test_value, field_size,
   field_type_id, is_system_field, data_type, field_title, col_name,
   list_order, is_new_sort_group, include_on_redirect
) VALUES ($form_id, 'core__last_modified', '', 'medium',
   8, 'yes', 'date', 'Last modified', 'last_modified_date',
   3, 'yes', 'no'
);
EOD;
   $field_id = insert_record($sql);

$sql = <<<EOD
INSERT INTO ft_form_fields (form_id, field_name, field_test_value, field_size,
   field_type_id, is_system_field, data_type, field_title, col_name,
   list_order, is_new_sort_group, include_on_redirect
) VALUES ($form_id, 'core__ip_address', '', 'medium',
   1, 'yes', 'number', 'IP Address', 'ip_address',
   4, 'yes', 'no'
);
EOD;
   $field_id = insert_record($sql);

#- insert the default view related records
$sql = <<<EOD
INSERT INTO ft_list_groups (group_type, group_name, custom_data, list_order)
   VALUES ('form_${form_id}_view_group', 'Views', '', 1);
EOD;
   $group_id = insert_record($sql);

$sql = <<<EOD
INSERT INTO ft_views (form_id, access_type, view_name, view_order,
   is_new_sort_group, group_id, num_submissions_per_page, default_sort_field,
   default_sort_field_order, may_add_submissions, may_copy_submissions,
   may_edit_submissions, may_delete_submissions, has_client_map_filter,
   has_standard_filter
) VALUES ($form_id, 'public', 'All submissions', '1',
   'yes', $group_id, 10, 'submission_date',
   'desc', 'yes', 'no',
   'yes', 'yes', 'no',
   'no'
);
EOD;
   $view_id = insert_record($sql);

$sql = <<<EOD
INSERT INTO ft_list_groups (group_type, group_name, custom_data, list_order)
   VALUES ('view_fields_$view_id', 'Data', 1, 1);
EOD;
   $group_id = insert_record($sql);

#- create the form table
$sql = <<<EOD
CREATE TABLE ft_form_$form_id (
   submission_id INTEGER NOT NULL AUTO_INCREMENT
   , submission_date DATETIME
   , last_modified_date DATETIME
   , ip_address VARCHAR(15)
   , is_finalized VARCHAR(3) DEFAULT 'yes'
   , PRIMARY KEY (submission_id)
   );
EOD;
   create_table($sql);

   # $rs = mysqli_query($con,"ROLLBACK");
   $rs = mysqli_query($con,"COMMIT");
}

function insert_record($sql) {
   global $con;
   $id = 0;
   if (mysqli_query($con,$sql)) {
      $id = mysqli_insert_id($con);
      print("Record inserted with id = $id.\n");
   } else {
      print("Error: Could not insert the record.\n");
      print(mysqli_errno($con).": ".mysqli_error($con)."\n");
      die;
   }
   return $id;
}

function create_table($sql) {
   global $con;
   if (mysqli_query($con,$sql)) {
      print("Table inserted.\n");
   } else {
      print("Error: Could not create the table.\n");
      print(mysqli_errno($con).": ".mysqli_error($con)."\n");
      die;
   }
}

function is_form_exist($name) {
   global $con;
   $sql = "SELECT count(*) FROM ft_forms where form_name = '$name'";
   $rs = mysqli_query($con,$sql);
   $row = mysqli_fetch_array($rs);
   $count = $row[0];
   mysqli_free_result($rs);
   return $count>0;
}
?>
</pre></body></html>

3. Create another PHP script to add additional fields.

Â

â‡’ Form Tools - Frequently Asked Questions

â‡ Load CSV Data as New Form

â‡‘ Understanding Form Tools Database

â‡‘â‡‘ Form Tools - Frequently Asked Questions

2019-11-08, 1568🔥, 0💬