query('PRAGMA journal_mode = WAL;'); //multiple readers, https://sqlite.org/wal.html
$db->query('PRAGMA busy_timeout = 5000;');//PRAGMA busy_timeout = milliseconds before retry; will it work on PDO vs SQLITE3?
$db->query('PRAGMA cell_size_check = ON;'); //database corruption is detected earlier and is less likely to "spread"
#blog = doc id, doc_title, doc_text, doc_image
$db->exec( 'CREATE TABLE IF NOT EXISTS blog ( doc_id INTEGER PRIMARY KEY, doc_title TEXT, doc_text TEXT, doc_image BLOB, doc_created_time INTEGER );');
//GET EVENT FROM WEB PAGE, always filter user input
//htmlspecialchars() for output
$filter_id = filter_input(INPUT_POST, FILTER_VALIDATE_INT); //watch for 0
$filter_created_time = filter_input(INPUT_POST, 'doc_created_time', FILTER_SANITIZE_SPECIAL_CHARS); //date time sends as STRING but need to store as int
$filter_title = filter_input(INPUT_POST, 'doc_title', FILTER_SANITIZE_SPECIAL_CHARS); //HTML-encode '"<>& and characters
$filter_text = filter_input(INPUT_POST, 'doc_text', FILTER_SANITIZE_SPECIAL_CHARS);
if ( is_null($filter_id) ) {
$filter_id = 1;
}
if ( is_null($filter_created_time) ) {
$filter_created_time = '1982-09-09T09:09';
}
if ( is_null($filter_title) ) {
$filter_title = 'Hi World';
}
if ( is_null($filter_text) ) {
$filter_text = 'Hows it going';
}
//CONVERT date time string to UNIX epoch time to store as integer watch y37 bugs
//Date values separated by dash are assumed to be in European order: d-m-y
//strtotime doesn't work properly
//need to handle Atom (example: 2005-08-15T15:52:01+00:00) format
//watch no seconds, need \T to handle
$obj_created_time = DateTime::createFromFormat('Y-m-d\TH:i', $filter_created_time);
$filter_created_timestamp = $obj_created_time->getTimestamp();
//filtered above so dont need htmlspecialchars()
echo 'hi'.$filter_id;
echo $filter_title;
echo 'created time>>'.$filter_created_time.'='.$filter_created_timestamp."<<<";
echo ' '.$filter_text;
//INSERT OR ADD VALUES
$qry = $db->prepare( 'INSERT INTO blog (doc_title, doc_text, doc_created_time) VALUES (?,?, ?)');
$qry->bindValue(1, $filter_title, SQLITE3_TEXT);
$qry->bindValue(2, $filter_text, SQLITE3_TEXT);
$qry->bindValue(3, $filter_created_timestamp, SQLITE3_INTEGER);
$res = $qry->execute();
//UPDATE post if optional image is there
//The global $_FILES will contain all the uploaded file information.
//$_FILES['userfile']['tmp_name'] need to move since temporary replace userfile with form input name!!
//isset($_FILES['doc_image']) always return true, so use if (is_uploaded_file($_FILES['userfile']['tmp_name'])) {
if(is_uploaded_file($_FILES['doc_image']['tmp_name']) ) {
echo "we have image ";
//check if image file
list($gd_width, $gd_height, $gd_type, $gd_attr) = getimagesize($_FILES['doc_image']['tmp_name']); //list Assign variables as if they were an array
echo print_r($gd_width)."|width|".$gd_height."|height|".print_r($gd_type)."|type|".print_r($gd_attr)."|>>>>>".mime_content_type($_FILES['doc_image']['tmp_name']);
$update_statement = $db->prepare('UPDATE blog SET doc_image = ? WHERE doc_id = ?;');
$last_id = $db->lastInsertId();
$update_statement->bindParam(2, $last_id);
$update_statement->bindParam(1, $file_pointer, PDO::PARAM_LOB);
$db->beginTransaction();
$result = $update_statement->execute();
$db->commit();
}
//test array
//$blogs=$db->query('SELECT * FROM blog;', PDO::FETCH_ASSOC)->fetchAll();
//echo print_r($blogs);
//RETRIEVE VALUES
$stmt = $db->prepare("select doc_id,doc_title, doc_text, doc_image, datetime(doc_created_time, 'unixepoch') from blog;");
$stmt->bindColumn(1, $bind_doc_id, PDO::PARAM_INT);
$stmt->bindColumn(2, $bind_doc_title, PDO::PARAM_STR, 256);
$stmt->bindColumn(3, $bind_doc_text, PDO::PARAM_STR, 256);
$stmt->bindColumn(4, $bind_doc_image_lob, PDO::PARAM_LOB);
$stmt->bindColumn(5, $bind_doc_created_time, PDO::PARAM_STR, 256); //convert it back to string from unix epoch time, see select strftim
$stmt->execute();
echo 'array begin while ';
while ($stmt->fetch(PDO::FETCH_BOUND)) {
echo ' ----------------------------------------here ';
//retrieve text values
echo $bind_doc_id . "-" . $bind_doc_title . "-" . $bind_doc_text . $bind_doc_created_time. " ";
echo "";
//retrieve image
echo "";
}
$max_rows=5;
$count_num_blogs=$db->query('SELECT count(doc_id) FROM blog;', PDO::FETCH_ASSOC)->fetchAll();
echo print_r($count_num_blogs);
//echo print_r($rows);
if( $count_num_blogs[0] >= $max_rows) {
$delete_statement = $db->prepare('DELETE FROM blog WHERE doc_id not in
(SELECT doc_id FROM blog ORDER BY doc_id DESC LIMIT ?);');
$delete_statement ->bindValue(1, $max_rows, SQLITE3_INTEGER);
$result = $delete_statement->execute();
}
//benchmark simulate multiple connections and multiple test using ab tool linux, 100 connections, 50 concurrently ;ApacheBench 100% 2seconds (longest request)
//sudo apt install apache2-utils
//ab -n 100 -c 50 https://example.com/sqlite.php
//https://www.php.net/manual/en/pdo.lobs.php
//close db
$db = null;
?>
![](data:image/png;base64,".base64_encode($bind_doc_image_lob). ")
";
}
$max_rows=5;
$count_num_blogs=$db->query('SELECT count(doc_id) FROM blog;', PDO::FETCH_ASSOC)->fetchAll();
echo print_r($count_num_blogs);
//echo print_r($rows);
if( $count_num_blogs[0] >= $max_rows) {
$delete_statement = $db->prepare('DELETE FROM blog WHERE doc_id not in
(SELECT doc_id FROM blog ORDER BY doc_id DESC LIMIT ?);');
$delete_statement ->bindValue(1, $max_rows, SQLITE3_INTEGER);
$result = $delete_statement->execute();
}
//benchmark simulate multiple connections and multiple test using ab tool linux, 100 connections, 50 concurrently ;ApacheBench 100% 2seconds (longest request)
//sudo apt install apache2-utils
//ab -n 100 -c 50 https://example.com/sqlite.php
//https://www.php.net/manual/en/pdo.lobs.php
//close db
$db = null;
?>