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: