PHP MySQL BLOB

 PHP MySQL BLOB

Summary: in this tutorial, you will learn how to handle BLOB data using PHP PDO. We will show you how to insert, update, and select BLOB data in MySQL databases.

Sometimes, for security reasons, you may need to store large data objects e.g., images, PDF files, and videos in the MySQL database.

MySQL provides a BLOB type that can hold a large amount of data. BLOB stands for the binary large data object. The maximum value of a BLOB object is specified by the available memory and the communication package size. You can change the communication package size by using the  max_allowed_packet variable in MySQL and  post_max_size in the PHP settings.

Let’s see how PHP PDO handles the BLOB type in MySQL.

First, we create a new table named files in the sample database for practicing.

The files the table contains three columns:

• The id column is the primary key, auto-increment column.
• The mime column stores the mime type of the file.
• The data column whose data type is the BLOB is used to store the content of the file.

The following CREATE TABLE statement creates the files table:

CREATE TABLE files (
    id   INT           AUTO_INCREMENT PRIMARY KEY,
    mime VARCHAR (255) NOT NULL,
    data BLOB          NOT NULL
);

Second, we define a class called BlobDemo with the following code:

<?php

/**
 * PHP MySQL BLOB Demo
 */
class BobDemo {

    const DB_HOST = 'localhost';
    const DB_NAME = 'classicmodels';
    const DB_USER = 'root';
    const DB_PASSWORD = '';

    /**
     * Open the database connection
     */
    public function __construct() {
        // open database connection
        $conStr = sprintf("mysql:host=%s;dbname=%s;charset=utf8", self::DB_HOST, self::DB_NAME);

        try {
            $this->pdo = new PDO($conStr, self::DB_USER, self::DB_PASSWORD);
            //for prior PHP 5.3.6
            //$conn->exec("set names utf8");
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    /**
     * close the database connection
     */
    public function __destruct() {
        // close the database connection
        $this->pdo = null;
    }

}

In the __construct() the method, we open a database connection to the MySQL database, and in the  __destruct() the method, we close the connection.

Insert BLOB data into the database

PHP PDO provides a convenient way to work with BLOB data using the streams and preparing statements. To insert the content of a file into a BLOB column, you follow the steps below:

• First, open the file for reading in binary mode.
• Second, construct an INSERT statement.
• Third, bind the filehandle to the prepared statement using the  bindParam() method and call the  execute() method to execute the query.

See the following  insertBlob() method:

   /**
     * insert blob into the files table
     * @param string $filePath
     * @param string $mime mimetype
     * @return bool
     */
    public function insertBlob($filePath, $mime) {
        $blob = fopen($filePath, 'rb');

        $sql = "INSERT INTO files(mime,data) VALUES(:mime,:data)";
        $stmt = $this->pdo->prepare($sql);

        $stmt->bindParam(':mime', $mime);
        $stmt->bindParam(':data', $blob, PDO::PARAM_LOB);

        return $stmt->execute();
    }

Notice that the PDO::PARAM_LOB instructs PDO to map the data as a stream.

Update an existing BLOB column

To update a BLOB column, you use the same technique as described in inserting data into a BLOB column. See the following  updateBlob() method:

   /**
     * update the files table with the new blob from the file specified
     * by the filepath
     * @param int $id
     * @param string $filePath
     * @param string $mime
     * @return bool
     */
    function updateBlob($id, $filePath, $mime) {

        $blob = fopen($filePath, 'rb');

        $sql = "UPDATE files
                SET mime = :mime,
                    data = :data
                WHERE id = :id;";

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

        $stmt->bindParam(':mime', $mime);
        $stmt->bindParam(':data', $blob, PDO::PARAM_LOB);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();
    }

Query data from a BLOB column

The following steps describe how to select data from a BLOB column:

• First, construct a SELECT statement.
• Second, bind the corresponding parameter using the  bindColumn() method of the PDOStatement object.
• Third, execute the statement.

See the following  selectBlob() method:

    /**
     * select data from the the files
     * @param int $id
     * @return array contains mime type and BLOB data
     */
    public function selectBlob($id) {

        $sql = "SELECT mime,
                        data
                   FROM files
                  WHERE id = :id;";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array(":id" => $id));
        $stmt->bindColumn(1, $mime);
        $stmt->bindColumn(2, $data, PDO::PARAM_LOB);

        $stmt->fetch(PDO::FETCH_BOUND);

        return array("mime" => $mime,
            "data" => $data);
    }

PHP MySQL BLOB examples

In the following examples, we will use the BlobDemo class to save a GIF image and a PDF file into the BLOB column of the files table.

PHP MySQL BLOB with image files

First, we insert binary data from the images/php-mysql-blob.gif file into the BLOB column of the files table as follows:

$blobObj = new BlobDemo();

// test insert gif image
$blobObj->insertBlob('images/php-mysql-blob.gif',"image/gif");

Then, we can select the BLOB data and display it as a GIF image:

$a = $blobObj->selectBlob(1);
header("Content-Type:" . $a['mime']);
echo $a['data'];

PHP MySQL BLOB with PDF files

The following code inserts the content of the  pdf/php-mysql-blob.pdf PDF file into the BLOB column:

$blobObj = new BlobDemo();

// test insert pdf
$blobObj->insertBlob('pdf/php-mysql-blob.pdf',"application/pdf");

Then, we can select the PDF data and render it in the web browser as follows:

$a = $blobObj->selectBlob(2);
header("Content-Type:" . $a['mime']);
echo $a['data'];

To replace the PDF file with the GIF image file, you use the  updateBlob() the method is as follows:

$blobObj->updateBlob(2, 'images/php-mysql-blob.gif', "image/gif");

$a = $blobObj->selectBlob(2);
header("Content-Type:" . $a['mime']);
echo $a['data'];

You can download the source code of this tutorial via the following link:

Download PHP MySQL BLOB Source Code

In this tutorial, we have shown you how to manage MySQL BLOB data including inserting, updating, and querying blob.