function jotform_db2dump($apiKey, $format, $formTitle, $questions, $submissions)
{
// get a list of questions
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields) && $q['text'] != "" && $q['text'] != "....") {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
// CREATE TABLE STATEMENT
$table = mysql_fieldname_format($formTitle);
$sql .= "IF NOT EXISTS (SELECT NAME FROM SYSIBM.SYSTABLES WHERE NAME=`" . $table . "`)\nTHEN\n";
$sql .= "CREATE TABLE `" . $table . "`(\n";
$fields_sql = array();
$fields = array();
foreach ($ordered_questions as $order => $i) {
$mysql_type = "varchar(255)";
if ($questions[$i]['type'] == "control_textarea") {
$mysql_type = "text";
}
array_push($fields, $questions[$i]['text']);
array_push($fields_sql, "\t`" . mysql_fieldname_format($questions[$i]['text']) . "` " . $mysql_type);
}
$sql .= "\t`submissionID` BIGINT,\n";
$sql .= implode(",\n", $fields_sql);
$sql .= ",\n\tPRIMARY KEY(submissionID)";
$sql .= ")\nEND IF\n";
// INSERT / REPLACE STATEMENT
foreach ($submissions as $s) {
$insert = "INSERT IGNORE INTO `{$table}` (\n";
$keys = array("`submissionID`");
$values = array($s["id"]);
$answer = array();
foreach ($s['answers'] as $a) {
$answer[$a['text']] = $a['answer'];
}
foreach ($fields as $k) {
if ($k != "" && $k != "....") {
array_push($keys, "`" . mysql_fieldname_format($k) . "`");
if (is_array($answer[$k])) {
$a = implode(",", $answer[$k]);
} else {
$a = $answer[$k];
}
array_push($values, "'" . my_mysql_real_escape_string($a) . "'");
}
}
$insert .= implode(", ", $keys);
$insert .= "\n) VALUES (\n";
$insert .= implode(", ", $values);
$insert .= "\n);\n\n";
$sql .= $insert;
}
return $sql;
}
function jotform_postgresqldump($apiKey, $format, $formTitle, $questions, $submissions)
{
// get a list of questions
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields) && $q['text'] != "" && $q['text'] != "....") {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
// CREATE TABLE STATEMENT
$table = mysql_fieldname_format($formTitle);
$sql .= "CREATE TABLE IF NOT EXISTS \"" . $table . "\" (\n";
$fields_sql = array();
$fields = array();
foreach ($ordered_questions as $order => $i) {
$mysql_type = "varchar(255)";
if ($questions[$i]['type'] == "control_textarea") {
$mysql_type = "text";
}
array_push($fields, $questions[$i]['text']);
array_push($fields_sql, "\t\"" . mysql_fieldname_format($questions[$i]['text']) . "\" " . $mysql_type);
}
$sql .= "\t\"submissionID\" bigserial primary key,\n";
$sql .= implode(",\n", $fields_sql);
$sql .= "\n);\n\n";
// INSERT / REPLACE STATEMENT
foreach ($submissions as $s) {
$insert = "INSERT INTO \"" . $table . "\" (";
$keys = array("\"submissionID\"");
$values = array($s["id"]);
$answer = array();
foreach ($s['answers'] as $a) {
$answer[$a['text']] = $a['answer'];
}
foreach ($fields as $k) {
if ($k != "" && $k != "....") {
array_push($keys, "\"" . mysql_fieldname_format($k) . "\"");
if (is_array($answer[$k])) {
$a = implode(",", $answer[$k]);
} else {
$a = $answer[$k];
}
array_push($values, "'" . my_mysql_real_escape_string($a) . "'");
}
}
$insert .= implode(", ", $keys);
$insert .= ")\nVALUES (";
$insert .= implode(", ", $values);
$insert .= ");\n\n";
$sql .= $insert;
}
return $sql;
}
function jotform_mysqldump($apiKey, $formID)
{
// get a list of questions
$jotformAPI = new JotForm($apiKey);
$form = $jotformAPI->getForm($formID);
$formTitle = $form['title'];
$questions = $jotformAPI->getFormQuestions($formID);
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields)) {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
//print "<pre>";
//print_r( $questions );
// prepare CREATE TABLE code
$table = mysql_fieldname_format($formTitle);
$sql = "CREATE TABLE IF NOT EXISTS `" . $table . "` (\n";
$fields_sql = array();
$fields = array();
foreach ($ordered_questions as $order => $i) {
$mysql_type = "varchar(255)";
if ($questions[$i]['type'] == "control_textarea") {
$mysql_type = "text";
}
array_push($fields, $questions[$i]['text']);
array_push($fields_sql, "\t`" . mysql_fieldname_format($questions[$i]['text']) . "` " . $mysql_type);
}
$sql .= implode(",\n", $fields_sql);
$sql .= "\n);\n\n";
//print $sql;
// get submission data
$submissions = $jotformAPI->getFormSubmissions($formID);
//print_r($submissions);
foreach ($submissions as $s) {
$insert = "INSERT IGNORE INTO `{$table}` (\n";
$keys = array();
$values = array();
foreach ($fields as $k) {
//print "<li>$k".$s['fields'][$k];
array_push($keys, "`" . mysql_fieldname_format($k) . "`");
array_push($values, "'" . mysql_real_escape_string($s['fields'][$k]) . "'");
}
$insert .= implode(", ", $keys);
$insert .= "\n) VALUES (\n";
$insert .= implode(", ", $values);
$insert .= "\n);\n\n";
$sql .= $insert;
}
return $sql;
}
function jotform_mongodbdump($apiKey, $format, $formTitle, $questions, $submissions)
{
// get a list of questions
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields) && $q['text'] != "" && $q['text'] != "....") {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
$fields = array();
foreach ($ordered_questions as $order => $i) {
array_push($fields, $questions[$i]['text']);
}
$sql = array();
foreach ($submissions as $s) {
$insert = array();
$keys = array("submission_id");
$values = array($s["id"]);
$answer = array();
foreach ($s['answers'] as $a) {
$answer[$a['text']] = $a['answer'];
}
foreach ($fields as $k) {
if ($k != "" && $k != "....") {
array_push($keys, "" . mysql_fieldname_format($k) . "");
if (is_array($answer[$k])) {
$a = implode(",", $answer[$k]);
} else {
$a = $answer[$k];
}
array_push($values, "" . my_mysql_real_escape_string($a) . "");
}
}
for ($i = 0; $i < count($keys); $i++) {
$insert[$keys[$i]] = $values[$i];
}
array_push($sql, $insert);
}
return $sql;
}
function jotform_sqlserverdump($apiKey, $format, $formTitle, $questions, $submissions)
{
// get a list of questions
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields) && $q['text'] != "" && $q['text'] != "....") {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
// CREATE TABLE STATEMENT
$table = mysql_fieldname_format($formTitle);
$sql .= "IF NOT EXISTS\n(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('" . $table . "') AND type in (N'U'))\nBEGIN\n";
$sql .= "CREATE TABLE " . $table . "\n(\n";
$fields_sql = array();
$fields = array();
foreach ($ordered_questions as $order => $i) {
$mysql_type = "varchar(255)";
if ($questions[$i]['type'] == "control_textarea") {
$mysql_type = "text";
}
array_push($fields, $questions[$i]['text']);
array_push($fields_sql, "\t" . mysql_fieldname_format($questions[$i]['text']) . " " . $mysql_type);
}
$sql .= "\tsubmissionID bigint PRIMARY KEY,\n";
$sql .= implode(",\n", $fields_sql);
$sql .= "\n);";
$sql .= "\nEND";
$sql .= "\nGO\n\n";
// INSERT / REPLACE STATEMENT
foreach ($submissions as $s) {
$insert = "IF NOT EXISTS\n(SELECT * FROM " . $table . " WHERE submissionID=" . $s["id"] . ")\n";
$insert .= "\tINSERT INTO " . $table . "(";
$keys = array("submissionID");
$values = array($s["id"]);
$answer = array();
foreach ($s['answers'] as $a) {
$answer[$a['text']] = $a['answer'];
}
foreach ($fields as $k) {
if ($k != "" && $k != "....") {
array_push($keys, mysql_fieldname_format($k));
if (is_array($answer[$k])) {
$a = implode(",", $answer[$k]);
} else {
$a = $answer[$k];
}
array_push($values, "'" . my_mysql_real_escape_string($a) . "'");
}
}
$insert .= implode(", ", $keys);
$insert .= ")\n\tVALUES (";
$insert .= implode(", ", $values);
$insert .= ")\nELSE\n";
$insert .= "\tUPDATE " . $table . "\n\tSET ";
for ($i = 1; $i < count($keys); $i++) {
$insert .= $keys[$i] . "=" . $values[$i];
if ($i < count($keys) - 1) {
$insert .= ", ";
}
}
$insert .= "\n\tWHERE submissionID=" . $s["id"] . "\n\n";
$sql .= $insert;
}
return $sql;
}
function jotform_sybasedump($apiKey, $format, $formTitle, $questions, $submissions)
{
// get a list of questions
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields) && $q['text'] != "" && $q['text'] != "....") {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
// CREATE TABLE STATEMENT
$table = mysql_fieldname_format($formTitle);
$sql .= "IF NOT EXISTS (\n\tSELECT 1\n\tFROM sysobjects\n\tWHERE name=" . $table . "\n\tAND type=\"U\"\n)\n";
$sql .= "EXECUTE(\"CREATE TABLE " . $table . " (\n";
$fields_sql = array();
$fields = array();
foreach ($ordered_questions as $order => $i) {
$mysql_type = "varchar(255)";
if ($questions[$i]['type'] == "control_textarea") {
$mysql_type = "text";
}
array_push($fields, $questions[$i]['text']);
array_push($fields_sql, "\t`" . mysql_fieldname_format($questions[$i]['text']) . "` " . $mysql_type);
}
$sql .= "\t`submissionID` bigint,\n";
$sql .= implode(",\n", $fields_sql);
$sql .= ",\n\tPRIMARY KEY(submissionID)";
$sql .= ")\")\nGO\n\n";
// INSERT / REPLACE STATEMENT
foreach ($submissions as $s) {
$insert = "INSERT INTO `{$table}` (";
$keys = array("`submissionID`");
$values = array($s["id"]);
$answer = array();
foreach ($s['answers'] as $a) {
$answer[$a['text']] = $a['answer'];
}
foreach ($fields as $k) {
if ($k != "" && $k != "....") {
array_push($keys, "`" . mysql_fieldname_format($k) . "`");
if (is_array($answer[$k])) {
$a = implode(",", $answer[$k]);
} else {
$a = $answer[$k];
}
array_push($values, "'" . my_mysql_real_escape_string($a) . "'");
}
}
$insert .= implode(", ", $keys) . ")\n";
$insert .= "\t ON EXISTING UPDATE `" . $table . "`\n\t SET ";
for ($i = 0; $i < count($keys); $i++) {
$insert .= $keys[$i] . "=" . $values[$i];
if ($i < count($keys) - 1) {
$insert .= " AND ";
}
}
$insert .= "\nVALUES (";
$insert .= implode(", ", $values);
$insert .= ");\n\n";
$sql .= $insert;
}
return $sql;
}
function jotform_informixdump($apiKey, $format, $formTitle, $questions, $submissions)
{
// get a list of questions
$new_questions = array();
$ignored_fields = array("control_head", "control_button", "control_pagebreak", "control_collapse", "control_text");
$i = 0;
foreach ($questions as $q) {
if (!in_array($q['type'], $ignored_fields) && $q['text'] != "" && $q['text'] != "....") {
array_push($new_questions, $q);
$ordered_questions[$q['order']] = $i++;
}
}
$questions = $new_questions;
// CREATE TABLE STATEMENT
$table = mysql_fieldname_format($formTitle);
$sql .= "CREATE TABLE IF NOT EXISTS `" . $table . "` (\n";
$fields_sql = array();
$fields = array();
foreach ($ordered_questions as $order => $i) {
$mysql_type = "varchar(255)";
if ($questions[$i]['type'] == "control_textarea") {
$mysql_type = "text";
}
array_push($fields, $questions[$i]['text']);
array_push($fields_sql, "\t`" . mysql_fieldname_format($questions[$i]['text']) . "` " . $mysql_type);
}
$sql .= "\t`submissionID` BIGINT PRIMARY KEY,\n";
$sql .= implode(",\n", $fields_sql);
$sql .= "\n);\n\n";
// INSERT / REPLACE STATEMENT
foreach ($submissions as $s) {
$insert = "MERGE INTO `" . $table . "` (\n";
$insert .= "ON " . $table . ".submissionID = " . $s["id"] . "\n";
$insert .= "WHEN MATCHED THEN\n";
$keys = array();
$values = array();
$answer = array();
foreach ($s['answers'] as $a) {
$answer[$a['text']] = $a['answer'];
}
foreach ($fields as $k) {
if ($k != "" && $k != "....") {
array_push($keys, "`" . mysql_fieldname_format($k) . "`");
if (is_array($answer[$k])) {
$a = implode(",", $answer[$k]);
} else {
$a = $answer[$k];
}
array_push($values, "'" . my_mysql_real_escape_string($a) . "'");
}
}
$insert .= "UPDATE TABLE `" . $table . "` SET ";
for ($i = 0; $i < count($keys); $i++) {
$insert .= $keys[$i] . "=" . $values[$i];
if ($i < count($keys) - 1) {
$insert .= " AND ";
}
}
$insert .= "\nWHEN NOT MATCHED THEN\n";
$insert .= "INSERT INTO `" . $table . "` (";
$insert .= implode(", ", $keys);
$insert .= ")\nVALUES (";
$insert .= implode(", ", $values);
$insert .= ");\n\n";
$sql .= $insert;
}
return $sql;
}
