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 );');
//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_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_title) ) {
$filter_title = 'Hi World';
}
if ( is_null($filter_text) ) {
$filter_text = 'Hows it going';
}
echo 'hi'.$filter_id;
echo $filter_title;
echo $filter_text;
//INSERT OR ADD VALUES
$qry = $db->prepare( 'INSERT INTO blog (doc_title, doc_text) VALUES (?,?)');
$qry->bindValue(1, $filter_title, SQLITE3_TEXT);
$qry->bindValue(2, $filter_text, SQLITE3_TEXT);
$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!!
if(isset($_FILES['doc_image'])) {
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 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->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 . "
";
//retrieve image
echo "![](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;
?>