How to Delete BLOB in PostgreSQL using PHP

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

This tutorial will cover how to delete BLOB in PostgreSQL using PHP. BLOB is an acronym for “binary large object” used for storing various forms of binary data, such as a document or picture file. While PostgreSQL does not make use of the BLOB data type directly, the bytea data type can be used to store binary data files in PostgreSQL.

Prerequisites

PHP must be properly installed, configured and working in order to delete BLOB in PostgreSQL using PHP.

  • Confirm the version of the current PHP installation by executing the php -v command.

The results should resemble the following in a Linux system:

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

Composer must be properly installed and configured.

Confirm the Composer installation by executing the composer –version command in the terminal. The results should resemble the following:

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

The Sample Dataset

The following table structure, named toyfactory, will be used for this tutorial’s sample database:

1
2
3
4
5
6
7
CREATE TABLE tbl_toy (
     id        SERIAL PRIMARY KEY,
     toy_id  INTEGER,
     mym_type CHARACTER VARYING(255),
     mym_name CHARACTER VARYING(255),
     mym_data BYTEA NOT NULL
     );

The sample dataset with the corresponding data follows:

1
2
3
4
5
6
toyfactory=# SELECT * FROM tbl_toy;
 id | toy_id  | mym_type   | mym_name  |  mym_data
----+---------+------------+-----------+--------------
 1  |       1 | image/jpg  | woody      | \x3136373788
 2  |       2 | image/jpg  | buz        | \x3136373789
(2 ROWS)

Project Structure

This section will cover the structure of the application.

  • First, to maintain consistency with the database, name the project directory toysfactory.

  • Next, create a JSON file and name it composer.json using the following code:

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

This will both handle the mapping of the classes of the PHP application and create the PostgreSQL namespace.

NOTE: The PHP Standard Recommendation is PSR-4: Improved Autoloading for adding FQCN into a file path.

  • Next, navigate to project directory, via the terminal, and execute the following composer command:
1
composer update
  • The above code creates a vendor sub-directory within the application. As no other parameters were specified, there will be no third party dependency; only the autoload generated files will be included. An example is displayed here:
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

In this section a text file named postgres_config.ini will be created to hold the configuration of the PostgreSQL database. Following are the details for the database connection:

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

Assets Directory

A directory that will hold the toy images, called assets, will be created with a sub-folder named img.

The Connection.php

  • Following is the code that will handle the database connection via php:
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 toyfactory;


/**
 * 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('postgres_config.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 that this is a singleton class named Connection.php.

Also notice that the details within the configuration file, created in the previous section, were used in the above code.

Delete BLOB data in PostgreSQL

Following is the code used to delete BLOB in PostgreSQL using PHP:

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
 /**
* Delete the large object in the database
* @param int $id
* @throws \Exception
*/

public function delete($id) {

global $pdo;

$this->pdo=$pdo;

try {
    $this->pdo->beginTransaction();
    // selects the file data from the database employee
    $stmt = $this->pdo->prepare("SELECT mime_data "
            . "FROM user_info "
            . "WHERE user_id= :id");
    $stmt->execute([$id]);
    $stmt->bindColumn('mime_data', $fileData, \PDO::PARAM_STR);

    // deletes the large object in the user_info table
    $stmt = $this->pdo->prepare("DELETE FROM user_info WHERE user_id = :id");
    $stmt->execute([$id]);

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

A breakdown of the above code follows:

  • First, a SQL statement is prepared that selects the mime_data column in the table user_info.

  • The execute() method is then called, using the $id variable to execute the statement.

  • The mime_data is bound to variable $fileData.

  • Finally, the delete statement used to delete the BLOB in the database is executed.

Note that the following code must be used in the index.php file to invoke this function via the URL:

1
2
3
4
5
6
7
8
9
10
11
12
require 'vendor/autoload.php';

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

$pdo = Connection::get()->connect();
$blobDB = new dbBlob($pdo);

// get document id from the query string
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);

$file = $blobDB->delete($id);

Now enter the following URL in the browser: localhost:8080/toyfactory/index.ph?id=2

As shown below, it can now be verified, via the PostgreSQL shell, that the data was actually deleted:

1
2
3
4
5
toyfactory=# SELECT * FROM tbl_toy;
 id | toy_id  | mym_type   | mym_name  |  mym_data
----+---------+------------+-----------+--------------
 1  |       1 | image/jpg  | woody      | \x3136373788
(1 ROWS)

Conclusion

This tutorial explained how to delete BLOB in PostgreSQL using PHP. The article first detailed how to confirm the current versions of the PHP and composer installations, create a sample dataset, set up the project structure, the PHP configuration file and the assets directory. The tutorial then provided the code on how to delete BLOB in PostgreSQL using PHP, a breakdown of the code and how to confirm the deletion of the file was successful. Remember there will be no third party dependency when creating a vendor sub-directory within the application if no other parameters are specified.

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.