SQLite and PHP primer

sqlite3SQLite (3.6+) is a database supporting a SQL-compatible syntax and does not rely on a server, rather it stores data on files. Here we’ll prime how to use SQLite from command line and how to integrate it with PHP (5.3+).

Command line operations

Invoking “sqlite3 databasefilename” will fire up SQLite, and create the file if not already there. The prompt will resemble the MySQL one with notable exceptions. From here you can type SQL command for CRUD operations, like:

CREATE TABLE test (id integer primary key, name varchar(200));
INSERT INTO test (name) VALUES ('Hello world');

Other commands are quite different if you are used to MySQL, and starts with a dot like:

.tables [tablenames]
.schema tablename

Autoincrement with care

By default there’s a sort-of auto increment column (ROWID), that works as expected except that does not prevent an ID to be re-used once that a record is deleted. With this in mind one can save CPU time as explained here.

Using SQLite with PHP via library

Bill Weinman made an object oriented library that I tried and have been quite happy with. You can find it here. Here an almost working example:

$db = new bwSQLite3($databaseFile, $table);
$db->sql_do("create table $table (id integer primary key, itemname text)");
$db->sql_do("insert into $table (itemname) VALUES ('?')", $item1);

More to come.