SQLite PHP: Querying Data

Summary: in this tutorial, you will learn how to query data from SQLite tables using PHP PDO.

This tutorial begins where the “Insert data into a table in PHP” tutorial left off.

To query data from a table in PHP using PDO, you follow these steps:

Step 1. Connect to the SQLite database:

$pdo = new \PDO($dsn);Code language: PHP (php)

Step 2. Execute a SELECT statement using the query() method of the PDO object:

$rows = $pdo->query($sql);Code language: PHP (php)

The query() method returns a result set as a PDOStatement object.

If you want to pass values to the SELECT statement, you can:

• Create the PDOStatement object by calling the prepare() method of the PDO object.
• Bind values using the bindValue() method of the PDOStatement object.
• Call the execute() method to execute the SELECT statement.

Here’s the code to do these three steps:

$stmt = $pdo->prepare($sql); $stmt->bindValue(':id',$id); $stmt->execute();Code language: PHP (php)

Alternatively, you can pass values to the execute() method directly:

$stmt = $pdo->prepare($sql); $stmt->execute([ ':id' => id ]);Code language: PHP (php)

Step 3. Fetch rows from the result set using the fetch() method of the PDOStatement object and process each row individually:

while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { // process each row in the result set }Code language: PHP (php)

If you are interested in various fetch modes, check out the Fetching Data section on the PHP tutorial website.

Querying all rows from a table

Step 1. Create a new file called query.php within the project directory.

Step 2. Define the get_projects() function that retrieves all rows from the projects table:

<?php require_once 'config.php'; function get_projects($pdo) { $stmt = $pdo->query('SELECT project_id, project_name FROM projects'); $projects = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $projects[] = [ 'project_id' => $row['project_id'], 'project_name' => $row['project_name'] ]; } return $projects; } $dsn = "sqlite:$db"; try { $pdo = new \PDO($dsn); // get all projects $projects = get_projects($pdo); var_dump($projects); } catch(\PDOException $e) { echo $e->getMessage(); }Code language: PHP (php)

How it works.

First, load the configuration from the config.php file:

require_once 'config.php';Code language: PHP (php)

The config.php stores the path to the SQLite database file:

<?php $db = './database/my.db';Code language: PHP (php)

Second, define the get_projects() function that retrieves all rows from the projects table:

function get_projects($pdo) { $stmt = $pdo->query('SELECT project_id, project_name FROM projects'); $projects = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $projects[] = [ 'project_id' => $row['project_id'], 'project_name' => $row['project_name'] ]; } return $projects; }Code language: PHP (php)

Third, construct the data source name (dsn) for connecting to the SQLite database:

$dsn = "sqlite:$db";Code language: PHP (php)

Fourth, open a database connection:

$pdo = new \PDO($dsn);Code language: PHP (php)

Fifth, call the get_projects function to retrieve all rows from the projects table and dump the result set:

$projects = get_projects($pdo); var_dump($projects);Code language: PHP (php)

Finally, display an error message if an error occurs while opening the database connection or querying data:

} catch(\PDOException $e) { echo $e->getMessage(); }Code language: PHP (php)

Step 3. Run the query.php in the web browser.

You’ll see the following output:

array (size=2) 0 => array (size=2) 'project_id' => int 1 'project_name' => string 'PHP SQLite Demo' (length=15) 1 => array (size=2) 'project_id' => int 2 'project_name' => string 'Mastering SQLite' (length=16)Code language: PHP (php)

Finding tasks for a project

Step 1. Define a new function get_tasks_by_project() that retrieves all tasks of a project specified by a project id, in the query.php file:

function get_tasks_by_project($pdo, $projectId) { $sql = 'SELECT * FROM tasks WHERE project_id = :project_id'; $stmt = $pdo->prepare($sql); $stmt->execute([':project_id' => $projectId]); $tasks = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $tasks[] = [ 'task_id' => $row['task_id'], 'task_name' => $row['task_name'], 'start_date' => $row['start_date'], 'completed_date' => $row['completed_date'], 'completed' => $row['completed'], 'project_id' => $row['project_id'], ]; } return $tasks; }Code language: PHP (php)

Step 2. Call the get_tasks_by_project() function to get all tasks of the project with id 1:

$tasks = get_tasks_by_project($pdo, 1); var_dump($tasks);Code language: PHP (php)

Step 3. Run the query.php in the web browser.

It’ll show three tasks of the project id 1:

array (size=3) 0 => array (size=6) 'task_id' => int 1 'task_name' => string 'Prepare the sample database schema' (length=34) 'start_date' => string '2016-06-01' (length=10) 'completed_date' => string '2016-06-01' (length=10) 'completed' => int 1 'project_id' => string '1' (length=1) 1 => array (size=6) 'task_id' => int 2 'task_name' => string 'Create new tables ' (length=18) 'start_date' => string '2016-05-01' (length=10) 'completed_date' => string '2016-05-02' (length=10) 'completed' => int 1 'project_id' => string '1' (length=1) 2 => array (size=6) 'task_id' => int 3 'task_name' => string 'Insert some sample data' (length=23) 'start_date' => string '2016-05-01' (length=10) 'completed_date' => string '2016-06-02' (length=10) 'completed' => int 1 'project_id' => string '1' (length=1)Code language: PHP (php)

Here’s the complete query.php code:

<?php require_once 'config.php'; function get_projects($pdo) { $stmt = $pdo->query('SELECT project_id, project_name FROM projects'); $projects = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $projects[] = [ 'project_id' => $row['project_id'], 'project_name' => $row['project_name'] ]; } return $projects; } function get_tasks_by_project($pdo, $projectId) { $sql = 'SELECT * FROM tasks WHERE project_id = :project_id'; $stmt = $pdo->prepare($sql); $stmt->execute([':project_id' => $projectId]); $tasks = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { $tasks[] = [ 'task_id' => $row['task_id'], 'task_name' => $row['task_name'], 'start_date' => $row['start_date'], 'completed_date' => $row['completed_date'], 'completed' => $row['completed'], 'project_id' => $row['project_id'], ]; } return $tasks; } $dsn = "sqlite:$db"; try { $pdo = new \PDO($dsn); // get all projects $projects = get_projects($pdo); var_dump($projects); // get tasks by project $tasks = get_tasks_by_project($pdo, 1); var_dump($tasks); } catch(\PDOException $e) { echo $e->getMessage(); }Code language: PHP (php)

Summary

• Call the query()method of the PDO object to execute a SELECT statement to retrieve data from tables.
• Use a prepared statement (PDOStatement) to execute a SELECT statement with values to retrieve data from tables.