Us2Pk3XD

1<?php
2// This file walks you through the most common features of PHP's SQLite3 API.
3// The code is runnable in its entirety and results in an `analytics.sqlite` file.
4
5
6// Create a new database, if the file doesn't exist and open it for reading/writing.
7// The extension of the file is arbitrary.
8$db = new SQLite3('analytics.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);
9
10
11// Create a table.
12
13$db->query('CREATE TABLE IF NOT EXISTS "visits" (
14    "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
15    "user_id" INTEGER,
16    "url" VARCHAR,
17    "time" DATETIME
18)');
19
20
21// Insert some sample data.
22//
23// It's advisable to wrap related queries in a transaction (BEGIN and COMMIT),
24// even if you don't care about atomicity.
25// If you don't do this, SQLite automatically wraps every single query
26// in a transaction, which slows down everything immensely. If you're new to SQLite,
27// you may be surprised why the INSERTs are so slow.
28
29$db->exec('BEGIN');
30$db->query('INSERT INTO "visits" ("user_id", "url", "time")
31    VALUES (42, "/test", "2017-01-14 10:11:23")');
32$db->query('INSERT INTO "visits" ("user_id", "url", "time")
33    VALUES (42, "/test2", "2017-01-14 10:11:44")');
34$db->exec('COMMIT');
35
36
37// Insert potentially unsafe data with a prepared statement.
38// You can do this with named parameters:
39
40$statement = $db->prepare('INSERT INTO "visits" ("user_id", "url", "time")
41    VALUES (:uid, :url, :time)');
42$statement->bindValue(':uid', 1337);
43$statement->bindValue(':url', '/test');
44$statement->bindValue(':time', date('Y-m-d H:i:s'));
45$statement->execute(); // you can reuse the statement with different values
46
47
48// Fetch today's visits of user #42.
49// We'll use a prepared statement again, but with numbered parameters this time:
50
51$statement = $db->prepare('SELECT * FROM "visits" WHERE "user_id" = ? AND "time" >= ?');
52$statement->bindValue(1, 42);
53$statement->bindValue(2, '2017-01-14');
54$result = $statement->execute();
55
56echo("Get the 1st row as an associative array:\n");
57print_r($result->fetchArray(SQLITE3_ASSOC));
58echo("\n");
59
60echo("Get the next row as a numeric array:\n");
61print_r($result->fetchArray(SQLITE3_NUM));
62echo("\n");
63
64// If there are no more rows, fetchArray() returns FALSE.
65
66// free the memory, this in NOT done automatically, while your script is running
67$result->finalize();
68
69
70// A useful shorthand for fetching a single row as an associative array.
71// The second parameter means we want all the selected columns.
72//
73// Watch out, this shorthand doesn't support parameter binding, but you can
74// escape the strings instead.
75// Always put the values in SINGLE quotes! Double quotes are used for table
76// and column names (similar to backticks in MySQL).
77
78$query = 'SELECT * FROM "visits" WHERE "url" = \'' .
79    SQLite3::escapeString('/test') .
80    '\' ORDER BY "id" DESC LIMIT 1';
81
82$lastVisit = $db->querySingle($query, true);
83
84echo("Last visit of '/test':\n");
85print_r($lastVisit);
86echo("\n");
87
88
89// Another useful shorthand for retrieving just one value.
90
91$userCount = $db->querySingle('SELECT COUNT(DISTINCT "user_id") FROM "visits"');
92
93echo("User count: $userCount\n");
94echo("\n");
95
96
97// Finally, close the database.
98// This is done automatically when the script finishes, though.
99
100$db->close();