SQLite3 with PHP; Creating a database and connecting to it.

SQLite is a serverless, self-contained database that in practice works straight out of the box. With no need to install or run to access and use.

The SQLite library is 250KB in size compared to 650MB for the MySQL server package.

SQLite is used in programs and mobile apps/games to store data as it is an embedded database.

Its downside comes with using large databases as SQLite can only have a single write operation going at any one time.

SQLite3 with PHP

Since PHP 5.3.0 SQLite3 comes bundled and enabled with PHP as default.  This means it does not need to be installed or linked to use.

The easiest way to create a new SQLite database is to create a PDO connection to what you want the database called and it will create it if it does not exist.

$db = new PDO('sqlite:test_database.db', '', '', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
));

This will create test_database.db

If you want your database in a subfolder:

$db = new PDO('sqlite:/db/test_database.db', '', '', array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ));

Once the database has been created/exists the above code is now used as the connection, with this you can create tables:

$db->exec("CREATE TABLE IF NOT EXISTS mytable (
    id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);");

And insert data:

$db->exec("INSERT INTO mytable (id, name) VALUES (1, 'Mike')");

Or run any PDO SQLite commands.