Connect to a Database and run SQL Statements

وثائق 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
185	/ar/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)	2025-02-22 17:15:44
184	/en/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)	2025-02-22 16:58:54
183	/fr/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 (compatible; SemrushBot/7~bl; +http://www.semrush.com/bot.html)	2025-02-22 09:32:05
182	/en/examples/database-demo/red	GET	Desktop	Windows	Windows 10	Chrome	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36	2025-02-22 09:14:58
181	/en/examples/database-demo	GET	Desktop	Windows	Windows 10	Chrome	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Safari/537.36	2025-02-22 09:14:33
180	/pt-BR/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)	2025-02-22 08:53:56
179	/fr/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)	2025-02-22 08:28:51
178	/es/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/)	2025-02-22 02:51:27
177	/en/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; PerplexityBot/1.0; +https://perplexity.ai/perplexitybot)	2025-02-21 23:43:50
176	/es/examples/database-demo	GET	Mobile	Android	Android 10	Chrome	Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/133.0.0.0 Mobile Safari/537.36	2025-02-21 17:33:27
175	/es/examples/database-demo	GET	Desktop	Windows	Windows 7	Firefox	Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.2.8) Gecko/20100722 Firefox/3.6.8 ( .NET CLR 3.5.30729; .NET4.0C)	2025-02-21 11:09:01
174	/pt-BR/examples/database-demo	GET	Desktop	Mac	Mac OS X	Chrome	Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.166 Safari/535.19	2025-02-21 09:47:17
173	/zh-CN/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/)	2025-02-21 05:34:29
172	/en/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 (compatible; SeznamBot/4.0; +https://o-seznam.cz/napoveda/vyhledavani/en/seznambot-crawler/)	2025-02-21 03:28:25
171	/pt-BR/examples/database-demo	GET	Mobile	Android	Android 7.0	Webkit	Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot)	2025-02-21 00:56:16
170	/es/examples/database-demo	GET	Bot	Bot			Mozilla/5.0 (compatible; SemrushBot/7~bl; +http://www.semrush.com/bot.html)	2025-02-20 22:43:54
169	/en/examples/database-demo	GET	Desktop	Windows	Windows 10	Chrome	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 YaBrowser/22.7.0 Yowser/2.5 Safari/537.36	2025-02-20 11:11:49
168	/ar/examples/database-demo	GET	Desktop	Windows	Windows 10	Chrome	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 YaBrowser/22.7.0 Yowser/2.5 Safari/537.36	2025-02-20 11:11:31
167	/zh-CN/examples/database-demo	GET				Safari	Mozilla/5.0 (Macintosh; U; PPC Mac OS X; de-de) AppleWebKit/85.7 (KHTML, like Gecko) Safari/85.7	2025-02-20 10:50:22
166	/fr/examples/database-demo	GET	Bot	Bot		Chrome	Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; Amazonbot/0.1; +https://developer.amazon.com/support/amazonbot) Chrome/119.0.6045.214 Safari/537.36	2025-02-20 08:05:36

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;
    }
}