PostgreSQL Tutorial

  • Home
  • Administration
  • Views
  • Triggers
  • Stored Procedures
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
Home / PostgreSQL PHP / PostgreSQL PHP: Working with BLOB

PostgreSQL PHP: Working with BLOB

Summary: in this tutorial, you will learn how to work with PostgreSQL BLOB using PHP such inserting BLOB, querying BLOB, and deleting BLOB.

BLOB stands for the binary large object that is used to store binary data such as the content of a file. PostgreSQL does not have the BLOB data type. However, you can use the  bytea data type for storing the binary string.

We will create a new table named company_files to store the binary string.

1
2
3
4
5
6
7
8
CREATE TABLE company_files (
     id        SERIAL PRIMARY KEY,
     stock_id  INTEGER NOT NULL,
     mime_type CHARACTER VARYING(255) NOT NULL,
     file_name CHARACTER VARYING(255) NOT NULL,
     file_data BYTEA NOT NULL,
     FOREIGN KEY(stock_id) REFERENCES stocks(id)
);

We will store the content of a file in the file_data column. In addition, we will read the file from the assets/images folder and insert them into the company_files table.

To work with the BLOB data, we create a new class named BlobDB.

PostgreSQL PHP BLOB

Inserting PostgreSQL BLOB data

The following insert() method reads data from a file specified by the $pathToFile parameter and inserts it into the company_files table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
   /**
     * Insert a file into the company_files table
     * @param int $stockId
     * @param string $fileName
     * @param string $mimeType
     * @param string $pathToFile
     * @return int
     * @throws \Exception
     */
    public function insert($stockId, $fileName, $mimeType, $pathToFile) {
        if (!file_exists($pathToFile)) {
            throw new \Exception("File %s not found.");
        }
 
        $sql = "INSERT INTO company_files(stock_id,mime_type,file_name,file_data) "
                . "VALUES(:stock_id,:mime_type,:file_name,:file_data)";
 
        try {
            $this->pdo->beginTransaction();
            
            // create large object
            $fileData = $this->pdo->pgsqlLOBCreate();
            $stream = $this->pdo->pgsqlLOBOpen($fileData, 'w');
            
            // read data from the file and copy the the stream
            $fh = fopen($pathToFile, 'rb');
            stream_copy_to_stream($fh, $stream);
            //
            $fh = null;
            $stream = null;
 
            $stmt = $this->pdo->prepare($sql);
 
            $stmt->execute([
                ':stock_id' => $stockId,
                ':mime_type' => $mimeType,
                ':file_name' => $fileName,
                ':file_data' => $fileData,
            ]);
 
            // commit the transaction
            $this->pdo->commit();
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
 
        return $this->pdo->lastInsertId('company_files_id_seq');
    }

How it works.

  1. First, call the pgsqlLOBCreate() method of the PDO object to create a new large object and get the OID of the large object.
  2. Next, call the pgsqlLOBopen() method to open  a stream on the large object to write data to it.
  3. Then, read data from a file and copy the data from the file stream to the large binary object.
  4. After that, prepare the INSERT statement and execute it.
  5. Finally, call the lastInsertId to get the generated ID.

Note that the pgsqlLOBCreate() method must be called within a transaction, therefore, we place all the logic within a transaction.

Place the following code in the index.php file to insert the content of the google.png file into the company_files table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
 
require 'vendor/autoload.php';
 
use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\BlobDB as BlobDB;
 
try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $blobDB = new BlobDB($pdo);
    $fileId = $blobDB->insert(2, 'logo', 'image/png', 'assets/images/google.png');
 
    echo 'A file has been inserted with id ' . $fileId;
} catch (\PDOException $e) {
    echo $e->getMessage();
}

Launch the index.php file, we get the following message.

1
A file has been inserted with id 1

To verify the insert operation, we use the following query:

1
SELECT * FROM company_files;

1
2
3
4
id | stock_id | mime_type | file_name |  file_data
----+----------+-----------+-----------+--------------
  1 |        2 | image/png | logo      | \x3137323730
(1 row)

Querying PostgreSQL BLOB data

The following read() method reads the BLOB data from the company_files table and output the file content to the web browser.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
    /**
     * Read BLOB from the database and output to the web browser
     * @param int $id
     */
    public function read($id) {
 
        $this->pdo->beginTransaction();
 
        $stmt = $this->pdo->prepare("SELECT id, file_data, mime_type "
                . "FROM company_files "
                . "WHERE id= :id");
 
        // query blob from the database
        $stmt->execute([$id]);
 
        $stmt->bindColumn('file_data', $fileData, \PDO::PARAM_STR);
        $stmt->bindColumn('mime_type', $mimeType, \PDO::PARAM_STR);
        $stmt->fetch(\PDO::FETCH_BOUND);
        $stream = $this->pdo->pgsqlLOBOpen($fileData, 'r');
 
        // output the file
        header("Content-type: " . $mimeType);
        fpassthru($stream);
    }

How it works.

  1. First, prepare a SELECT statement.
  2. Next, execute the SELECT statement by calling the execute() method.
  3. Then, pass the OID to the pgsqlLOBOpen() method of a PDO object to get the stream.
  4. After that, output the stream to based on the mime type of the file.
  5. Finally, because the pgsqlLOBopen() must be called within a transaction, we called the beginTransaction() at the beginning of the method.

To test the read() method, we place the following code in the file.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
 
require 'vendor/autoload.php';
 
use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\BlobDB as BlobDB;
 
$pdo = Connection::get()->connect();
$blobDB = new BlobDB($pdo);
 
// get document id from the query string
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);
 
$file = $blobDB->read($id);

The file.php file gets the id value from the query string and outputs the file stored in the company_files table to the web browser.

PostgreSQL BLOB example

Deleting PostgreSQL BLOB data

The following delete() method deletes a row in the company_files table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
    /**
     * Delete the large object in the database
     * @param int $id
     * @throws \Exception
     */
    public function delete($id) {
        try {
            $this->pdo->beginTransaction();
            // select the file data from the database
            $stmt = $this->pdo->prepare('SELECT file_data '
                    . 'FROM company_files '
                    . 'WHERE id=:id');
            $stmt->execute([$id]);
            $stmt->bindColumn('file_data', $fileData, \PDO::PARAM_STR);
            $stmt->closeCursor();
 
            // delete the large object
            $this->pdo->pgsqlLOBUnlink($fileData);
            $stmt = $this->pdo->prepare("DELETE FROM company_files WHERE id = :id");
            $stmt->execute([$id]);
 
            $this->pdo->commit();
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }

How it works.

  1. First, get the OID object from the file_data column.
  2. Second, use the pgsqlLOBUnLink() method to remove the BLOB data and execute the DELETE statement to remove a row specified by an ID in the company_files table.

In this tutorial, we have shown you how to insert, query, and delete BLOB data in the PostgresQL database.

Previous Tutorial: PostgreSQL PHP: Calling Stored Procedures
Next Tutorial: PostgreSQL PHP: Delete Data From a Table

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL PHP

  • Connect to PostgreSQL Database Using PDO
  • Create New Tables in PHP
  • Insert Data Into Tables in PHP
  • Update Data In a Table using PDD
  • Handle Transaction in PHP
  • Query Data From PostgresQL using PDO
  • Call PostgreSQL Stored Procedures in PHP
  • Manage with BLOB in PHP
  • Delete Data From a Table in PHP

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL Recursive View
  • Learn PostgreSQL Recursive Query By Example
  • Creating Updatable Views Using the WITH CHECK OPTION Clause
  • PostgreSQL Upsert Using INSERT ON CONFLICT statement
  • How to Generate a Random Number in A Range
  • Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table
  • PostgreSQL Character Types: CHAR, VARCHAR, and TEXT
  • Using PostgreSQL SERIAL To Create Auto-increment Column
  • PostgreSQL Boolean Data Type with Practical Examples
  • Understanding PostgreSQL Timestamp Data Types

More Tutorials

  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Functions
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2016 by PostgreSQL Tutorial Website. All Rights Reserved.