How to INSERT BLOB in PostgreSQL using PHP

Introduction

A binary large object (BLOB) is used for storing file contents. This is helpful for developers who want to use the PHP scripting language to add large object data and then save it within a PostgreSQL database. Flexibility is a revered characteristic of what’s special about PostgreSQL. Although it doesn’t have a BLOB data type, it understands how to utilize an alternate one to get the job done. In this case, the binary string storing is taken care of with the bytea data type. Learn more with this tutorial that explains how to insert BLOB in PostgreSQL using PHP.

Prerequisite

  • Install and configure PHP on your system.

  • Verify which PHP version you have installed with the command php -v.

  • If you have a Linux distro, the result for the PHP version you have installed should resemble something similar to this:

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
  • For your Windows OS, its screen should display a response showing the installed PHP version like this:
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
  • Be sure to have correctly installed and setup Composer.

  • At a terminal window, verify the version on your OS with this composer --version command.

Composer version 1.9.1 2019-11-01 17:20:17

Creating the Sample Dataset

  • Construct a test database for you to use with the examples given in this tutorial on how to insert BLOB in PostgreSQL using PHP. Name it employee. Create the structure of it with this code:
CREATE TABLE user_info (
     id        SERIAL PRIMARY KEY,
     user_id  INTEGER,
     mime_class CHARACTER VARYING(255),
     mime_name CHARACTER VARYING(255),
     mime_data BYTEA NOT NULL
     );

Creating the PHP Project Structure via Composer

Use Composer to build the project structure in PHP.

  • Make blobphp the name of the project main directory.

  • Create a new file with the .json extension format and name it composer.json.

  • Append your new composer.json with the code here like this:

{
  "autoload": {
    "psr-4": {
      "blobphp\": "app/"
    }
  }
}

Now, with the above script, class mapping is set for the namespace of your PostgreSQL project.

NOTE: The psr-4 in the script is the acronym for “PHP Standard Recommendations” for autoloading. It enables file path usage of Java’s Fully-Qualified Class Name (FQCN).

  • From a terminal window, go to the directory for the project and input this code:
composer update
  • The above code puts in your directory for the project a new vendor folder. Compose automatically produces autoload files because no library of a third party was specified.
Loading composer repositories with package information
Updating dependencies (including require-dev)
Nothing to install or update
Generating autoload files

Make the PHP config file

  • Within directory app, you will store your PostgreSQL test database’s information in a PHP configuration file. Put config.ini as its name.
host=localhost
port=5432
database=employee
user=postgres
password=1234

Create the Asset directory

  • Make a new folder to store images for the examples given in this tutorial on how to insert BLOB in PostgreSQL database using PHP. Name the folder assets\img.

The PHP code for the connection.php

  • Create a configuration file in your app direction so you can make a database connection for your PostgreSQL project. Name the config file connection.php.
<?php
namespace blobphp;


/**
 * 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('conf.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;
    }


}

NOTE: It’s important to follow the long code as indicated because taking a shortcut may raise exceptions while learning how to insert BLOB in PostgreSQL using PHP.

  • In the above code, the class called Connection is a singleton type which creates one instance, one time. Therefore, if it runs across another instance, it won’t keep making instances. Instead, it will allude to the original instance it had created.

Insert BLOB data in PostgreSQL

<?php
namespace blobphp;

class dbblob {

/**
 * Insert a file into the company_files table
 * @param int $userid
 * @param string $mimeClass
 * @param string $mimeName
 * @param string $mimeData
 * @return int
 * @throws \Exception
 */

    public function insert($userid, $mimeName, $mimeClass, $pathOfTheFile) {

        global $pdo;


        $this->pdo=$pdo;

        if (!file_exists($pathOfTheFile)) {
            throw new \Exception("File %s not found.");
        }

        $sql = "INSERT INTO user_info(user_id,mime_class,mime_name,mime_data) "
                . "VALUES(:user_id,:mime_class,:mime_name,:mime_data)";

        try {
            $this->pdo->beginTransaction();

            // create large object
            $data = $this->pdo->pgsqlLOBCreate();
            $strm = $this->pdo->pgsqlLOBOpen($data, 'w');

            // read data from the file and copy the the stream
            $fh = fopen($pathOfTheFile, 'rb');
            stream_copy_to_stream($fh, $strm);
            //
            $fh = null;
            $strm = null;

            $stmt = $this->pdo->prepare($sql);

            $stmt->execute([
                ':user_id' => $userid,
                ':mime_class' => $mimeClass,
                ':mime_name' => $mimeName,
                ':mime_data' => $data,
            ]);

            // commit the transaction
            $this->pdo->commit();
        } catch (\Exception $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        return $this->pdo->lastInsertId('user_info_id_seq');
    }

}

    ?>

Here are some details regarding the code you just inputted shown above:

  • To simultaneously make a large object (OID) and obtain its type, you called the pgsqlLOBCreate() method.

  • A write data stream is opened with the method pgsqlLOBopen() method.

  • The data is read and copied via the stream that goes to the BLOB.

  • You then prepared and executed the statement INSERT.

  • Now you’re ready to open your index.php file, and use the code below for latInsertId() method to complete the steps on how to insert BLOB in PostgreSQL using PHP:

<?php

require 'vendor/autoload.php';

use blobphp\Connection as Connection;
use blobphp\dbBlob as dbBlob;

try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $blobDB = new dbBlob($pdo);
    $fileId = $blobDB->insert(2, 'sam', 'image/jpg', 'assets/img/sam.jpg');

    echo 'A file has been inserted with id ' . $fileId;
} catch (\PDOException $e) {
    echo $e->getMessage();
}

You successfully completed an index.php file launching! A message should be visible stating: A file has been inserted with id 1.

  • With psql at the command line in PostgreSQL, confirm the BLOB insertion:
employee=# SELECT * FROM user_info;
 id | user_id | mime_class | mime_name |  mime_data
----+---------+------------+-----------+--------------
  1 |       2 | image/jpg  | sam       | \x3136373633

Conclusion

This tutorial explained how to insert BLOB in PostgreSQL using PHP. It’s essential for developers to be able to work with a variety of file formats within the PostgreSQL. And luckily, you can. Use the INSERT method for BLOBs and streamline your coding all of the time.

Pilot the ObjectRocket Platform Free!

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

Get Started

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.