- Configuring XAMPP to use SQLite
- Create or Open SQLite Database
- Creating Tables in SQLite DB
- Inserting Record in SQLite Database
- Fetching Records from SQLite Database
- Fetching SQLite Last Insert Id
- Using PDO with SQLite
In PHP, SQLite uses the SQLite3 extension, which adds the SQLite3, and SQLite3Result classes to the PHP language. Following listing shows some benefits of SQLite database.
Why use SQLite:
- File-based: You don’t have to install a separate database server
- Lightweight: Takes fewer resources because there’s no constantly running server
- Easy to distribute: Just install the SQLite executable on your machine available at https://sqlite.org.
- Very fast reading: In many cases, even faster than when using a “real” database.
- Slow writing: Writing can sometimes take longer than with other systems because file locking is an issue.
- Not suitable if you have a large amount of data to deal with.
How to enable SQLite to use in PHP:
- Download the precompiled SQLite binaries from https://sqlite.org/download.html
- Open the php.ini file and uncomment the
extension=sqlite3
directive and - Uncomment the
sqlite3.extension_dir=
and specify the directory where you stored the (uncompressed) downloaded files, for example,sqlite3.extension_dir =/etc/sqlite
How to use SQLite with XAMPP
- Visit https://sqlite.org/download.html
- Download the zipped .dll file under the “Precompiled Binaries for Windows” heading.
- Extract the zip file to a folder (i.e. sqlite) inside the Xampp installation
Next, you need to configure php.ini to enable SQLite on your Xampp installation:
- Open
php.ini
file in your code editor available inside the Xampp folder (i.e.D:\xampp\php
). - Find the
;extension=sqlite3
directive and uncomment it by removing the semicolon. - Find
;sqlite3.extension_dir=
directive, uncomment it, and specify the path where you stored the uncompressed .dll files, for example,sqlite3.extension_dir =D:\xampp\php\sqlite
- You’re done.
Create or Open SQLite Database
Let’s create a new database first. The extension of the file is up to you, but .sqlite
is common and self-explanatory.
<?php $db = new SQLite3('brainbell_db.sqlite'); echo 'Connected to the database.'; $db->close();
You provide the name of the database file (it gets created if it doesn’t exist yet). For this to work, the PHP process needs read and write privileges to the file.
Creating a Table in SQLite DB
<?php try { $db = new SQLite3('brainbell.db'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); echo 'Table Created'; $db->close(); } catch (Exception $e) { echo $e->getMessage(); }
SQLite is a “typeless” database engine, you don’t need not specify the type of a column when creating the table. If you’ve specified the type of a column you can still insert values of a type different than what is specified.
Inserting Record in SQLite Database
<?php try { $db = new SQLite3('brainbell.db'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $db->query("INSERT INTO user_table (name, email) VALUES('brainbell.com', 'admin@brainbell.com')"); $db->close(); } catch (Exception $e) { echo $e->getMessage(); }
The INSERT queries are slow in SQLite database. It is recommended to wrap queries in a transaction (using BEGIN
and COMMIT
), otherwise, the SQLite automatically wraps the query in a transaction, which slows down everything:
<?php //... $db->exec('BEGIN'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $db->query("INSERT INTO user_table (name, email) VALUES('brainbell.com', 'admin@brainbell.com')"); $db->exec('COMMIT'); //...
SQLite Prepared Statement using Positioned Parameters:
You can insert the potentially unsafe data, for example form data, with a prepared statement. You can use named or positioned parameters, following example uses positioned parameters (question marks):
<?php try { $db = new SQLite3('brainbell.db'); $db->exec('BEGIN'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $stmt = $db->prepare("INSERT INTO user_table (name, email) VALUES(?,?)"); $stmt->bindParam(1, $_POST['name'], SQLITE3_TEXT); $stmt->bindParam(2, $_POST['email'], SQLITE3_TEXT); $stmt->execute(); $db->exec('COMMIT'); $db->close(); } catch (Exception $e) { echo $e->getMessage(); }
The bindParam
method takes three parameters:
- A string (for named parameters) or an int (for positional parameters), positional parameters start with 1.
- The variable to bind to.
- The data type of the variable to bind.
SQLITE3_TEXT
: (default) a text string.SQLITE3_INTEGER
: a signed integer.SQLITE3_FLOAT
: a floating point number.SQLITE3_BLOB
: a blob of data (a file, such as an image).SQLITE3_NULL
: a NULL value
Fetching Records from SQLite Database
<?php try { $db = new SQLite3('brainbell.db'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $result = $db->query("SELECT rowid, * FROM user_table "); while ($row = $result->fetchArray()) { //echo $row[0]. ', ' .$row[1].', ' .$row[2].'<br>'; echo $row['rowid']. ', ' .$row['name'].', ' .$row['email'].'<br>'; } $db->close(); } catch (Exception $e) { echo $e->getMessage(); }
Except for
https://www.sqlite.org/lang_createtable.html#rowidWITHOUT ROWID
tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the “rowid
“. The rowid value can be accessed using one of the special case-independent names “rowid”, “oid”, or “_rowid_” in place of a column name.
SQLite Prepared Statement using Named Parameters:
<?php $db = new SQLite3('brainbell.db'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $stmt = $db->prepare("SELECT * FROM user_table WHERE email=:email "); $stmt->bindParam(':email', $_POST['email'], SQLITE3_TEXT); $result = $stmt->execute(); while ($row = $result->fetchArray()) { echo $row['name'].', ' .$row['email'].'<br>'; } $db->close();
For detail, see positioned parameters in prepare statement.
SQLite Last Insert Id
The lastInsertRowID
method of SQLite3 class returns the row ID of the last INSERT into the database:
<?php $db = new SQLite3('brainbell.db'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $db->query("INSERT INTO user_table (name, email) VALUES('BrainBell','admin@brainbell.com')"); echo $db->lastInsertRowID(); $db->close();
Note: This method returns 0 if the record is not successfully inserted into the database.
Using PDO with SQLite
PDO (PHP Data Object) provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data.
PDO requires a data source name (DSN) to connect to a database. To connect with an SQLite database the “sqlite:path/to/database.ext” format is used. See the following example:
<?php $id = $_POST['id'] ?? 1; try{ $db = new PDO('sqlite:brainbell.db'); $db->query('CREATE TABLE IF NOT EXISTS user_table (name, email, UNIQUE (email))'); $stmt = $db->prepare("SELECT * FROM user_table WHERE rowid=:id"); $stmt->bindParam(':id', $id); $stmt->execute(); while ($row = $stmt->fetch()) { echo $row['name'].', ' .$row['email'].'<br>'; } } catch (Exception $e) { echo $e->getMessage(); }
Working with Databases: