Handling BLOB in PHP and MySQL

By: Andi, Stig and Derick Emailed: 1590 times Printed: 2041 times    

Latest comments
By: rohit kumar - how this program is work
By: Kirti - Hi..thx for the hadoop in
By: Spijker - I have altered the code a
By: ali mohammed - why we use the java in ne
By: ali mohammed - why we use the java in ne
By: mizhelle - when I exported the data
By: raul - no output as well, i'm ge
By: Rajesh - thanx very much...
By: Suindu De - Suppose we are executing

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;


PHP Home | All PHP Tutorials | Latest PHP Tutorials

Sponsored Links

If this tutorial doesn't answer your question, or you have a specific question, just ask an expert here. Post your question to get a direct answer.



Bookmark and Share

Comments(5)


1. View Comment

Hi Andi, Stig and Derick!

Nice turorial! However, I think you are using a wrong argument in "$stmt->bind_param("s", $data);"

Using s (string), I cannot get this command working, however, by using "b" for blob (see PHP manual: http://php.net/manual/en/mysqli-stmt.bind-param.php ) the script is perfectly working.

My MySQL and PHP versions:

PHP 5.3.2
MySQL 5.1.45


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

I'm working on an intranet site/web application. We have a table for file storage (MySQL 4.1.22/InnoDB engine), with a field defined as LONGBLOB. MySQL specifies this type to be able to handle just over 4GB. While we will never be pushing this much data to a record, we do have a few instances of BLOBS up to 32MB, which is too much for the MEDIUMBLOB type.

However, I am using the mysql_query() method of inserting. This works well for data as long as 10MB, but the call hangs (no exceptions, timeouts, etc...just simply hangs as if in an infinite loop) for several hours. I have temporarily unlimited all PHP timeouts, as well as insured the max_allowed_packet on our MySQL instance is at 200MB (a little overkill, just to be sure). I even went so far as to set the net_write_timeout value to 120 seconds and the net_read_timeout to 240 seconds. Both the web and db servers are on our LAN (100Mb), so there is optimal communication between the 2 machines.

Are there any other settings I should be aware of to check? Also, is it possible that this is a limitation of the mysql_query() method, and that I need to switch up and use the mysqli interface?

I'm really at a loss here. My background comes from MS SQL, so I am still learning my way around MySQL.

Thanks in advance!


View Tutorial          By: Tony Gingrich at 2011-10-23 21:22:31
3. View Comment

great tutorial but for me blob handling is still sucks

View Tutorial          By: Sarcina Saptamani at 2011-11-10 09:15:07
4. View Comment

Good tutorial sir

View Tutorial          By: vinay at 2012-01-28 12:38:30
5. View Comment

I like this tutorial, it is self explaining. good work :)

View Tutorial          By: Nikia at 2013-01-25 20:04:30

Your name (required):


Your email(required, will not be shown to the public):


Your sites URL (optional):


Your comments:



More Tutorials by Andi, Stig and Derick
Execution Lifetime of a PHP script
preg_split() and explode() in PHP
preg_replace() and preg_replace_callback() in PHP
preg_match(), function preg_match_all(), preg_grep() in PHP
tmpfile() in PHP
Renaming and Removing Files in PHP
Locking files in PHP
File Handling in PHP
Handling BLOB in PHP and MySQL
Using Sessions in PHP
Using Cookies in PHP
Using PEAR::Crypt_HMAC in PHP
Using HMAC Verification in PHP
Input Validation in PHP
__autoload() METHOD in PHP

More Tutorials in PHP
PHP code to import from CSV file to MySQL
PHP code to write to a CSV file from MySQL query
PHP code to write to a CSV file for Microsoft Applications
Convert XML to CSV in PHP
Password must include both numeric and alphabetic characters - Magento
PHP file upload (Large Files)
PHP file upload prompts authentication for anonymous users
PHP file upload with IIS on windows XP/2000 etc
Error: Length parameter must be greater than 0
Multiple File Upload in PHP using IFRAME
Resume or Pause File Uploads in PHP
Exception in module wampmanager.exe at 000F15A0 in Windows 8
Handling file locks in PHP
HTML table output using Nested for loops in PHP
Count occurrences of a character in a String in PHP

More Latest News
Most Viewed Articles (in PHP )
A Basic Example using PHP in AWS (Amazon Web Services)
isset() function in PHP
Upload and Download files with FTP in PHP
Parent: child process exited with status 3221225477 -- Restarting
Different versions of PHP - History and evolution of PHP
GDBM, NDBM, DB2, DB3, DBM, and CDB Databases in PHP
Sorting an Array in PHP
Reading .CSV file in PHP
Using Text file as database in PHP
Generate random timestamp between two dates
Function to return number of digits of an integer in PHP
Count occurrences of a character in a String in PHP
Resume or Pause File Uploads in PHP
The Object (compound) Type in PHP
Variables in PHP
Most Emailed Articles (in PHP)
Integers and Floating-Point Numbers in PHP
Appending One Array to Another in PHP
Reading .CSV file in PHP
Perl's Encoding::FixLatin equivalent in PHP
Handling BLOB in PHP and MySQL
File Handling in PHP
Reading word by word from a file in PHP
Upload and Download files with FTP in PHP
History and origin of PHP
Variables in PHP
Strings in PHP
Using list() in PHP
switch Statements in PHP
do...while Loops in PHP
Destructors in PHP