How to Query a BLOB in PostgreSQL using PHP

Written by Data Pilot
December 09, 2019
  • Blob
  • PHP
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In SQL, binary data can be stored in a database as a BLOB, or binary large object. A BLOB often takes the form of an audio file, an image or some other multimedia object. Although PostgreSQL doesn’t offer a specific BLOB data type, it’s still possible to store and query BLOB data. In this article, we’ll show you how to query a BLOB in PostgreSQL using PHP.

Prerequisites

Before we start explaining how to query PostgreSQL BLOB data using PHP, let’s review a few important prerequisites that need to be in place:

  • First, you’ll need to ensure that PHP has been installed and configured. To check your PHP version, use the php -v command. On Linux systems, the result of this command should look something like this:
1
2
3
4
PHP 7.3.7-2+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Jul 25 2019 11:44:59) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.7, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.3.7-2+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

On Windows systems, you’ll see something like the following:

1
2
3
PHP 7.3.11 (cli) (built: Oct 22 2019 11:20:18) ( ZTS MSVC15 (Visual C++ 2017) x64 )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.11, Copyright (c) 1998-2018 Zend Technologies
  • You’ll also need to make sure that Composer is installed and configured. To confirm that it’s installed, use the command composer --version. You should see output that looks like the following:
1
Composer version 1.9.1 2019-11-01 17:20:17

The Sample Dataset

Now that we’ve discussed the prerequisites, we can turn our attention to data. We’ll need some sample data to use in this tutorial. In this section, we’ll show you the sample dataset structure that we’ll be working with throughout this tutorial.

Shown below is the table structure for our hypothetical flowershop business:

1
2
3
4
5
6
7
CREATE TABLE tbl_flower (
     id        SERIAL PRIMARY KEY,
     flower_id  INTEGER,
     m_class CHARACTER VARYING(255),
     m_name CHARACTER VARYING(255),
     m_data BYTEA NOT NULL
     );

Here’s the sample dataset for this table:

1
2
3
4
5
6
flowershop=# SELECT * FROM tbl_flower;
 id | flower_id | m_class  | m_name    |  m_data
----+---------+------------+-----------+--------------
 1  |       1 | image/jpg  | rose      | \x3136373735
 2  |       2 | image/jpg  | sunflower | \x3136373736
(2 ROWS)

Composer creates PHP project structure.

Next, we’ll create our project directory’s structure:

  • First, we’ll create the main project directory and name it flowerphp.

  • Then we’ll create our composer.json file and add the following code to it:

1
2
3
4
5
6
7
{
  "autoload": {
    "psr-4": {
      "flowerphp\": "app/"
    }
  }
}

This file will handle the class mapping of our PHP application and will create the PostgreSQL namespace.

NOTE: Follow PHP Standard Recommendations PSR-4, which offers improved autoloading, to be able to use Fully Qualified Class Names (FQCN) in a file path.

  • After editing the file, we’ll navigate to our project directory in the terminal and execute the command shown below:
1
composer update
  • This command will create a folder named vendor within the project directory. Since we don’t specify any other configuration, nothing else will be installed or updated; instead, it will generate autoload files.
1
2
3
4
Loading composer repositories with package information
Updating dependencies (including require-dev)
Nothing to install or update
Generating autoload files

The PHP configuration file

Next, let’s create a configuration file inside the app directory and name it configuration.ini. This file will contain details about our PostgreSQL database.

1
2
3
4
5
host=localhost
port=5432
database=flowershop
user=postgres
password=1234

NOTE: We’ll need to create an assets folder and a sub-folder inside this folder named img. This is where we’ll store the images that we plan to use in this tutorial.

The Connection.php

In this section, we’ll show you how to create the connection.php file that stores the connection details needed for our application.

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
50
51
52
53
54
<?php
namespace flowerphp;


/**
 * Represents the Connection
 */
class Connection {

    /**
     * The Connection
     * @var type
     */
    private static $conn;

    /**
     * Connect to the database and return an instance of \PDO object
     * @return \PDO
     * @throws \Exception
     */
    public function connect() {

        // The following script will parse the parameters in the conf.ini configuration file
        $params = parse_ini_file('configuration.ini');
        if ($params === false) {
            throw new \Exception("Error reading database configuration file");
        }
        // connect to the postgresql database from the config.file parameters
        $conStr = sprintf("pgsql:host=%s;dbname=%s;user=%s;password=%s",
                $params['host'],
                $params['database'],
                $params['user'],
                $params['password']);

        $pdo = new \PDO($conStr);
        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        return $pdo;
    }

    /**
     * return an instance of the Connection object
     * @return type
     */
    public static function get() {
        if (null === static::$conn) {
            static::$conn = new static();
        }

        return static::$conn;
    }


}
  • The code shown above creates a singleton class named Connection. A singleton class is created only once, and it will refer to an existing instance should one exist.

Query BLOB data in PostgreSQL

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
/**
    * Read BLOB from the database and output to the web browser
    * @param int $id
    */
public function read($id) {

    global $pdo;

    $this->pdo=$pdo;

    $this->pdo->beginTransaction();

    $stmt = $this->pdo->prepare("SELECT id, m_data, m_class "
            . "FROM tbl_flower "
            . "WHERE flower_id= :id");

    // query blob from the database
    $stmt->execute([$id]);

    $stmt->bindColumn('m_data', $fileData, \PDO::PARAM_STR);
    $stmt->bindColumn('m_class', $mimeClass, \PDO::PARAM_STR);
    $stmt->fetch(\PDO::FETCH_BOUND);
    $stream = $this->pdo->pgsqlLOBOpen($fileData, 'r');

    // output the file
    header("Content-type: " . $mimeClass);
    fpassthru($stream);
}
    ?>

Let’s take a closer look at what’s happening in the code shown above:

  • We begin by preparing a statement for the SELECT operation.

  • Then we execute that statement using the method execute().

  • Next, we pass the OID to the pgsqlLOBOpen() method.

  • The output stream will depend on the MIME-type of the file.

  • Finally, we’ll call the beginTransaction() at the onset of the method; we do this because the pgsqlLOBopen() method needs to be invoked within a transaction.

If we want to query a BLOB in the PostgreSQL database, we can simply pass the id as a URL parameter. An example would look like this: localhost:8080/flowerphp/index.ph?id=3.

Here’s an example of what the output should look like:

alt text

Conclusion

PostgreSQL may not have an explicit BLOB data type, but it’s easy to both store and query binary data in PostgreSQL from an application. In this tutorial, we showed you how to query a BLOB in a PostgreSQL database using PHP. With our instructions and code examples, you’ll be able to build your own applications that query for BLOB data and handle the results.

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.