Avertissement - certaines fonctionnalités de cette page nécessitent un navigateur ou un système d'exploitation plus récent que celui que vous utilisez actuellement. Si vous disposez d'un autre navigateur, ouvrez cette page avec.
Connect to a Database and run SQL Statements
Documentation API- 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 |
|---|---|---|---|---|---|---|---|---|
| 1426 | /fr/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com) | 2024-11-21 11:19:02 | ||
| 1425 | /en/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; SeznamBot/4.0; +http://napoveda.seznam.cz/seznambot-intro/) | 2024-11-21 05:49:01 | ||
| 1424 | /pt-BR/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/) | 2024-11-20 16:57:37 | ||
| 1423 | /en/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; SeznamBot/4.0; +http://napoveda.seznam.cz/seznambot-intro/) | 2024-11-20 13:59:57 | ||
| 1422 | /fr/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/) | 2024-11-20 12:42:23 | ||
| 1421 | /zh-CN/examples/database-demo | GET | Bot | Bot | Timpibot/0.9 (+http://www.timpi.io) | 2024-11-20 10:39:24 | ||
| 1420 | /es/examples/database-demo | GET | Bot | Bot | Timpibot/0.9 (+http://www.timpi.io) | 2024-11-20 10:39:19 | ||
| 1419 | /pt-BR/examples/database-demo | GET | Bot | Bot | Timpibot/0.9 (+http://www.timpi.io) | 2024-11-20 10:39:18 | ||
| 1418 | /ar/examples/database-demo | GET | Bot | Bot | Timpibot/0.9 (+http://www.timpi.io) | 2024-11-20 10:39:16 | ||
| 1417 | /fr/examples/database-demo | GET | Bot | Bot | Timpibot/0.9 (+http://www.timpi.io) | 2024-11-20 10:39:16 | ||
| 1416 | /en/examples/database-demo | GET | Bot | Bot | Timpibot/0.9 (+http://www.timpi.io) | 2024-11-20 10:39:16 | ||
| 1415 | /en/examples/database-demo | GET | Desktop | Windows | Windows 10 | Chrome | Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 | 2024-11-20 09:11:51 |
| 1414 | /en/examples/database-demo | GET | Desktop | Linux | Linux | Firefox | Mozilla/5.0 (X11; Linux x86_64; rv:132.0) Gecko/20100101 Firefox/132.0 | 2024-11-20 00:40:04 |
| 1413 | /fr/examples/database-demo | GET | YisouSpider | 2024-11-19 08:29:00 | ||||
| 1412 | /ar/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot) | 2024-11-19 04:59:30 | ||
| 1411 | /pt-BR/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot) | 2024-11-19 04:47:27 | ||
| 1410 | /zh-CN/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot) | 2024-11-19 04:47:00 | ||
| 1409 | /fr/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot) | 2024-11-19 04:42:08 | ||
| 1408 | /es/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot) | 2024-11-19 04:42:02 | ||
| 1407 | /es/examples/database-demo | GET | Bot | Bot | Mozilla/5.0 (compatible; SemrushBot/7~bl; +http://www.semrush.com/bot.html) | 2024-11-19 01:23:46 |
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;
}
}