* https://www.sqlite.org/download.html
* https://sqlitebrowser.org/dl/
CREATE TABLE `jobs` (
`job_reference` TEXT,
`job_title` TEXT,
`job_status` TEXT,
`reply_to` TEXT
);
CREATE TABLE `job_history` (
`job_reference` TEXT,
`field_name` TEXT,
`old_value` TEXT,
`new_value` TEXT,
`changed_on` TEXT
);
$db_field_name) {
$data[$job_reference][$db_field_name] = $fields[$field_pos];
}
}
$myDB = new PDO('sqlite:/home/seven/tmp/grant.db');
foreach($data as $job_ref=>$val) {
if(empty($val['job_reference']))
continue;
$job_reference = $val['job_reference'];
$sql = "select * from jobs where job_reference= :job_reference";
$stmt = $myDB->prepare($sql);
$stmt->bindParam(':job_reference', $job_reference, PDO::PARAM_STR);
$stmt->execute();
$results = $stmt->fetch();
if($results) {
foreach($field_name as $field_pos=>$db_field_name) {
if($val[$db_field_name] != $results[$db_field_name]) {
$changed_on = date("Y-m-d H:i");
echo "Field $db_field_name changed from {$results[$db_field_name]} to {$val[$db_field_name]}\n";
$sql = "insert into job_history(job_reference, field_name, old_value, new_value, changed_on) values(:job_reference, :field_name, :old_value, :new_value, :changed_on)";
$stmt_change = $myDB->prepare($sql);
$stmt_change->bindParam(':job_reference', $job_reference, PDO::PARAM_STR);
$stmt_change->bindParam(':field_name', $db_field_name, PDO::PARAM_STR);
$stmt_change->bindParam(':new_value', $val[$db_field_name], PDO::PARAM_STR);
$stmt_change->bindParam(':old_value', $results[$db_field_name], PDO::PARAM_STR);
$stmt_change->bindParam(':changed_on', $changed_on, PDO::PARAM_STR);
$stmt_change->execute();
$sql = "update jobs set $db_field_name = :new_value where job_reference = :job_reference";
$stmt_update = $myDB->prepare($sql);
$stmt_update->bindValue(':job_reference', $job_reference, PDO::PARAM_STR);
$stmt_update->bindValue(':new_value', $val[$db_field_name], PDO::PARAM_STR);
$stmt_update->execute();
}
}
} else {
echo "Insert row\n";
$field_names = implode(",", array_values($field_name));
foreach($field_name as $pos_id=>$field_name_val) {
$val_names[] = ":".$field_name_val;
}
$value_names = implode(",", $val_names);
$sql = "insert into jobs($field_names) values($value_names)";
$stmt = $myDB->prepare($sql);
foreach($field_name as $field_pos=>$db_field_name) {
$bind_name = ":".$db_field_name;
$stmt->bindParam($bind_name, $val[$db_field_name], PDO::PARAM_STR);
}
$stmt->execute();
}
}
?>