ZetCode

PHP SQLite

last modified November 14, 2026

This is a PHP programming tutorial for the SQLite version 3 database. It covers the basics of SQLite programming with the PHP language. The examples were run with PHP 8.5 and SQLite 3.46.

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 a SQLite browser GUI.

PHP has two ways to work with SQLite: the sqlite3 extension with the SQLite3 class, and the pdo_sqlite driver of PDO. On Debian and Ubuntu, both are installed with the php-sqlite3 package. The extensions are enabled by the package; in a hand-made configuration, we enable them in the php.ini file with extension=sqlite3 and extension=pdo_sqlite.

$ sudo apt install php-sqlite3 sqlite3

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

A SQLite database is a single file. The SQLite3 constructor opens the file; if it does not exist, it is created. The :memory: name creates a temporary database that lives in the memory of the process. In this tutorial, the PHP scripts create the test.db file, and we check its contents with the sqlite3 command line tool. In the interactive mode, it is started with sqlite3 test.db; the .tables command lists the tables, .mode and .headers change the output format, and .exit ends the session. Here, we pass the commands as arguments.

PHP SQLite3 version example

In the following examples, we get the version of the SQLite database. The first example uses the static SQLite3::version method.

version.php
<?php

declare(strict_types=1);

$ver = SQLite3::version();

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

var_dump($ver);

The SQLite3::version returns the version of the SQLite library that PHP uses. It is an array with the version string and the version number.

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

SQLITE_VERSION

In the second example, we ask the database itself.

version2.php
<?php

declare(strict_types=1);

$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.46.1

PHP SQLite3 exec

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

create_table.php
<?php

declare(strict_types=1);

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

$db->exec('DROP TABLE IF EXISTS cars');
$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)");

echo "The cars table was created\n";

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

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

The first statement removes the table if it exists, so that the script can be run repeatedly. The second 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.

We verify the written data with the sqlite3 tool. The -column option selects the column mode, and -header turns on the headers.

$ php create_table.php
The cars table was created
$ sqlite3 test.db ".tables"
cars
$ sqlite3 -column -header test.db "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

PHP SQLite3 error handling

By default, the SQLite3 class does not throw exceptions. A failed statement emits a PHP warning and the method returns false. The error code and message are available through lastErrorCode and lastErrorMsg. The enableExceptions method switches to exceptions, which is the better choice for new code.

exceptions.php
<?php

declare(strict_types=1);

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

$ret = $db->exec('SELEC * FROM cars');
var_dump($ret);
echo $db->lastErrorCode(), ': ', $db->lastErrorMsg(), "\n";

$db->enableExceptions(true);

try {
    $db->exec('SELEC * FROM cars');
} catch (SQLite3Exception $e) {
    echo get_class($e), ': ', $e->getMessage(), "\n";
}

try {
    $db = new SQLite3('/nonexistent/test.db', SQLITE3_OPEN_READWRITE);
} catch (Exception $e) {
    echo get_class($e), ': ', $e->getMessage(), "\n";
}

The first exec contains a syntax error. It raises a warning, returns false, and the error is in lastErrorMsg. After enabling the exceptions, the same statement throws SQLite3Exception. Opening a database in a directory that does not exist, with the SQLITE3_OPEN_READWRITE flag (no creation), throws an exception too.

$ php exceptions.php

Warning: SQLite3::exec(): near "SELEC": syntax error in /app/exceptions.php on line 7
bool(false)
1: near "SELEC": syntax error
SQLite3Exception: near "SELEC": syntax error
Exception: Unable to open database: unable to open database file

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

declare(strict_types=1);

$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\n";

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: the ids of deleted rows are never reused.

$db->exec("INSERT INTO friends(name) VALUES ('Tom')");
$db->exec("INSERT INTO friends(name) VALUES ('Rebecca')");

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. We see the output of the program.

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

PHP SQLite3 query

The query method executes an SQL query and returns a SQLite3Result object.

fetch_all.php
<?php

declare(strict_types=1);

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

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

