PHP with SQLite using PDO
Had to throw together a fast and dirty database-backed system. Super easy with SQLite, which is built into most PHP builds. Not sure? Run this and look for --with-sqlite3 in the Configure Command section:
<?php
phpinfo();
Creating a database and setting up tables from the command line is easy:
$ sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
sqlite> .open /path/to/database.sqlite
sqlite> CREATE TABLE tag (uuid char(36) primary key, tag varchar(255));
sqlite> .tables
tag
sqlite> INSERT INTO tag (uuid, tag) VALUES('371A4A0D-C21E-42AD-854A-B48181D061AD', 'Litigation');
sqlite> SELECT * FROM tag;
371A4A0D-C21E-42AD-854A-B48181D061AD|Litigation
sqlite> create table article_tag( uuid text primary key, article_uuid text, tag_uuid text);
sqlite> create index atart on article_tag(article_uuid);
sqlite> create index attag on article_tag(tag_uuid);
sqlite> .quit
I realized after the fact that SQLite maps “regular” SQL data types (CHAR(36) etc) to its internal data types (TEXT); also, note you can specify the database you’re opening on the command line, instead of using .open:
$ sqlite3 ./database.sqlite
Accessing the database via PDO in PHP is simple; here’s a basic script that pulls from the database and lists the contents of a table:
<?php
require("UUID.php");
$db = new PDO('sqlite://path/to/database.sqlite');
$stmt = $db->prepare('SELECT uuid,tag FROM tag'); # WHERE meal LIKE ?');
$stmt->execute(); # (array($_POST['meal']));
$rows = $stmt->fetchAll();
if (count($rows) == 0) {
print "No tags.\n";
} else {
print "UUID \tTag\n";
foreach ($rows as $row) {
print $row[0] . "\t" . $row[1] . PHP_EOL;;
}
}
?>
Alternately, to use the column names in the foreach loop:
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
...
print $row['uuid'] . "\t" . $row['tag'] . PHP_EOL;
To insert data into the database, use the PDO transaction support; reads won’t be impacted, but other transactions will block until a current transaction is committed or rolled back:
$sql = "INSERT INTO tag (uuid, tag) VALUES(:uuid, :tag)";
$stmt = $db->prepare($sql);
$ins_uuid = "5ab76a57-5e91-48a8-9392-0d32fb417c34";
$ins_tag = "New tag value";
$stmt->bindParam(":uuid", $ins_uuid, PDO::PARAM_STR); #
$stmt->bindParam(":tag", $ins_tag, PDO::PARAM_STR);
try {
$db->beginTransaction();
$stmt->execute();
$db->commit();
} catch(\PDOException $e) {
die($e->getMessage());
}
Easy. A few things (it’s been a while since I did much of anything with PHP, and PDO is new to me, so maybe these are old news to the rest of the world?):
- If you want to use, e.g., a string literal, use ->bindValue() instead of ->bindParam() (which passes the variable by reference).
- PDO::PARAM constants defined.
SQLite lacks a date type but supports storing dates in a couple of formats, including an integer count of seconds since epoch. PHP makes these easy to work with, too:
$date = '11/28/21';
$dt = new DateTime($date);
$epoch = $dt->getTimestamp();
$display = date("Y-m-d", $epoch);
print "$date = $epoch = $display" . PHP_EOL;
Importing tab-separated data into SQLite:
# head -n 3 /nas/www/dogbeach/Book1.txt
uuid matter_type_uuid name sort_value active
a8e314e4-ee2b-4108-affa-1e65d68c4e05 c787e2fd-7a9e-4fae-afdd-33a6fccd3490 Potential New Client 0 1
f0f2ef0b-3ffb-42ae-b2a2-3eee8f5c97f5 52ee97ec-f80d-4d48-8fe1-e4b03be59162 Open 100 1
sqlite> .import /nas/www/dogbeach/Book1.txt matter_status
sqlite> select * from matter_status
uuid matter_type_uuid name sort_value active
a8e314e4-ee2b-4108-affa-1e65d68c4e05 c787e2fd-7a9e-4fae-afdd-33a6fccd3490 Potential New Client 0 1
...
Same deal with comma separated values:
sqlite> .mode csv
Output to a CSV file:
sqlite> .mode csv
sqlite> .output /Users/flying-geek/Documents/Aircraft.csv
sqlite> SELECT ...;
sqlite> .output stdout
Comments
Post a Comment