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 such as images, PDF files, and videos, in the MySQL database.
MySQL provides the BLOB data type that can store a large amount of binary 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, create a new table named files in for practice.
The files table contains three columns:
- The
idcolumn is the primary key, auto-increment column. - The
mimecolumn stores the mime type of the file. - The
datacolumn whose data type is the BLOB 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
);Code language: SQL (Structured Query Language) (sql)Second, define a class called BlobDemo with the following code:
<?php
/**
* PHP MySQL BLOB Demo
*/
class BlobDemo {
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;
}
}Code language: PHP (php)In the __construct() method, we open a database connection to the MySQL database, and in the __destruct() 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 prepare 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 file handle to the prepared statement using the
bindParam()method and call theexecute()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();
}Code language: PHP (php)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();
}Code language: PHP (php)Query data from the 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 thePDOStatementobject. - 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);
}Code language: PHP (php)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");Code language: PHP (php)
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'];Code language: PHP (php)
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");Code language: PHP (php)
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'];Code language: PHP (php)
To replace the PDF file with the GIF image file, you use the updateBlob() method as follows:
$blobObj->updateBlob(2, 'images/php-mysql-blob.gif', "image/gif");
$a = $blobObj->selectBlob(2);
header("Content-Type:" . $a['mime']);
echo $a['data'];Code language: PHP (php)You can download the source code of this tutorial via the following link:
Download PHP MySQL BLOB Source Code
In this tutorial, you have learned how to manage MySQL BLOB data, including inserting, updating, and querying blob.
