Handling BLOB in PHP and MySQL

By: Andi, Stig and Derick Viewed: 153309 times    

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;

Most Viewed Articles (in PHP )

Latest Articles (in PHP)

Comment on this tutorial

Subscribe to Tutorials

Related Tutorials

Archived Comments

1. Hi Andi, Stig and Derick!

Nice tur

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

2. I'm working on an intranet site/web application. W
View Tutorial          By: Tony Gingrich at 2011-10-23 21:22:31

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

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

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

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

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

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