How to Use SQLite

1. Configuring XAMPP to use SQLite
2. Create or Open SQLite Database
3. Creating Tables in SQLite DB
4. Inserting Record in SQLite Database
5. Fetching Records from SQLite Database
6. Fetching SQLite Last Insert Id
7. 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:

1. Download the precompiled SQLite binaries from https://sqlite.org/download.html
2. Open the php.ini file and uncomment the extension=sqlite3 directive and
3. 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

1. Visit https://sqlite.org/download.html
2. Download the zipped .dll file under the “Precompiled Binaries for Windows” heading.
3. 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:

1. Open php.ini file in your code editor available inside the Xampp folder (i.e. D:\xampp\php).
2. Find the ;extension=sqlite3 directive and uncomment it by removing the semicolon.
3. 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
4. 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:

1. A string (for named parameters) or an int (for positional parameters), positional parameters start with 1.
2. The variable to bind to.
3. 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 WITHOUT 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.

https://www.sqlite.org/lang_createtable.html#rowid

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: