<?php
// index.php for swamp-logger
//
// Purpose:
// To gather the POST values to be inserted into an SQLite db
// NOTES:
// A Windows apache server needs to make sure the php_sqlite.dll and the php_pdo.dll
// is enabled in the php.ini file.
// check if the request for this file is a 'post' request,if not send the form
// back with and error if any:
if($_POST && isset($_POST['user'], $_POST['drawing_no'])){
// get the posted values:
$user = $_POST['user'];
$drawing_no = $_POST['drawing_no'];
// establish the db connection:
$pdo = get_db();
// insert the data:
$id = insert_log_data($pdo, $user, $drawing_no);
// you could check here for the id and handle it if it's null/not there.
if($id){
// just echo something back to the user:
echo "Data with ID " . $id . " logged successfully!";
}
// close the db:
$pdo = null;
} else {// it's not a POST or there's and error or missing data, send the form:
// --------- FORM SECTION (used for testing, can be deleted) -------------- //
?>
Please enter information in all fields!
<form action="" method="post">
Name: <input type="text" name="user" /><br/>
Drawing Number: <input type="text" name="drawing_no" /><br/>
<input type="submit" value="Submit Log Details" />
</form>
<?php
} // end else.
// --------- END FORM SECTION --------------------------------------------- //
// Returns an SQLite PDO connection object ready for use.
function get_db(){
try{
// create the PDO conn object:
$pdo = new PDO('sqlite:usage_log.sqlite3');
// query to create the table if not exists:
$create_query = "
CREATE TABLE IF NOT EXISTS usage_log(
id INTEGER PRIMARY KEY,
user TEXT,
drawing_no TEXT,
datetime TEXT
)";
// this will just exit if the table exists:
$pdo->exec($create_query);
// return the PDO object:
return $pdo;
}
catch(PDOException $e){
echo $e->getMessage();
}
}
// Inserts the sanitized data into the db, PDO does handle sanitization to
// avoid sql injection but you may need handle the input from a html user form
// more strictly.
function insert_log_data($pdo, $user, $drawing_no){
try{
// build the query string as a 'prepared statement':
$sql = "INSERT INTO usage_log(user, drawing_no, datetime)
VALUES (:user, :drawing_no, :datetime)";
// get the current datetime stamp (may need to set timezone):
// eg: date_default_timezone_set('UTC');
$datetime = date("Y-m-d H:i:s");
// prepare and execute the statement with the args passed in:
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':user', $user);
$stmt->bindValue(':drawing_no', $drawing_no);
$stmt->bindValue(':datetime', $datetime);
$stmt->execute();
// we can return the last inserted id (our new record):
return $pdo->lastInsertId();
}
catch(PDOException $e){
echo $e->getMessage();
}
}