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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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
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
List all PHP tutorials.