Php : Convert a blob into an image file

Php : Convert a blob into an image file

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Converting MySQL BLOBs to Image Files in PHP

Storing binary data directly within a relational database is a common pattern, often used for user avatars, document previews, or cached thumbnails. However, retrieving that data as a raw string isn't always sufficient; many applications require the binary content to be written to disk as a physical image file (JPG, PNG, etc.). While PHP lacks a native BLOB type for variables like some other languages might suggest, it provides robust functions to handle the conversion seamlessly. The core mechanism relies on treating the database column not as text, but as a stream of bytes.

To perform this conversion, you first need to fetch the binary data from your MySQL instance. Whether you are using PDO or the legacy mysqli extension, the critical step is ensuring the driver returns the raw binary string rather than attempting to interpret it as text. With PDO, you achieve this by setting the attribute PDO::MYSQL_ATTR_USE_BUFFERED_QUERY appropriately and, more importantly, selecting the correct fetch mode or relying on the default behavior which preserves binary data in PHP 7.4+.

Here is a practical example using PDO to retrieve the BLOB and write it directly to a file:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=images', 'user', 'pass');
// Ensure no emulation of prepared statements interferes with binary data handling in some drivers
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("SELECT image_data FROM images WHERE id = ?");
$stmt->execute([123]);
$row = $stmt->fetch();

if ($row) {
    $binaryData = $row['image_data']; // This is raw binary data
    
    $filePath = '/var/www/html/uploads/avatar_' . $row['id'] . '.jpg';
    
    // Write the binary string directly to a file
    if (file_put_contents($filePath, $binaryData)) {
        echo "Image saved successfully.";
    } else {
        echo "Failed to write image to disk.";
    }
}
?>

When using MySQLi, the approach is slightly different because mysqli_fetch_array might return strings depending on the fetch mode. You must ensure you are fetching the raw value without converting it to a string representation that loses binary integrity. Often, using fetch_column() or ensuring the result is treated as an object/array with specific type hints helps, but in modern PHP versions, standard array access usually preserves the binary nature of BLOB columns if fetched via prepared statements.

Handling File Extensions and Content Types

A frequent pitfall is assuming the database column name implies the file extension. If your schema stores images as image_data, you cannot automatically append .jpg or .png without metadata. You must determine the correct MIME type before saving the file to ensure web servers serve it correctly.

You can inspect the content using PHP's built-in functions, though this requires reading a portion of the file stream. Alternatively, store the file extension in a separate column (e.g., image_extension) alongside the BLOB. This adds a schema constraint but prevents ambiguity. If you rely solely on the binary data, you might use the finfo extension to detect the MIME type:

$fp = fopen($filePath, 'rb');
$finfo = finfo_open(FILEINFO_MIME_TYPE);
$mimeType = finfo_file($finfo, $fp);
fclose($fp);
// mimeType will be something like image/jpeg or image/png

Storage Considerations and Trade-offs

While storing images in a BLOB column is convenient for application logic—allowing you to manipulate the image without touching the filesystem immediately—it introduces significant performance overhead. The database becomes responsible for I/O operations, which it is not optimized for compared to the file system. As your collection of images grows, query times will degrade because the database engine must read large chunks of disk space into memory for every fetch.

Furthermore, managing versions or deleting old images requires updating the database row and then manually cleaning up the filesystem if you ever implemented a dual-write strategy. For high-volume applications, it is often better to store only the file path in the database and keep the actual image files on the server's disk or an object storage service like AWS S3.

However, for small-scale projects or specific use cases where data integrity within the transactional database is paramount, converting a BLOB to an image file remains a valid and straightforward operation using the methods described above. The key is always ensuring that the binary data is handled as bytes, not text, throughout the entire retrieval and writing process.

Our network