Skip to content

Instantly share code, notes, and snippets.

@ehthxC7x9D

ehthxC7x9D/SQLite-PHP-quickstart.php

Forked from bladeSk/SQLite-PHP-quickstart.php
Created March 2, 2024 17:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ehthxC7x9D/bc3a1b06af7877a7f9d60641407396ba to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ehthxC7x9D/bc3a1b06af7877a7f9d60641407396ba to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @bladeSk bladeSk revised this gist Jun 11, 2022. 1 changed file with 5 additions and 2 deletions.
    7 changes: 5 additions & 2 deletions SQLite-PHP-quickstart.php
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,9 @@
    // The extension of the file is arbitrary.
    $db = new SQLite3('analytics.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);

    // Errors are emitted as warnings by default, enable proper error handling.
    $db->enableExceptions(true);


    // Create a table.

    @@ -20,11 +23,11 @@

    // Insert some sample data.
    //
    // INSERTs may seem very slow in SQLite, which happens when not using transactions.
    // It's advisable to wrap related queries in a transaction (BEGIN and COMMIT),
    // even if you don't care about atomicity.
    // If you don't do this, SQLite automatically wraps every single query
    // in a transaction, which slows down everything immensely. If you're new to SQLite,
    // you may be surprised why the INSERTs are so slow.
    // in a transaction, which slows everything down immensely.

    $db->exec('BEGIN');
    $db->query('INSERT INTO "visits" ("user_id", "url", "time")
  2. @bladeSk bladeSk revised this gist Apr 25, 2017. No changes.
  3. @bladeSk bladeSk created this gist Apr 25, 2017.
    100 changes: 100 additions & 0 deletions SQLite-PHP-quickstart.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,100 @@
    <?php
    // This file walks you through the most common features of PHP's SQLite3 API.
    // The code is runnable in its entirety and results in an `analytics.sqlite` file.


    // Create a new database, if the file doesn't exist and open it for reading/writing.
    // The extension of the file is arbitrary.
    $db = new SQLite3('analytics.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);


    // Create a table.

    $db->query('CREATE TABLE IF NOT EXISTS "visits" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "user_id" INTEGER,
    "url" VARCHAR,
    "time" DATETIME
    )');


    // Insert some sample data.
    //
    // It's advisable to wrap related queries in a transaction (BEGIN and COMMIT),
    // even if you don't care about atomicity.
    // If you don't do this, SQLite automatically wraps every single query
    // in a transaction, which slows down everything immensely. If you're new to SQLite,
    // you may be surprised why the INSERTs are so slow.

    $db->exec('BEGIN');
    $db->query('INSERT INTO "visits" ("user_id", "url", "time")
    VALUES (42, "/test", "2017-01-14 10:11:23")');
    $db->query('INSERT INTO "visits" ("user_id", "url", "time")
    VALUES (42, "/test2", "2017-01-14 10:11:44")');
    $db->exec('COMMIT');


    // Insert potentially unsafe data with a prepared statement.
    // You can do this with named parameters:

    $statement = $db->prepare('INSERT INTO "visits" ("user_id", "url", "time")
    VALUES (:uid, :url, :time)');
    $statement->bindValue(':uid', 1337);
    $statement->bindValue(':url', '/test');
    $statement->bindValue(':time', date('Y-m-d H:i:s'));
    $statement->execute(); // you can reuse the statement with different values


    // Fetch today's visits of user #42.
    // We'll use a prepared statement again, but with numbered parameters this time:

    $statement = $db->prepare('SELECT * FROM "visits" WHERE "user_id" = ? AND "time" >= ?');
    $statement->bindValue(1, 42);
    $statement->bindValue(2, '2017-01-14');
    $result = $statement->execute();

    echo("Get the 1st row as an associative array:\n");
    print_r($result->fetchArray(SQLITE3_ASSOC));
    echo("\n");

    echo("Get the next row as a numeric array:\n");
    print_r($result->fetchArray(SQLITE3_NUM));
    echo("\n");

    // If there are no more rows, fetchArray() returns FALSE.

    // free the memory, this in NOT done automatically, while your script is running
    $result->finalize();


    // A useful shorthand for fetching a single row as an associative array.
    // The second parameter means we want all the selected columns.
    //
    // Watch out, this shorthand doesn't support parameter binding, but you can
    // escape the strings instead.
    // Always put the values in SINGLE quotes! Double quotes are used for table
    // and column names (similar to backticks in MySQL).

    $query = 'SELECT * FROM "visits" WHERE "url" = \'' .
    SQLite3::escapeString('/test') .
    '\' ORDER BY "id" DESC LIMIT 1';

    $lastVisit = $db->querySingle($query, true);

    echo("Last visit of '/test':\n");
    print_r($lastVisit);
    echo("\n");


    // Another useful shorthand for retrieving just one value.

    $userCount = $db->querySingle('SELECT COUNT(DISTINCT "user_id") FROM "visits"');

    echo("User count: $userCount\n");
    echo("\n");


    // Finally, close the database.
    // This is done automatically when the script finishes, though.

    $db->close();