Ebooks

PHP SQLite3 tutorial

This is a PHP programming tutorial for the SQLite version 3 database. It covers the basics of SQLite programming with the PHP language.

To work with this tutorial, we must have PHP CLI installed on the system.

For working with the SQLite database, we can install the sqlite3 command line tool or the SQLite browser GUI.

$ php -v
PHP 7.2.11 (cli) (built: Oct 10 2018 02:39:52) ( ZTS MSVC15 (Visual C++ 2017) x86 )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies

In this tutorial, we use PHP 7.2.11.

...
;extension=sockets
extension=sqlite3
;extension=tidy
...

SQLite ships with PHP; we do not need to install it. We must enable the sqlite3 extension in the php.ini file.

SQLite

SQLite is an embedded relational database engine. The documentation calls it a self-contained, serverless, zero-configuration and transactional SQL database engine. It is very popular with hundreds of millions copies worldwide in use today. Several programming languages have built-in support for SQLite including PHP and Python.

Creating SQLite database

We use the sqlite3 command line tool to create a new database.

$ sqlite3 test.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite>

We provide a parameter to the sqlite3 tool; the test.db is the database name. It is a file on our disk. If it is present, it is opened. If not, it is created.

sqlite> .tables
sqlite> .exit
$ ls
test.db

The .tables command gives a list of tables in the test.db database. There are currently no tables. The .exit command terminates the interactive session of the sqlite3 command line tool. The ls command shows the contents of the current working directory. We can see the test.db file. All data will be stored in this single file.

PHP SQLite3 version example

In the following examples, we get the version of the SQLite database.

version.php
<?php

$ver = SQLite3::version();

echo $ver['versionString'] . "\n";
echo $ver['versionNumber'] . "\n";

var_dump($ver);

The SQLite3::version() returns the version of the SQLite database.

$ php version.php
3.20.1
3020001
array(2) {
    ["versionString"]=>
    string(6) "3.20.1"
    ["versionNumber"]=>
    int(3020001)
}

This is the output.

version2.php
<?php

$db = new SQLite3('test.db');

$version = $db->querySingle('SELECT SQLITE_VERSION()');

echo $version . "\n";

The program returns the current version of the SQLite database. This time we have executed the SELECT SQLITE_VERSION() statement.

$db = new SQLite3('test.db');

We create an SQLite3 object and open an SQLite3 database connection.

$version = $db->querySingle('SELECT SQLITE_VERSION()');

The querySingle() executes a query and returns a single result.

$ php version2.php
3.20.1

This is the output.

PHP SQLite3 exec

The exec() executes a result-less query against a given database.

create_table.php
<?php

$db = new SQLite3('test.db');

$db->exec("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INT)");
$db->exec("INSERT INTO cars(name, price) VALUES('Audi', 52642)");
$db->exec("INSERT INTO cars(name, price) VALUES('Mercedes', 57127)");
$db->exec("INSERT INTO cars(name, price) VALUES('Skoda', 9000)");
$db->exec("INSERT INTO cars(name, price) VALUES('Volvo', 29000)");
$db->exec("INSERT INTO cars(name, price) VALUES('Bentley', 350000)");
$db->exec("INSERT INTO cars(name, price) VALUES('Citroen', 21000)");
$db->exec("INSERT INTO cars(name, price) VALUES('Hummer', 41400)");
$db->exec("INSERT INTO cars(name, price) VALUES('Volkswagen', 21600)");

The program creates a cars table and inserts eight rows into the table.

$db->exec("CREATE TABLE cars(id INTEGER PRIMARY KEY, name TEXT, price INT)");

This SQL statement creates a new cars table. The table has three columns. Note that in SQLite database, INTEGER PRIMARY KEY column is auto-incremented.

$db->exec("INSERT INTO cars(name, price) VALUES('Audi', 52642)");
$db->exec("INSERT INTO cars(name, price) VALUES('Mercedes', 57127)");

These two lines insert two cars into the table.

sqlite> .mode column
sqlite> .headers on

We verify the written data with the sqlite3 tool. First we modify the way the data is displayed in the console. We use the column mode and turn on the headers.

