function makeTables($dbh, $data)
{
//make participant table
$pid = $data["pid"];
makeParticipantTable($dbh, $pid);
//make page tables and fill columns for master table
$pages = $data["pages"];
$cols = array();
foreach ($pages as $p => $page) {
$pageCols = array();
if (isset($page["interactions"])) {
$interactions = $page["interactions"];
foreach ($interactions as $interaction => $junk) {
$pageCols[] = $interaction;
if (!in_array($interaction, $cols)) {
$cols[] = $interaction;
}
}
}
if (!empty($pageCols)) {
makeBigTable($dbh, 'interactions_page_' . (string) ($p + 1), $pageCols);
}
}
//make master table
if (!empty($cols)) {
makeBigTable($dbh, 'interactions_master', $cols);
}
}
function updateTables($dbh, $data)
{
//set program-defined variables
$pid = $data["pid"]["value"];
$updatetime = $data["updatetime"]["value"];
//create participant table
makeParticipantTable($dbh, $pid);
//set arrays for big table columns and values
$columns = array(0 => "lastupdate");
$placeholders = array(0 => ":lastupdate");
$values = array("lastupdate" => $updatetime);
$asks = array();
$page = -1;
$pages = array();
$pageArray = array();
foreach ($data as $key => $block) {
if ($key != "action" && $block["name"] != 'pid' && $block["name"] != 'updatetime') {
//set page
if ($block["page"] > $page) {
$page = $block["page"];
$pageArray = array();
$pageArray["columns"] = array(0 => "lastupdate");
$pageArray["placeholders"] = array(0 => ":lastupdate");
$pageArray["values"] = array("lastupdate" => $updatetime);
}
//set array values
$column = $block["name"];
$text = $block["ask"];
$value = empty($block["value"]) ? null : $block["value"];
$tmsp = $block["tmsp"];
$columns[] = $column;
$columns[] = $column . "_time";
$pageArray["columns"][] = $column;
$pageArray["columns"][] = $column . "_time";
$placeholders[] = ":" . $column;
$placeholders[] = ":" . $column . "_time";
$pageArray["placeholders"][] = ":" . $column;
$pageArray["placeholders"][] = ":" . $column . "_time";
$values[$column] = $value;
$values[$column . "_time"] = $tmsp;
$pageArray["values"][$column] = $value;
$pageArray["values"][$column . "_time"] = $tmsp;
$pages[$page] = $pageArray;
//add row for question if needed
$sql = "INSERT INTO p" . $pid . "_data SELECT :label WHERE NOT EXISTS " . "(SELECT label FROM p" . $pid . "_data WHERE label = :label);";
try {
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':label', $column);
$stmt->execute();
} catch (PDOException $e) {
echo 'SQL Query: ', $sql;
echo 'Error: ' . $e->getMessage();
}
//insert data into participant data table
$sql = "UPDATE p" . $pid . "_data SET (question, answer, timestp) = (:question, :answer, :tmsp) WHERE label = :label;";
try {
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':label', $column);
$stmt->bindParam(':question', $text);
$stmt->bindParam(':answer', $value);
$stmt->bindParam(':tmsp', $tmsp);
$stmt->execute();
} catch (PDOException $e) {
echo 'SQL Query: ', $sql;
echo 'Error: ' . $e->getMessage();
}
}
}
//insert data into master data table
addBigTableData($dbh, $pid, 'data_master', $columns, $placeholders, $values);
//insert data into page tables
foreach ($pages as $p => $page) {
addBigTableData($dbh, $pid, 'data_page_' . $p, $page["columns"], $page["placeholders"], $page["values"]);
}
}