Home » SQLite PHP » SQLite PHP: Querying Data

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.
Was this tutorial helpful ?
SQLite PHP: Updating Data
Previously
SQLite PHP: Updating Data
Up Next
SQLite PHP: Deleting Data
SQLite PHP: Deleting Data