SQLite PHP: Working with BLOB Data

Summary: in this tutorial, you will learn how to manage BLOB data in SQLite database using PHP PDO.

This tutorial begins where the “Deleting data from a table in PHP” tutorial left off.

BLOB stands for a binary large object, which is a collection of binary data stored as a value in the SQLite database.

BLOB lets you store documents, images, and multimedia files directly in the SQLite database.

Creating a new table

For demonstration purposes, we’ll create a new table called documents.

CREATE TABLE IF NOT EXISTS documents (
    document_id INTEGER PRIMARY KEY,
    mime_type   TEXT    NOT NULL,
    doc         BLOB
);Code language: SQL (Structured Query Language) (sql)

Step 1. Open the terminal and navigate to the database directory of the project:

Step 2. Connect to the my.db database file using the sqlite3 shell:

sqlite3 my.dbCode language: CSS (css)

Step 3. Execute the CREATE TABLE statement to create the documents table:

CREATE TABLE IF NOT EXISTS documents (
    document_id INTEGER PRIMARY KEY,
    mime_type   TEXT    NOT NULL,
    doc         BLOB
);Code language: PHP (php)

In the documents table:

• id is an auto-increment column.
• mime_type indicates the MIME type of the document e.g., application/pdf, image/png. This information is used for rendering the document in web browsers.
• doc stores the document data as BLOB.

Step 4. Verify if the table documents has been created successfully:

.tablesCode language: CSS (css)

Output:

documents  projects   tasks

The output indicates that he documents table has been created successfully.

Writing BLOB into the table

We’ll show you how to store a PDF file and an image in the documents table.

Steps for inserting BLOB into a table using PHP

To insert BLOB data into a table, you follow these steps:

Step 1. Connect to the SQLite database by creating an instance of the PDO class.

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

Step 2. Read data from a file using the fopen() function:

$fh = fopen($pathToFile, 'rb');Code language: PHP (php)

The fopen() function returns a file handler.

Step 3. Prepare the INSERT statement for execution by calling the prepare() method of the PDO object:

$stmt = $pdo->prepare($sql);Code language: PHP (php)

The prepare() method returns an instance of the PDOStatement class.

Step 4. Bind the file handler to the :data placeholder:

$stmt->bindParam(':data', $fh, \PDO::PARAM_LOB);Code language: PHP (php)

Step 5. Execute the INSERT statement by calling the execute() method of the PDOStatement object:

$stmt->execute();Code language: PHP (php)

Inserting BLOB example

Step 1. Download the SQLite Sample Database Diagram in PDF format and the SQLiteTutorial logo in PNG format into your computer.

Step 2. Create a new directory called assets within the project directory and copy these files to the assets directory.

Step 3. Create a new file insert-document.php in the project directory to insert the PDF document and image into the documents table:

<?php

require_once 'config.php';

function insert_document($pdo, $mimeType, $filename) {

    $sql = 'INSERT INTO documents(mime_type,doc) 
            VALUES(:mime_type, :doc)';
    
    $fh = fopen($filename, 'rb');
    if(!$fh) {
        throw new \Exception('Could not open file: ' . $filename);
    }
    
    // prepare statement
    $stmt = $pdo->prepare($sql);

    $stmt->bindParam(':mime_type', $mimeType);
    $stmt->bindParam(':doc', $fh, \PDO::PARAM_LOB);
    
    // execute the INSERT statement
    $stmt->execute();

    // return last inserted id
    return $pdo->lastInsertId();
}

// connect to database
$dsn = "sqlite:$db";

try {

    $pdo = new \PDO($dsn);

     // insert a PNG file into the documents table
    $id = insert_document(
            $pdo,
            'image/png', 
            'assets/sqlite-tutorial-logo.png'
        );

    echo 'Inserted document ID: ' . $id . '<br>';

    // insert a PDF file into the documents table
    $id = insert_document(
            $pdo,
            'application/pdf', 
            'assets/sqlite-sample-database-diagram.pdf'
    );

    echo 'Inserted document ID: ' . $id . '<br>';
   
   
  
}  catch (\PDOException $e) {
    echo $e->getMessage();
}        Code language: HTML, XML (xml)

Verifying data

Step 1. Launch the SQLiteStudio.

Step 2. Open the my.db SQLite database file from the SQLiteStudio.

Step 3. Query data from the documents table:

SELECT id,
       mime_type,
       doc
  FROM documents;Code language: SQL (Structured Query Language) (sql)

Output:

Reading BLOB from the table

Steps for querying BLOB from SQLite in PHP

To query BLOB data from the SQLite database, you can use the following steps:

Step 1. Connect to the SQLite database by creating an instance of the PDO class.

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

Step 2. Prepare the INSERT statement for execution by calling the prepare() method of the PDO object:

$stmt = $pdo->prepare($sql);Code language: PHP (php)

The prepare() method returns an instance of the PDOStatement class.

Step 3. Bind the column with the type \PDO::PARAM_LOB:

$stmt->bindColumn(1, $doc, \PDO::PARAM_LOB);Code language: PHP (php)

The PDO::PARAM_LOB instructs PDO to map the BLOB data as a stream.

Step 4. Execute the SELECT statement by calling the execute() method of the PDOStatement object:

$stmt->execute();Code language: PHP (php)

Querying BLOB data example

The following defines a function read_document that reads BLOB data from the SQLite database and transfers it to the browser with the correct mime type:

<?php

require 'config.php';

function read_document($pdo, $documentId) {

    $sql = 'SELECT mime_type, doc FROM documents WHERE document_id = :document_id';

    // initialize params
    $mimeType = null;
    $doc = null;
        
    // 
    $stmt = $pdo->prepare($sql);
    
    if ($stmt->execute([":document_id" => $documentId])) {

        $stmt->bindColumn(1, $mimeType);
        $stmt->bindColumn(2, $doc, \PDO::PARAM_LOB);

        if($stmt->fetch(\PDO::FETCH_BOUND))  {
            return [
                "document_id" => $documentId,
                "mime_type" => $mimeType,
                "doc" => $doc
            ];
        }
    } 
    return null;
}

$dsn = "sqlite:$db";

// get document id from the query string
$documentId = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);


try{
    // connect to database
    $pdo = new \PDO($dsn);

    // retrieve a document by id from SQLite database
    $doc = read_document($pdo, $documentId);

        // show the document in the browser
    if ($doc != null) {
        header("Content-Type:" . $doc['mime_type']);
        fpassthru($doc['doc']);
    } else {
        echo 'Error loading document ' . $documentId;
    }
} catch(\PDOException $e) {
    echo $e->getMessage();
}
Code language: PHP (php)

Notice that we use the fpassthru() function to pass the BLOB data reading from the database to the output.

For example, the following screenshot shows how the document.php script returns the PDF file in the web browser:

To test the document id 2, you change the value in the id query string as shown in the screenshot below:

Summary

• Use BLOB data to store large documents directly within SQLite databases.