How to Query a BLOB in PostgreSQL using PHP
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 -vcommand. On Linux systems, the result of this command should look something like this:
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:
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:
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:
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:
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.jsonfile and add the following code to it:
"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:
- This command will create a folder named
vendorwithin the project directory. Since we don’t specify any other configuration, nothing else will be installed or updated; instead, it will generate autoload files.
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.
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.
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
* 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 thepgsqlLOBopen()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:
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started