while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
    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(SQLITE3_ASSOC)) {

The fetchArray retrieves a result row as an associative or numerically indexed array or both (the default is both, which returns every value twice). It returns false if there are no more rows. In this example, we use the SQLITE3_ASSOC mode to fetch the row with column names as keys. This is the output of the example.

$ 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

PHP SQLite3 fetch modes

There are three modes of fetchArray: SQLITE3_BOTH (the default), SQLITE3_ASSOC and SQLITE3_NUM. When we want a single row or a single value, the querySingle method is shorter: it returns the first column of the first row, or the whole row when the second argument is true.

fetch_modes.php
<?php

declare(strict_types=1);

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

$res = $db->query('SELECT * FROM cars WHERE id = 1');
print_r($res->fetchArray());
$res->finalize();

$res = $db->query('SELECT * FROM cars WHERE id = 1');
print_r($res->fetchArray(SQLITE3_NUM));
$res->finalize();

print_r($db->querySingle('SELECT * FROM cars WHERE id = 1', true));
echo $db->querySingle('SELECT COUNT(*) FROM cars'), "\n";
var_dump($db->querySingle('SELECT name FROM cars WHERE id = 100'));

The first print_r shows the default mode, where each value is present under the index and under the name. The second one shows the numeric mode. The querySingle call with true returns an associative array, and without it returns a single value; when there is no row, it returns null. Before the second query, we call finalize to release the first result.

$ php fetch_modes.php
Array
(
    [0] => 1
    [id] => 1
    [1] => Audi
    [name] => Audi
    [2] => 52642
    [price] => 52642
)
Array
(
    [0] => 1
    [1] => Audi
    [2] => 52642
)
Array
(
    [id] => 1
    [name] => Audi
    [price] => 52642
)
8
NULL

PHP SQLite3 escapeString

The escapeString returns a string that has been properly escaped for use inside a single-quoted SQL string literal. It doubles the single quotes.

escape_string.php
<?php

declare(strict_types=1);

$db = new SQLite3(':memory:');
$db->exec('CREATE TABLE authors(id INTEGER PRIMARY KEY, name TEXT)');

$name = "Flann O'Brien";

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

var_dump($name);
var_dump($escaped);

$db->exec("INSERT INTO authors(name) VALUES ('$escaped')");

echo $db->querySingle('SELECT name FROM authors'), "\n";

The example escapes a value that contains an apostrophe. Without the escaping, the SQL statement would be invalid. The escaped value is inserted into the table and the original name is read back.

Escaping is easy to forget or to do incorrectly, so parameterized statements, described below, are the better choice.

$ php escape_string.php
string(13) "Flann O'Brien"
string(14) "Flann O''Brien"
Flann O'Brien

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 SQLite3Stmt object.

PHP SQLite3 has bindParam and bindValue methods 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

declare(strict_types=1);

$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]}\n";

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); the numbering starts from 1. The last argument is the data type: SQLITE3_INTEGER, SQLITE3_FLOAT, SQLITE3_TEXT, SQLITE3_BLOB or SQLITE3_NULL.

$ php prepared.php
3 Skoda 9000

Parameterized statements with named placeholders

The second example uses parameterized statements with named placeholders.

prepared2.php
<?php

declare(strict_types=1);

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

$stm = $db->prepare('SELECT * FROM cars WHERE price BETWEEN :low AND :high ORDER BY price');
$stm->bindValue(':low', 20000, SQLITE3_INTEGER);
$stm->bindValue(':high', 60000, SQLITE3_INTEGER);

echo $stm->getSQL(true), "\n";

$res = $stm->execute();

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

We select cars in a price range using two named placeholders.

$stm = $db->prepare('SELECT * FROM cars WHERE price BETWEEN :low AND :high ORDER BY price');

The named placeholders start with a colon character. The advantage of named placeholders is readability: we do not have to count the positions.

echo $stm->getSQL(true), "\n";

The getSQL method returns the SQL of the statement. With the true argument, the bound values are expanded in the text, which is useful for debugging.

$ php prepared2.php
SELECT * FROM cars WHERE price BETWEEN 20000 AND 60000 ORDER BY price
6 Citroen 21000
8 Volkswagen 21600
4 Volvo 29000
7 Hummer 41400
1 Audi 52642
2 Mercedes 57127

PHP SQLite3 bindParam

The bindParam binds a PHP variable to a statement parameter. The variable is bound by reference, so the statement can be executed repeatedly with different values. This is useful to handle multiple rows.

bind_param.php
<?php

declare(strict_types=1);

