Programming Tutorials

Handling BLOB in PHP and MySQL

By: Andi, Stig and Derick in PHP Tutorials on 2008-11-23  

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;
?>