Warning - some features on this page require a newer Browser or OS than the one that you are currently using. If you have a different browser available then open this page with it.
Connect to a Database and run SQL Statements
API Documentation- This page uses a Database with SQL to save and show recent page views and HTTP requests.
- Use the controls to submit additional requests, or try this page with other browsers or dev tools to see different user agents.
- Database example code and the PHP code for this page is below the table.
- You can highlight a row on the table by clicking on it.
- Red
- Green
- Blue
- Yellow
Id | URL | Method | Device Type | OS Type | OS | Browser | User-Agent | Date Requested |
---|---|---|---|---|---|---|---|---|
749 | /en/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com) | 2024-07-27 06:50:35 | ||
748 | /pt-BR/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 2024-07-26 20:13:21 | ||
747 | /zh-CN/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 2024-07-26 20:12:37 | ||
746 | /fr/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 2024-07-26 20:12:08 | ||
745 | /es/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 2024-07-26 20:12:00 | ||
744 | /ar/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 2024-07-26 20:11:05 | ||
743 | /en/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 2024-07-26 20:08:21 | ||
742 | /pt-BR/examples/database-demo | GET | Bot | Android | Android 6.0.1 | Chrome | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | 2024-07-26 01:35:48 |
741 | /en/examples/database-demo | GET | Desktop | Windows | Windows 10 | Edge (Chromium) | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 Edg/126.0.0.0 | 2024-07-25 21:16:45 |
740 | /es/examples/database-demo | GET | Bot | Android | Android 6.0.1 | Chrome | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | 2024-07-25 19:35:44 |
739 | /pt-BR/examples/database-demo | GET | Bot | Android | Android 6.0.1 | Chrome | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | 2024-07-24 20:44:30 |
738 | /ar/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) | 2024-07-24 06:32:02 | ||
737 | /es/examples/database-demo | GET | Bot | Android | Android 6.0.1 | Chrome | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | 2024-07-24 04:05:03 |
736 | /en/examples/database-demo | GET | Bot | Android | Android 6.0.1 | Chrome | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | 2024-07-23 15:15:46 |
735 | /pt-BR/examples/database-demo | GET | Bot | Android | Android 6.0.1 | Chrome | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.6478.126 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) | 2024-07-23 13:44:46 |
734 | /fr/examples/database-demo | GET | meta-externalagent/1.1 (+https://developers.facebook.com/docs/sharing/webmasters/crawler) | 2024-07-23 11:07:26 | ||||
733 | /es/examples/database-demo | GET | Desktop | Mac | Mac OS X | Safari | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Version/8.0.2 Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) | 2024-07-22 15:30:25 |
732 | /fr/examples/database-demo | GET | Desktop | Mac | Mac OS X | Safari | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Version/8.0.2 Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) | 2024-07-22 15:30:17 |
731 | /ar/examples/database-demo | GET | Desktop | Mac | Mac OS X | Safari | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Version/8.0.2 Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) | 2024-07-22 15:30:09 |
730 | /en/examples/database-demo | GET | Desktop | Mac | Mac OS X | Safari | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_1) AppleWebKit/600.2.5 (KHTML, like Gecko) Version/8.0.2 Safari/600.2.5 (Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) | 2024-07-22 15:30:01 |
Database Code Overview
// Connect to a Database
$db = new \FastSitePHP\Data\Database($dsn, $user, $password);
// Query for Multiple Records
// Returns an Array of Records (Associative Array for each Record).
$sql = 'SELECT * FROM pages';
$records = $db->query($sql);
// Query for one record. Returns an Associative Array or [null] if not found.
// Both [query()] and [queryOne()] support optional parameters when querying.
$sql = 'SELECT * FROM pages WHERE id = ?';
$params = [1];
$record = $db->queryOne($sql, $params);
// Use [execute()] for INSERT, UPDATE, DELETE, CREATE and other Transactions.
// In addition to using [?] you can also used named parameters in the
// format of ":name". Named parameters can make the code easier to read.
$sql = 'INSERT INTO pages (title, content)';
$sql .= ' VALUES (:title, :content)';
$params = [
'title' => 'Database Demo',
'content' => '<h1>Database Demo<h1>',
];
$rows_affected = $db->execute($sql, $params);
// Many additional examples can be found on the quick reference page.
PHP Code for this Page
<?php
namespace App\Controllers\Examples;
use FastSitePHP\Application;
use FastSitePHP\Data\Database;
use FastSitePHP\Lang\I18N;
use FastSitePHP\Web\Request;
class DatabaseDemo
{
/**
* Return HTML for the Web Page
*/
public function get(Application $app, $lang)
{
// Load Language File
I18N::langFile('database-demo', $lang);
// Add Code Example from text file
$file_path = $app->config['I18N_DIR'] . '/code/database-demo.{lang}.txt';
$app->locals['i18n']['db_code'] = I18N::textFile($file_path, $app->lang);
// Add a record for the request and get the 20 most recent records
$records = $this->insertAndSelectRecords($app);
// Render the View
$templates = [
'old-browser-warning.htm',
'examples/database-demo.php',
'examples/database-demo.htm',
'table-highlighter.htm',
];
return $app->render($templates, [
'nav_active_link' => 'examples',
'records' => $records,
'controller_code' => file_get_contents(__FILE__),
]);
}
/**
* JSON Service that logs requests from button clicks.
* Returns the 20 most recent records.
*/
public function routePage(Application $app, $lang, $color)
{
return ['records' => $this->insertAndSelectRecords($app)];
}
/**
* Add a record for the request and return recent records
*/
private function insertAndSelectRecords(Application $app)
{
$db = $this->connectToDb();
$this->insertRecord($app, $db);
$records = $this->getRecentRecords($db);
return $records;
}
/**
* Return a Db Connection to a SQLite Database in the Temp
* Directory. Create the file if it doesn't yet exist.
*/
private function connectToDb()
{
// Path to SQLite Db
$path = sys_get_temp_dir() . '/database-demo.sqlite';
$this->checkDb($path);
// Connect and create table the first time the db is used
$dsn = 'sqlite:' . $path;
$db = new Database($dsn);
$sql = <<<'SQL'
CREATE TABLE IF NOT EXISTS requests (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url TEXT,
method TEXT,
user_agent TEXT,
date_requested TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
SQL;
$db->execute($sql);
return $db;
}
/**
* Delete the SQLite Database every time it reaches over 10 megabytes.
* It is intended only as a temporary database for this demo page.
*/
private function checkDb($path) {
if (is_file($path)) {
$ten_megabytes = (1024 * 1024 * 10);
$file_size = filesize($path);
if ($file_size > $ten_megabytes) {
unlink($path);
// Add to a log file each time it's removed
$log_path = sys_get_temp_dir() . '/database-demo.txt';
$now = date(DATE_RFC2822);
$contents = "${path} deleted at ${now}\n";
file_put_contents($log_path, $contents, FILE_APPEND);
}
}
}
/**
* Insert a Record for each Request
*/
private function insertRecord(Application $app, Database $db)
{
$req = new Request();
$sql = 'INSERT INTO requests (url, method, user_agent) VALUES (?, ?, ?)';
$params = [$app->requestedPath(), $req->method(), $req->userAgent()];
$db->execute($sql, $params);
}
/**
* Return the 20 most recent records.
* The SELECT statement is defined in a [*.sql] file,
* which allows for easy editing and testing outside of PHP code.
*/
private function getRecentRecords(Database $db)
{
$sql = file_get_contents(__DIR__ . '/../../Models/DatabaseDemo.sql');
$records = $db->query($sql);
return $records;
}
}