Conectar a um banco de dados e executar instruções SQL

Documentação da API
  • Vermelho
  • Verde
  • Azul
  • Amarelo
Id URL Método Tipo de Dispositivo Tipo de SO SO Navegador User-Agent Data Requisitado
267 /pt-BR/examples/database-demo GET Bot Bot CCBot/2.0 (https://commoncrawl.org/faq/) 2023-06-08 04:43:35
266 /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/114.0.5735.90 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2023-06-07 20:48:20
265 /ar/examples/database-demo GET Desktop Mac Mac OS X Safari Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.0.3 Safari/605.1.15 2023-06-07 19:40:38
264 /en/examples/database-demo GET Bot Bot Google Bot Bot Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2023-06-07 11:42:36
263 /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/114.0.5735.90 Mobile Safari/537.36 (compatible; Googlebot/2.1; +http://www.google.com/bot.html) 2023-06-07 10:12:35
262 /es/examples/database-demo/yellow PUT Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:37:00
261 /es/examples/database-demo/yellow PUT Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:59
260 /es/examples/database-demo/yellow DELETE Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:57
259 /es/examples/database-demo/blue DELETE Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:56
258 /es/examples/database-demo/blue DELETE Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:54
257 /es/examples/database-demo/blue GET Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:51
256 /es/examples/database-demo/green GET Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:48
255 /es/examples/database-demo/red POST Desktop Windows Windows 10 Edge (Chromium) Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:43
254 /es/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/114.0.0.0 Safari/537.36 Edg/114.0.1823.37 2023-06-07 06:36:21
253 /zh-CN/examples/database-demo GET Bot Bot Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) 2023-06-06 20:41:44
252 /pt-BR/examples/database-demo GET Bot Bot Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) 2023-06-06 20:39:29
251 /fr/examples/database-demo GET Bot Bot Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) 2023-06-06 20:37:02
250 /es/examples/database-demo GET Bot Bot Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) 2023-06-06 20:35:20
249 /en/examples/database-demo GET Bot Bot Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) 2023-06-06 20:33:17
248 /ar/examples/database-demo GET Bot Bot Mozilla/5.0 (compatible; MJ12bot/v1.4.8; http://mj12bot.com/) 2023-06-06 20:29:58

Visão geral do código do banco de dados

// Conecte a um Banco de Dados
$db = new \FastSitePHP\Data\Database($dsn, $user, $password);

// Consulta para Múltiplos Registros
// Retorna um Array de Registros (Array Associativo para cada Registro).
$sql = 'SELECT * FROM pages';
$records = $db->query($sql);

// Consulta para um registro. Retorna um Array Associativo ou [null] se não
// encontrado. Ambas [query()] e [queryOne()] suportam parâmetros opcionais ao
// consultar.
$sql = 'SELECT * FROM pages WHERE id = ?';
$params = [1];
$record = $db->queryOne($sql, $params);

// Utiliza [execute()] para INSERT, UPDATE, DELETE, CREATE e outras Transações.
// Além de utilizar [?] você pode também utilizar parâmetros nomeados no formato
// ":name". Parâmetros nomeados capo tornar o código mais fácil de ler.
$sql = 'INSERT INTO pages (title, content)';
$sql .= ' VALUES (:title, :content)';
$params = [
    'title'   => 'Banco de Dados de Demonstração',
    'content' => '<h1>Banco de Dados de Demonstração<h1>',
];
$rows_affected = $db->execute($sql, $params);

// Muitos exemplos adicionais pode ser encontrados na página Referência Rápida.

Código PHP para esta página

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