sqlite> select * from cars;
id          name        price
----------  ----------  ----------
1           Audi        52642
2           Mercedes    57127
3           Skoda       9000
4           Volvo       29000
5           Bentley     350000
6           Citroen     21000
7           Hummer      41400
8           Volkswagen  21600

This is the data that we have written to the cars table.

PHP SQLite3 lastInsertRowID

Sometimes, we need to determine the Id of the last inserted row. In PHP SQLite3, we use the lastInsertRowID() method.

last_rowid.php
<?php

$db = new SQLite3(':memory:');

$db->exec("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)");
$db->exec("INSERT INTO friends(name) VALUES ('Tom')");
$db->exec("INSERT INTO friends(name) VALUES ('Rebecca')");
$db->exec("INSERT INTO friends(name) VALUES ('Jim')");
$db->exec("INSERT INTO friends(name) VALUES ('Robert')");

$last_row_id = $db->lastInsertRowID();

echo "The last inserted row Id is $last_row_id";

We create a friends table in memory. The Id is automatically incremented.

$db->exec("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)");

In SQLite3, INTEGER PRIMARY KEY column is auto incremented. There is also an AUTOINCREMENT keyword. When used in INTEGER PRIMARY KEY AUTOINCREMENT a slightly different algorithm for Id creation is used.

$db->exec("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)");
$db->exec("INSERT INTO friends(name) VALUES ('Tom')");
$db->exec("INSERT INTO friends(name) VALUES ('Rebecca')");
$db->exec("INSERT INTO friends(name) VALUES ('Jim')");
$db->exec("INSERT INTO friends(name) VALUES ('Robert')");

When using auto-increment, we have to explicitly state the column names, omitting the one that is auto-incremented. The four statements insert four rows into the friends table.

$last_row_id = $db->lastInsertRowID();

Using the lastInsertRowID() we get the last inserted row Id.

$ php last_rowid.php
The last inserted row Id is 4

We see the output of the program.

PHP SQLite3 query

The query() method executes an SQL query and returns a result object.

fetch_all.php
<?php

$db = new SQLite3('test.db');

$res = $db->query('SELECT * FROM cars');

while ($row = $res->fetchArray()) {
    echo "{$row['id']} {$row['name']} {$row['price']} \n";
}

The example retrieves all data from the cars table.

$res = $db->query('SELECT * FROM cars');

This SQL statement selects all data from the cars table.

