Handling BLOB in PHP and MySQL

By: Andi, Stig and Derick  

BLOB stands for Binary Large OBject and refers to binary data, such as JPEG images stored in the database.

Inserting BLOB Data Previously, with the mysql PHP extension, BLOB data was inserted into the database directly as part of the query. You can still do this with mysqli, but when you insert several kilobytes or more, a more efficient method is to use the mysqli_stmt_send_long_data() function or the send_long_data() method of the stmt class.

Here is an example:

<?php

$conn = mysqli_connect("localhost", "test", "", "test");

$conn->query("CREATE TABLE files (id INTEGER PRIMARY KEY AUTO_INCREMENT, ".

"data BLOB)");

$stmt = $conn->prepare("INSERT INTO files VALUES(NULL, ?)");

$stmt->bind_param("s", $data);

$file = "test.jpg";

$fp = fopen($file, "r");

$size = 0;

while ($data = fread($fp, 1024)) {

$size += strlen($data);

$stmt->send_long_data(0, $data);

}

//$data = file_get_contents("test.jpg");

if ($stmt->execute()) {

print "$file ($size bytes) was added to the files table\n";

} else {

die($conn->error);

}

In this example, the test.jpg file is inserted into the file’s table by transferring 1,024 bytes at a time to the MySQL server with the send_long_data() method. This technique does not require PHP to buffer the entire BLOB in memory before sending it to MySQL.

Retrieving BLOB Data

Retrieving BLOB data is the same as retrieving regular data. Use any of the fetch function/method variants as you see fit. Here is an example:

<?php

$conn = mysqli_connect("localhost", "test", "", "test");

if (empty($_GET['id'])) {

$result = $conn->query("SELECT id, length(data) FROM files LIMIT 20");

if ($result->num_rows == 0) {

print "No images!\n";

print "<a href=\"mysqli_blob1.php\">Click here to add one<a>\n";

exit;

}

while ($row = $result->fetch_row()) {

print "<a href=\"$_SERVER[PHP_SELF]?id=$row[0]\">";

print "image $row[0] ($row[1] bytes)</a><br />\n";

}

exit;

}

$stmt = $conn->prepare("SELECT data FROM files WHERE id = ?");

$stmt->bind_param("i", $_GET['id']);

$stmt->execute();

$data = null;

$stmt->bind_result($data);

if (!$stmt->fetch()) {

die("No such image!");

}

header("Content-type: image/jpeg");

print $data;




Archived Comments

1. Thanks
View Tutorial          By: Guru at 2014-07-25 13:27:56

2. i want to convert string to blob and save to mysql database
View Tutorial          By: shrikant at 2013-04-04 07:04:25

3. I like this tutorial, it is self explaining. good work :)
View Tutorial          By: Nikia at 2013-01-25 20:04:30

4. can anyone tell how to read word document stored in blob
View Tutorial          By: mukund at 2012-03-24 03:26:45

5. Good tutorial sir
View Tutorial          By: vinay at 2012-01-28 12:38:30

6. great tutorial but for me blob handling is still sucks
View Tutorial          By: Sarcina Saptamani at 2011-11-10 09:15:07

7. I'm working on an intranet site/web application. We have a table for file storage (MySQL 4.1.22/Inno
View Tutorial          By: Tony Gingrich at 2011-10-23 21:22:31

8. Hi Andi, Stig and Derick!

Nice turorial! However, I think you are using a wrong argu

View Tutorial          By: Lasse Christiansen at 2010-05-01 01:58:14


Most Viewed Articles (in PHP )

Handling BLOB in PHP and MySQL

Get the time past between two MySQL dates in PHP

public, protected, and private Properties in PHP

Building PHP 5.x with Apache2 on SuSE Professional 9.1/9.2

PHP ./configure RESULTING IN [email protected]_2_2_3_... AND UNRESOLVED REFERENCES WITH ORACLE OCI8

Installing PHP 5.x with Apache 2.x on HP UX 11i and configuring PHP 5.x with Oracle 9i

Cannot load /usr/local/apache/libexec/libphp4.so into server: ld.so.1:......

Setting up PHP in Windows 2003 Server IIS7, and WinXP 64

error: "Service Unavailable" after installing PHP to a Windows XP x64 Pro

Running different websites on different versions of PHP in Windows 2003 & IIS6 platform

Installing PHP with nginx-server under windows

Function to return number of digits of an integer in PHP

Function to force strict boolean values in PHP

Function to sort array by elements and count of element in PHP

Convert IP address to integer and back to IP address in PHP

Latest Articles (in PHP)

Comment on this tutorial