$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(SQLITE3_NUM)) {
    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 bindParam method. We set the variables and call execute for each row. With bindValue, the value would be fixed at the time of the call.

$ php bind_param.php
1 Peter Novak
2 Lucy Brown

SQL injection

The next example shows why parameter binding matters.

injection.php
<?php

declare(strict_types=1);

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

$input = "x' OR '1'='1";

$res = $db->query("SELECT name FROM cars WHERE name = '$input'");
$n = 0;
while ($res->fetchArray()) {
    $n++;
}
echo "Concatenated query returned $n rows\n";

$stm = $db->prepare('SELECT name FROM cars WHERE name = :name');
$stm->bindValue(':name', $input, SQLITE3_TEXT);
$res = $stm->execute();
$n = 0;
while ($res->fetchArray()) {
    $n++;
}
echo "Prepared query returned $n rows\n";

The input contains a quote that closes the string literal and adds a condition that is always true. When it is concatenated into the SQL text, the query returns all the rows. When the same input is bound to a placeholder, it is treated as a plain value and the query finds no car with such a name.

$ php injection.php
Concatenated query returned 8 rows
Prepared query returned 0 rows

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.

Number of columns

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

num_of_columns.php
<?php

declare(strict_types=1);

$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 example selects one row of the cars table and prints the number of columns of the result set.

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

PRAGMA table_info

The PRAGMA table_info command returns information about the columns of a table.

column_names.php
<?php

declare(strict_types=1);

$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, the default value for the column, and whether it is a part of the primary key.

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. This is the output of the example.

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

Column names

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

column_names2.php
<?php

declare(strict_types=1);

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

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

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

printf("%-10s %s\n", $col1, $col2);

while ($row = $res->fetchArray(SQLITE3_NUM)) {
    printf("%-10s %s\n", $row[1], $row[2]);
}

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. The columnName returns the name of the nth column, counted from zero. We print the two column names first and then all the rows with a while loop; the printf format %-10s pads the name to ten characters.

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

Column types

The columnType method returns the data type of a column in the current row. SQLite is dynamically typed: the type belongs to the stored value, not to the column, so we must fetch a row first.

column_type.php
<?php

declare(strict_types=1);

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

$db->exec('CREATE TABLE items(id INTEGER PRIMARY KEY, name TEXT, price REAL, photo BLOB, note TEXT)');
$db->exec("INSERT INTO items(name, price, photo, note) VALUES ('pen', 1.5, x'00ff', NULL)");

$types = [
    SQLITE3_INTEGER => 'INTEGER',
    SQLITE3_FLOAT => 'FLOAT',
    SQLITE3_TEXT => 'TEXT',
    SQLITE3_BLOB => 'BLOB',
    SQLITE3_NULL => 'NULL',
];

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

for ($i = 0; $i < $res->numColumns(); $i++) {
    printf("%-6s %s\n", $res->columnName($i), $types[$res->columnType($i)]);
}

We create a table with columns of several kinds and insert one row. After fetchArray, the columnType method returns one of the SQLITE3_* constants. The note column has a NULL value, so its type is SQLITE3_NULL.

$ php column_type.php
id     INTEGER
name   TEXT
price  FLOAT
photo  BLOB
note   NULL

List of tables

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

list_tables.php
<?php

declare(strict_types=1);

$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. The table names are stored inside the system sqlite_master table. These were the tables on our system.

$ php list_tables.php
cars

Changes

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

changes.php
<?php

declare(strict_types=1);

$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\n";

The example returns the number of deleted rows.

$ php changes.php
The DELETE statement removed 4 rows

PHP SQLite3 transactions

A transaction groups several statements, which either all succeed or are all reverted. In SQLite, we start it with BEGIN and end it with COMMIT or ROLLBACK. SQLite also makes inserts much faster when they are wrapped in one transaction, because the data is written to the disk once.

transaction.php
<?php

declare(strict_types=1);

$db = new SQLite3('test.db');
$db->enableExceptions(true);

$db->exec('DROP TABLE IF EXISTS accounts');
$db->exec('CREATE TABLE accounts(id INTEGER PRIMARY KEY, name TEXT NOT NULL, balance INT CHECK (balance >= 0))');
$db->exec("INSERT INTO accounts(name, balance) VALUES ('Alice', 100), ('Bob', 50)");

function transfer(SQLite3 $db, int $from, int $to, int $amount): void
{
    $db->exec('BEGIN');

    try {
        $db->exec("UPDATE accounts SET balance = balance + $amount WHERE id = $to");
        $db->exec("UPDATE accounts SET balance = balance - $amount WHERE id = $from");
        $db->exec('COMMIT');
    } catch (SQLite3Exception $e) {
        $db->exec('ROLLBACK');
        echo 'Transfer failed: ', $e->getMessage(), "\n";
    }
}

function printBalances(SQLite3 $db): void
{
    $res = $db->query('SELECT name, balance FROM accounts');

    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        echo "{$row['name']}: {$row['balance']}  ";
    }

    echo "\n";
}