while ($row = $res->fetchArray()) {

The fetchall() retrieves a result row as an associative or numerically indexed array or both (the default is both). It returns false if there are no more rows.

$ php fetch_all.php
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

This is the output of the example.

PHP SQLite3 escapeString

The escapeString() returns a string that has been properly escaped.

escape_string.php
<?php

$db = new SQLite3('test.db');

$sql = "SELECT name FROM cars WHERE name = 'Audi'";

$escaped = SQLite3::escapeString($sql);

var_dump($sql);
var_dump($escaped);

The example escapes a string in a query.

$ php escape_string.php
string(41) "SELECT name FROM cars WHERE name = 'Audi'"
string(43) "SELECT name FROM cars WHERE name = ''Audi''"

This is the output of the example.

PHP SQLite3 parameterized statements

SQL statements are often dynamically built. A user provides some input and this input is built into the statement. We must be cautious every time we deal with an input from a user. It has some serious security implications. The recommended way to dynamically build SQL statements is to use parameter binding.

Parameterized queries are created with prepare(); it prepares an SQL statement for execution and returns a statement object.

PHP SQLite3 has bindParam() and bindValue() method to bind values to placeholders. It allows to bind data to question mark or named placeholders.

Parameterized statements with question marks

In the first example we use the syntax of question marks.

prepared.php
<?php

$db = new SQLite3('test.db');

$stm = $db->prepare('SELECT * FROM cars WHERE id = ?');
$stm->bindValue(1, 3, SQLITE3_INTEGER);

$res = $stm->execute();

$row = $res->fetchArray(SQLITE3_NUM);
echo "{$row[0]} {$row[1]} {$row[2]}";

We select a car using question mark placeholder.

$stm = $db->prepare('SELECT * FROM cars WHERE id = ?');

The question marks ? are placeholders for values. The values are later added (bound) to the placeholders.

$stm->bindValue(1, 3, SQLITE3_INTEGER);

With bindValue() we bind value 3 to the question mark placeholder. The first argument is the positional parameter, identifying the placeholder (there can be multiple question mark placeholders).

$ php prepared.php
3 Skoda 9000

This is the output.

Parameterized statements with named placeholders

The second example uses parameterized statements with named placeholders.

prepared2.php
<?php

$db = new SQLite3('test.db');

$stm = $db->prepare('SELECT * FROM cars WHERE id = :id');
$stm->bindValue(':id', 1, SQLITE3_INTEGER);

$res = $stm->execute();

$row = $res->fetchArray(SQLITE3_NUM);
echo "{$row[0]} {$row[1]} {$row[2]}";

We select a specific car using a named placeholder.

$stm = $db->prepare('SELECT * FROM cars WHERE id = :id');

The named placeholders start with a colon character.

PHP SQLite3 bind_param

The bind_param() binds a parameter to a statement variable. It can be used to handle multiple rows.

bind_param.php
≪?php

$db = new SQLite3(':memory:');

$db->exec("CREATE TABLE friends(id INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT)");

$stm = $db->prepare("INSERT INTO friends(firstname, lastname) VALUES (?, ?)");
$stm->bindParam(1, $firstName);
$stm->bindParam(2, $lastName);

$firstName = 'Peter';
$lastName = 'Novak';
$stm->execute();

$firstName = 'Lucy';
$lastName = 'Brown';
$stm->execute();

$res = $db->query('SELECT * FROM friends');

while ($row = $res->fetchArray()) {
    echo "{$row[0]} {$row[1]} {$row[2]}\n";
}

In the example, we insert two rows into a table with a parameterized statement. To bind the placeholders, we use the bind_param() method.

$ php bind_param.php
1 Peter Novak
2 Lucy Brown

This is the output.

PHP SQLite3 metadata

Metadata is information about the data in the database. Metadata in a SQLite contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is a metadata. Number of rows and columns returned in a result set belong to metadata as well.

Metadata in SQLite can be obtained using specific PHP SQLite3 methods, PRAGMA command, or by querying the SQLite system sqlite_master table.

num_of_columns.php
<?php

$db = new SQLite3('test.db');

$res = $db->query("SELECT * FROM cars WHERE id = 1");
$cols = $res->numColumns();

echo "There are {$cols} columns in the result set\n";

The numColumns() returns the number of columns in the result set.

$ php num_of_columns.php
There are 3 columns in the result set

This is the output.

column_names.php
<?php

$db = new SQLite3('test.db');

$res = $db->query("PRAGMA table_info(cars)");

while ($row = $res->fetchArray(SQLITE3_NUM)) {
    echo "{$row[0]} {$row[1]} {$row[2]}\n";
}

In this example, we issue the PRAGMA table_info(tableName) command to get some metadata info about our cars table.

$res = $db->query("PRAGMA table_info(cars)");

The PRAGMA table_info(tableName) command returns one row for each column in the cars table. Columns in the result set include the column order number, column name, data type, whether or not the column can be NULL, and the default value for the column.

while ($row = $res->fetchArray(SQLITE3_NUM)) {
    echo "{$row[0]} {$row[1]} {$row[2]}\n";
}

From the provided information, we print the column order number, column name, and column data type.

$ php column_names.php
0 id INTEGER
1 name TEXT
2 price INT

This is the output of the example.

In the following example we print all rows from the cars table with their column names.

column_names2.php
<?php

$db = new SQLite3('test.db');

$res = $db->query("SELECT * FROM cars");

$col1 = $res->columnName(1);
$col2 = $res->columnName(2);

$header = sprintf("%-10s %s\n", $col1, $col2);
echo $header;

while ($row = $res->fetchArray()) {

    $line = sprintf("%-10s %s\n", $row[1], $row[2]);
    echo $line;
}

We print the contents of the cars table to the console with the names of the columns too. The records are aligned with the column names.

$col1 = $res->columnName(1);

The columnName() returns the name of the nth column.

$header = sprintf("%-10s %s\n", $col1, $col2);
echo $header;

These lines print two column names of the cars table.

while ($row = $res->fetchArray()) {

    $line = sprintf("%-10s %s\n", $row[1], $row[2]);
    echo $line;
}

We print the rows using the while loop. The data is aligned with the column names.

$ php column_names2.php
name       price
Audi       52642
Mercedes   57127
Skoda      9000
Volvo      29000
Bentley    350000
Citroen    21000
Hummer     41400
Volkswagen 21600

This is the output.

In our next example, we list all tables in the test.db database.

list_tables.php
<?php

$db = new SQLite3('test.db');

$res = $db->query("SELECT name FROM sqlite_master WHERE type='table'");

while ($row = $res->fetchArray(SQLITE3_NUM)) {
    echo "{$row[0]}\n";
}

The code example prints all available tables in the specified database to the terminal.

$res = $db->query("SELECT name FROM sqlite_master WHERE type='table'");

The table names are stored inside the system sqlite_master table.

$ php list_tables.php
cars
images

These were the tables on our system.

The changes() returns the number of database rows that were modified, inserted, or deleted by the most recent SQL statement.

changes.php
<?php

$db = new SQLite3(':memory:');

$db->exec("CREATE TABLE friends(id INTEGER PRIMARY KEY, name TEXT)");
$db->exec("INSERT INTO friends(name) VALUES ('Tom')");
$db->exec("INSERT INTO friends(name) VALUES ('Rebecca')");
$db->exec("INSERT INTO friends(name) VALUES ('Jim')");
$db->exec("INSERT INTO friends(name) VALUES ('Robert')");

$db->exec('DELETE FROM friends');

$changes = $db->changes();

echo "The DELETE statement removed $changes rows";

The example returns the number of deleted rows.

$ php changes.php
The DELETE statement removed 4 rows

This is the output.

PHP SQLite3 PDO example

PHP Data Objects (PDO) defines a lightweight interface for accessing databases in PHP. It provides a data-access abstraction layer for working with databases in PHP. It defines consistent API for working with various database systems.

PHP PDO is a built-in library; we do not need to install it.

list_tables.php
<?php

$pdo = new PDO('sqlite:test.db');

$stm = $pdo->query("SELECT * FROM cars");
$rows = $stm->fetchAll(PDO::FETCH_NUM);

foreach($rows as $row) {

    printf("$row[0] $row[1] $row[2]\n");
}

The example fetches all table rows with PHP PDO.

Dibi example

PHP Dibi is a tiny and smart database layer for PHP.

$ composer req dibi/dibi

We install the library.

fetch_cars.php
<?php

require('vendor/autoload.php');

$db = dibi::connect([
    'driver' => 'sqlite',
    'database' => 'test.db',
]);

$rows = $db->query('SELECT * FROM cars');

foreach ($rows as $row) {
    
    $id = $row->id;
    $name = $row->name;
    $price = $row->price;

    echo "$id $name $price \n";
}

The example fetches all rows from the cars table.

Doctrine DBAL example

Doctrine is a set of PHP libraries primarily focused on providing persistence services in PHP. Its main projects are an object-relational mapper (ORM) and the database abstraction layer (DBAL).

$ composer req doctrine/dbal

We install the Doctrine DBAL package.

fetch_cars.php
<?php

require_once "vendor/autoload.php";

use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\FetchMode;

$attrs = ['driver' => 'pdo_sqlite', 'path' => 'test.db'];

$conn = DriverManager::getConnection($attrs);

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder->select('*')->from('cars');

$stm = $queryBuilder->execute();
$rows = $stm->fetchAll(FetchMode::NUMERIC);

foreach ($rows as $row) {

    echo "{$row[0]} {$row[1]} {$row[2]}\n";
}

The example retrieves all rows from the cars table with Doctrine DBAL QueryBuilder.

This was PHP SQLite3 tutorial. You might also be interested in the following related tutorials: Doctrine QueryBuilder tutorial, PHP PDO tutorial, and PHP tutorial, or list all PHP tutorials.