In this tutorial, I will show you how to create and connect to SQLite database file using PDO. Then, we will create a table. Then, insert, update and delete rows.

Connect to SQLite

In order to create and connect to the SQLite database file, you need to provide the Data Source Name (DSN) to PDO(). The data source name is prefixed with sqlite: + the absolute path to your SQLite database file. By default, if the database file doesn't exist, then it will be created.

// Create and connect to SQLite database file.
$db_file = new PDO('sqlite:'.__DIR__.'/mytest.db');

Create table

To create a table, use PDO::exec() to execute your SQL statement to create a table.

// Create employees table.
$db_file->exec("CREATE TABLE IF NOT EXISTS employees(
                    id INTEGER PRIMARY KEY, 
                    name TEXT,
                    role TEXT)");

Insert rows

To insert data, it is recommended to use prepared statement with named parameters because it is faster and it is resilient to SQL injection.

// Data to insert.
$data = array(
                array('name' => 'joe', 'role' => 'CEO'),
                array('name' => 'Amy', 'role' => 'CFO'),
            );
 
// Prepare INSERT statement.
$insert = "INSERT INTO employees (name, role) VALUES (:name, :role)";
$stmt = $db_file->prepare($insert);
 
// Insert data in table.
foreach($data as $row) {
 
    // Bind parameters to statement variables.
    //    List of data type: https://www.php.net/manual/en/pdo.constants.php
    $stmt->bindParam(':name', $row['name'], PDO::PARAM_STR);
    $stmt->bindParam(':role', $row['role'], PDO::PARAM_STR);
 
    // Execute statement.
    $stmt->execute();
}

Update data

Update joe to NewJoe.

// Prepare UPDATE statement.
$update = "Update employees SET name=? WHERE name=?";
$stmt = $db_file->prepare($update);
 
// Execute statement.
$stmt->execute(array('NewJoe', 'joe'));

Delete data

Delete all employees with ID higher than 3.

// Prepare DELETE statement.
$delete = "DELETE FROM employees WHERE id > ?";
$stmt = $db_file->prepare($delete);
 
// Execute statement.
$stmt->execute(array(3));

Fetch data

The code below will return all employees with ID between 1 & 3. If you don't use PDO::FETCH_ASSOC in fetchAll() method, then you will get duplicate entries from $results.

// Prepare SELECT statement.
$select = "SELECT * FROM employees WHERE id >= ? AND id <= ?";
$stmt = $db_file->prepare($select);
 
// Execute statement.
$stmt->execute(array(1, 3)); // ID between 1 and 3.
 
// Get the results.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($results as $row) {
    print_r($row);
}

Output

Array
(
    [id] => 1
    [name] => NewJoe
    [role] => CEO
)
Array
(
    [id] => 2
    [name] => Amy
    [role] => CFO
)
Array
(
    [id] => 3
    [name] => NewJoe
    [role] => CEO
)

Get error details

Up to now, it is assumed that all statements are working fine. In case that there are errors, use PDOStatement::errorInfo() to get more details about the error message.

$is_ok = $stmt->execute(/*...*/);
if (!$is_ok) {
    print_r($stmt->errorInfo());
}

Github

https://github.com/xuanngo2001/php-opw-examples/blob/master/pdo/pdo-sqlite-intro.php

PHP, PDO, SQLite

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.

PHP - Using PDO with SQLite