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 //VALIDATION AND DEFAULTS AND ERRORS //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); //htmlspecialchars( $filter_delete_doc_id = filter_input(INPUT_POST, 'delete_doc_id', FILTER_VALIDATE_INT); //htmlspecialchars( $filter_update_doc_id = filter_input(INPUT_POST, 'update_doc_id', FILTER_VALIDATE_INT); //htmlspecialchars( $filter_update_title = filter_input(INPUT_POST, 'update_doc_title', FILTER_SANITIZE_SPECIAL_CHARS); //HTML-encode '"<>& and characters if ( is_null($filter_delete_doc_id) == false) { $filter_id = 1; $delete_statement = $db->prepare('DELETE FROM blog WHERE doc_id = ?;'); $delete_statement ->bindValue(1, $filter_delete_doc_id, SQLITE3_INTEGER); $result = $delete_statement->execute(); } elseif (is_null($filter_update_doc_id) == false){ echo 'we hee but what'; if ( is_null($filter_update_title) || $filter_update_title=='' ) { $filter_update_title = 'Hi World'; } $update_statement = $db->prepare('UPDATE blog SET doc_title = ? WHERE doc_id = ?;'); $update_statement->bindParam(1, $filter_update_title); $update_statement->bindParam(2, $filter_update_doc_id); $result = $update_statement->execute(); } else { if ( is_null($filter_created_time) || $filter_created_time=='' ) { $filter_created_time = '1982-09-09T09:09'; echo 'thing null'; }else { echo $filter_created_time.'empty string?'; $filter_created_time = substr($filter_created_time,0,255); //truncate long text } if ( is_null($filter_title) || $filter_title=='' ) { $filter_title = 'Hi World'; }else { $filter_title = substr($filter_title,0,255); //truncate long text } if ( is_null($filter_text) || $filter_title=='' ) { $filter_text = 'Hows it going'; } else { $filter_text = substr($filter_text,0,65536); //truncate long text } //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 echo $filter_created_time; $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(isset($_FILES['doc_image']) && 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 . "-"; if ($bind_doc_id == $filter_update_doc_id ) { echo "

You Were here

"; } echo "
"; echo "-
" . htmlspecialchars($bind_doc_text) ."
". htmlspecialchars($bind_doc_created_time). "
"; echo ""; //retrieve image echo ""; 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; ?>