Manage MySQL BLOB Data using PHP PDO

A MySQL BLOB column stores binary data such as an image. With PDO, bind the binary value to a prepared statement using PDO::PARAM_LOB.

require 'config.php';

$id=8;
$student='Alex';
$photo=fopen(
    'photos/1.png',
    'rb'
);

$sql="INSERT INTO student_profile
      (id,student,profile_photo)
      VALUES
      (:id,:student,:profile_photo)";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':id',
    $id,
    PDO::PARAM_INT
);

$stmt->bindValue(
    ':student',
    $student,
    PDO::PARAM_STR
);

$stmt->bindParam(
    ':profile_photo',
    $photo,
    PDO::PARAM_LOB
);

$stmt->execute();

fclose($photo);

This combines a prepared INSERT statement with PDO large-object binding.

What Is a MySQL BLOB? Top ↑

BLOB means Binary Large Object. It is used for binary data that should not be treated as ordinary character text.

Examples include:

• images,
• small documents,
• binary application data,
• other file content.

MySQL provides several BLOB types with different capacities. See the MySQL BLOB data type tutorial for the SQL datatype itself.

Sample student_profile Table Top ↑

The existing Plus2net example uses three columns:

The profile record can be associated with the existing Plus2net student table.

Insert Binary Data into a BLOB with PDO Top ↑

This example reads a PNG file in binary mode and stores it in profile_photo.

require 'config.php';

$id=8;
$student='Alex';

$photo=fopen(
    'photos/1.png',
    'rb'
);

if($photo===false){
    exit('Unable to open image file.');
}

try{
    $sql="INSERT INTO student_profile
          (id,student,profile_photo)
          VALUES
          (:id,:student,:profile_photo)";

    $stmt=$dbo->prepare($sql);

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->bindValue(
        ':student',
        $student,
        PDO::PARAM_STR
    );

    $stmt->bindParam(
        ':profile_photo',
        $photo,
        PDO::PARAM_LOB
    );

    $stmt->execute();

    echo 'Photo was stored.';
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to save the photo.';
}finally{
    fclose($photo);
}

The binary stream is supplied through PDO::PARAM_LOB instead of being concatenated into the SQL statement.

fopen() vs file_get_contents() for BLOB Data Top ↑

Binary data can also be read into a PHP string:

$photo=file_get_contents(
    'photos/1.png'
);

if($photo===false){
    exit('Unable to read image.');
}

$stmt->bindValue(
    ':profile_photo',
    $photo,
    PDO::PARAM_LOB
);

file_get_contents() is simple for small files but loads the complete file into PHP memory.

Using:

$photo=fopen(
    'photos/1.png',
    'rb'
);

provides a stream resource. This can be preferable when working with larger binary values, although actual buffering behavior can also depend on the PDO driver.

Retrieve BLOB Data using PDO Top ↑

Select the binary field just like another database column.

$id=8;

$stmt=$dbo->prepare(
    "SELECT id,student,profile_photo
     FROM student_profile
     WHERE id=:id"
);

$stmt->bindValue(
    ':id',
    $id,
    PDO::PARAM_INT
);

$stmt->execute();

$row=$stmt->fetch(
    PDO::FETCH_ASSOC
);

This uses a prepared SELECT query and WHERE condition.

Display a BLOB Image with Base64 Top ↑

For a small image, the binary data can be Base64-encoded and placed inside an HTML data URI.

If the stored sample is PNG:

if($row && $row['profile_photo']!==null){
    $image=base64_encode(
        $row['profile_photo']
    );

    echo "<img
        src='data:image/png;base64,$image'
        alt='Student profile photo'
        class='img-fluid'>";
}

Display Several Records Top ↑

$sql="SELECT id,student,profile_photo
      FROM student_profile
      ORDER BY id";

$stmt=$dbo->query($sql);

echo '<table class="table table-striped">';
echo '<tr><th>ID</th><th>Name</th><th>Photo</th></tr>';

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $id=(int)$row['id'];

    $student=htmlspecialchars(
        (string)$row['student'],
        ENT_QUOTES,
        'Windows-1252'
    );

    $image='';

    if($row['profile_photo']!==null){
        $base64=base64_encode(
            $row['profile_photo']
        );

        $image="<img
            src='data:image/png;base64,$base64'
            alt='Profile photo for $student'
            width='100'>";
    }

    echo "<tr>
        <td>$id</td>
        <td>$student</td>
        <td>$image</td>
        </tr>";
}

echo '</table>';

The ORDER BY clause keeps the records in a predictable sequence.

Send BLOB Data Directly to the Browser Top ↑

Base64 data URIs are convenient for demonstrations, but they increase the encoded size and place the complete image inside the HTML document.

A separate image-response script can instead return the binary data directly.

<?php
require 'config.php';

$id=filter_input(
    INPUT_GET,
    'id',
    FILTER_VALIDATE_INT,
    [
        'options' => [
            'min_range' => 1
        ]
    ]
);

if($id===false || $id===null){
    http_response_code(400);
    exit;
}

$stmt=$dbo->prepare(
    "SELECT profile_photo
     FROM student_profile
     WHERE id=:id"
);

$stmt->bindValue(
    ':id',
    $id,
    PDO::PARAM_INT
);

$stmt->execute();

$photo=$stmt->fetchColumn();

if($photo===false || $photo===null){
    http_response_code(404);
    exit;
}

header('Content-Type: image/png');
echo $photo;
exit;

