FastSitePHP | Connect to a Database and run SQL Statements

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.

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
2456	/en/examples/database-demo	GET	Bot	Bot			claudebot	2024-04-16 23:17:05
2455	/fr/examples/database-demo	GET	Bot	Bot	Bing Bot	Chrome	Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm) Chrome/116.0.1938.76 Safari/537.36	2024-04-16 21:54:39
2454	/zh-CN/examples/database-demo	GET						2024-04-16 17:24:13
2453	/zh-CN/examples/database-demo	HEAD						2024-04-16 17:24:12
2452	/ar/examples/database-demo	GET						2024-04-16 17:23:22
2451	/ar/examples/database-demo	HEAD						2024-04-16 17:23:22
2450	/pt-BR/examples/database-demo	GET						2024-04-16 17:22:30
2449	/pt-BR/examples/database-demo	HEAD						2024-04-16 17:22:30
2448	/fr/examples/database-demo	GET						2024-04-16 17:21:39
2447	/fr/examples/database-demo	HEAD						2024-04-16 17:21:39
2446	/es/examples/database-demo	GET						2024-04-16 17:20:49
2445	/es/examples/database-demo	HEAD						2024-04-16 17:20:49
2444	/en/examples/database-demo	GET						2024-04-16 17:08:35
2443	/en/examples/database-demo	HEAD						2024-04-16 17:08:35
2442	/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 Safari/537.36 Vivaldi/5.3.2679.68	2024-04-15 15:32:54
2441	/zh-CN/examples/database-demo	GET						2024-04-15 09:02:51
2440	/zh-CN/examples/database-demo	HEAD						2024-04-15 09:02:51
2439	/ar/examples/database-demo	GET						2024-04-15 09:01:58
2438	/ar/examples/database-demo	HEAD						2024-04-15 09:01:58
2437	/pt-BR/examples/database-demo	GET						2024-04-15 09:01:10

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