function updateTables($dbh, $data)
{
$pid = $data["pid"];
$interaction = $data["interaction"];
$tmsp = $data["tmsp"];
$page = $data["page"];
$set = $data["set"];
//insert data into master interactions table
addBigTableData($dbh, 'interactions_master', $pid, $interaction);
//insert data into page table
addBigTableData($dbh, 'interactions_page_' . $page, $pid, $interaction);
//insert data into participant interactions table
$sql = "INSERT INTO p" . $pid . "_interactions (timestp, interaction, page, set) VALUES (:timestp, :interaction, :page, :set);";
try {
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':timestp', $tmsp);
$stmt->bindParam(':interaction', $interaction);
$stmt->bindParam(':page', $page);
$stmt->bindParam(':set', $set);
$stmt->execute();
} catch (PDOException $e) {
echo 'SQL Query: ', $sql;
echo 'Error: ' . $e->getMessage();
}
}
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"]);
}
}