transfer($db, 1, 2, 30);
printBalances($db);

transfer($db, 1, 2, 500);
printBalances($db);

The example creates an accounts table with a CHECK constraint that does not allow a negative balance. The transfer function moves money between two accounts in a transaction. The first transfer succeeds. The second one would make the balance of Alice negative; the UPDATE violates the constraint, an exception is thrown, and the ROLLBACK reverts the first update of the transaction, so Bob does not receive the money.

$ php transaction.php
Alice: 70  Bob: 80  
Transfer failed: CHECK constraint failed: balance >= 0
Alice: 70  Bob: 80  

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. See the PHP PDO tutorial for more information.

PHP PDO is a built-in library; we only need the pdo_sqlite driver.

pdo_sqlite.php
<?php

declare(strict_types=1);

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

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

foreach ($rows as $row) {
    echo "$row[0] $row[1] $row[2]\n";
}

$stm = $pdo->prepare('SELECT name FROM cars WHERE price > :price ORDER BY price');
$stm->execute(['price' => 50000]);

echo implode(', ', $stm->fetchAll(PDO::FETCH_COLUMN)), "\n";

The example fetches all table rows with PHP PDO. The data source name is sqlite:test.db; for a memory database, it is sqlite::memory:. PDO throws exceptions by default. The second query uses a named placeholder in a prepared statement.

$ php pdo_sqlite.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
Audi, Mercedes, Bentley

Dibi example

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

$ composer require dibi/dibi

We install the library.

fetch_cars_dibi.php
<?php

declare(strict_types=1);

require __DIR__ . '/vendor/autoload.php';

$db = new Dibi\Connection([
    'driver' => 'sqlite',
    'database' => 'test.db',
]);

$rows = $db->query('SELECT * FROM cars WHERE price > ?', 30000);

foreach ($rows as $row) {
    echo "$row->id $row->name $row->price\n";
}

echo $db->fetchSingle('SELECT COUNT(*) FROM cars'), " cars\n";

The example fetches rows from the cars table. The values after the SQL text are bound to the ? placeholders. The rows are objects whose properties are the columns; the fetchSingle method returns a single value.

$ php fetch_cars_dibi.php
1 Audi 52642
2 Mercedes 57127
5 Bentley 350000
7 Hummer 41400
8 cars

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 require doctrine/dbal

We install the Doctrine DBAL package.

fetch_cars_dbal.php
<?php

declare(strict_types=1);

require __DIR__ . '/vendor/autoload.php';

use Doctrine\DBAL\DriverManager;

$conn = DriverManager::getConnection(['driver' => 'pdo_sqlite', 'path' => 'test.db']);

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder->select('*')->from('cars')->where('price > :price')->setParameter('price', 30000);

$rows = $queryBuilder->executeQuery()->fetchAllNumeric();

foreach ($rows as $row) {
    echo "{$row[0]} {$row[1]} {$row[2]}\n";
}

echo $conn->fetchOne('SELECT COUNT(*) FROM cars'), " cars\n";

The example retrieves rows from the cars table with the Doctrine DBAL QueryBuilder. In DBAL 4, the query is run with executeQuery, and the result is read with methods such as fetchAllNumeric; the old FetchMode constants and the execute method of the query builder are gone. The fetchOne method of the connection returns a single value.

$ php fetch_cars_dbal.php
1 Audi 52642
2 Mercedes 57127
5 Bentley 350000
7 Hummer 41400
8 cars

Source

SQLite3 - PHP manual

PDO_SQLITE - PHP manual

SQLite documentation

This was PHP SQLite tutorial. We have created a database, executed statements and queries, used prepared statements, read metadata, worked with transactions, and accessed SQLite also with PDO, Dibi, and Doctrine DBAL. See also the PHP PDO and PHP mysqli tutorials.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all PHP tutorials.