This approach avoids Base64 expansion in the HTML page and lets the browser request the image as a separate resource.

Store the MIME Type with the BLOB Top ↑

The original sample table stores only the binary data. That is sufficient only when the application already knows the format.

A more flexible table can include the MIME type:

CREATE TABLE student_profile_new (
    id INT PRIMARY KEY,
    student VARCHAR(50) NOT NULL,
    profile_photo MEDIUMBLOB,
    mime_type VARCHAR(50)
);

When an uploaded file is validated, store the server-detected MIME type:

$sql="INSERT INTO student_profile_new
      (id,student,profile_photo,mime_type)
      VALUES
      (:id,:student,:profile_photo,:mime_type)";

$stmt=$dbo->prepare($sql);

$stmt->bindValue(
    ':mime_type',
    $mime,
    PDO::PARAM_STR
);

When serving the file, do not blindly send any arbitrary value as an HTTP header. Validate the stored value against the MIME types your application supports.

$allowed_mimes=[
    'image/jpeg',
    'image/png',
    'image/webp'
];

if(
    !in_array(
        $row['mime_type'],
        $allowed_mimes,
        true
    )
){
    http_response_code(415);
    exit;
}

header(
    'Content-Type: '.
    $row['mime_type']
);

Update a MySQL BLOB with PDO Top ↑

Updating binary data uses the same LOB binding technique with an UPDATE statement.

$id=1;

$photo=fopen(
    'photos/2.png',
    'rb'
);

if($photo===false){
    exit('Unable to open replacement image.');
}

try{
    $sql="UPDATE student_profile
          SET profile_photo=:profile_photo
          WHERE id=:id";

    $stmt=$dbo->prepare($sql);

    $stmt->bindValue(
        ':id',
        $id,
        PDO::PARAM_INT
    );

    $stmt->bindParam(
        ':profile_photo',
        $photo,
        PDO::PARAM_LOB
    );

    $stmt->execute();

    echo 'Photo was updated.';
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to update the photo.';
}finally{
    fclose($photo);
}

For a single independent UPDATE, an explicit transaction is normally unnecessary. Use a transaction when the BLOB update must succeed together with other dependent database changes.

Empty or NULL BLOB Values Top ↑

If the schema permits a profile without an image, store an actual SQL NULL rather than an arbitrary empty string.

$photo=null;

$stmt=$dbo->prepare(
    "UPDATE student_profile
     SET profile_photo=:photo
     WHERE id=:id"
);

$stmt->bindValue(
    ':photo',
    null,
    PDO::PARAM_NULL
);

$stmt->bindValue(
    ':id',
    1,
    PDO::PARAM_INT
);

$stmt->execute();

The column itself must allow NULL for this example. See the SQL NULL tutorial for NULL handling at the database level.

MySQL BLOB Types and Maximum Sizes Top ↑

The theoretical datatype limit does not mean the application can automatically transfer a file of that size. PHP memory limits, upload settings, MySQL packet limits and application design can impose much smaller practical limits.

BLOB Performance and PHP Memory Top ↑

Binary data can be much larger than ordinary database fields, so retrieve it only when needed.

A page that needs only student names should not use:

SELECT *
FROM student_profile;

if that also transfers every stored photo.

Prefer:

SELECT id,student
FROM student_profile
ORDER BY id;

and load the BLOB only when the image is actually requested.

Base64 Has Additional Size Overhead Top ↑

Base64 encoding increases the amount of text required to represent the binary file. It is convenient for small tutorial examples but is not automatically the best approach for a page containing many or large images.

Store the BLOB or Store the File Path? Top ↑

Both designs can be valid depending on the application.

There is no rule that every uploaded image should be a BLOB. Choose the storage model according to the application's access, backup, performance and delivery requirements.

Upload an Image and Store It in a BLOB Top ↑

When the binary data comes from a visitor rather than a known local file, validate the upload before sending it to the database.

The upload workflow is:

Browser form
    |
    v
$_FILES
    |
    v
Check upload error
    |
    v
Check size
    |
    v
Detect MIME type
    |
    v
Validate image
    |
    v
Open binary stream
    |
    v
PDO::PARAM_LOB
    |
    v
MySQL BLOB

Common PDO BLOB Problems Top ↑

Using the Wrong Content-Type Top ↑

A PNG BLOB must not be sent as image/jpeg. Either use one known format or store the validated MIME type with each record.

Ignoring the BLOB Size Limit Top ↑

A normal BLOB is limited to about 64 KB. Use a larger datatype or another storage strategy for larger files.

Selecting Every BLOB with SELECT * Top ↑

Binary columns can be large. Do not retrieve them on listing pages when the page needs only ordinary fields.

Displaying Raw Database Errors Top ↑

Log PDO exceptions on the server and show visitors a short message rather than exposing errorInfo().

Not Escaping Other Database Values Top ↑

The BLOB itself may be binary, but fields such as student names must still be escaped before inserting them into HTML.

Using Base64 for Every Large Image Top ↑

Base64 is useful for small examples but increases data size and places the binary content inside the HTML. Direct image responses are often a better design for larger media.

Forgetting to Close File Resources Top ↑

When a binary file is opened using fopen(), close the resource after the database operation.

Assuming PARAM_LOB Removes the Need for File Validation Top ↑

PDO::PARAM_LOB controls parameter handling. It does not check file type, file size, upload validity or application authorization.

Related PHP PDO Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: