Current Location: Home> Latest Articles> Efficient Handling of BLOB and CLOB Data Types in PHP Using PDO

Efficient Handling of BLOB and CLOB Data Types in PHP Using PDO

M66 2025-06-11

Overview

In PHP, PDO (PHP Data Objects) is a powerful and flexible database access interface that supports multiple database types. When dealing with columns containing binary data (BLOB) or large text data (CLOB), special handling is required. This article explains in detail how to use PDO to handle these two data types with illustrative code examples.

Introduction to PDO

PDO provides a unified object-oriented API supporting databases such as MySQL, SQLite, and PostgreSQL, simplifying cross-database development. Using PDO, developers can write more secure and maintainable database interaction code.

How to Handle BLOB Data Type

BLOB (Binary Large Object) is used to store binary data like images, audio, and video files. With PDO, prepared statements and placeholders allow you to safely insert and retrieve this binary data.

// Connect to the database
$dsn = "mysql:host=localhost;dbname=test";
$user = "username";
$password = "password";

try {
    $pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
    exit;
}

// Save an image as BLOB
$imagePath = 'path/to/image.jpg';
$imageData = file_get_contents($imagePath);

$stmt = $pdo->prepare("INSERT INTO images (data) VALUES (:data)");
$stmt->bindParam(':data', $imageData, PDO::PARAM_LOB);
$stmt->execute();

// Retrieve and display the image from database
$stmt = $pdo->query("SELECT data FROM images LIMIT 1");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$imageData = $row['data'];

header("Content-type: image/jpeg");
echo $imageData;

How to Handle CLOB Data Type

CLOB (Character Large Object) is used to store large amounts of character data, such as long text or rich text content. In PDO, you can bind text data to placeholders using the bindValue method.

// Connect to the database
$dsn = "mysql:host=localhost;dbname=test";
$user = "username";
$password = "password";

try {
    $pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
    exit;
}

// Save long text as CLOB
$textContent = "This is a long text";

$stmt = $pdo->prepare("INSERT INTO texts (content) VALUES (:content)");
$stmt->bindValue(':content', $textContent, PDO::PARAM_STR);
$stmt->execute();

// Retrieve and display the long text from database
$stmt = $pdo->query("SELECT content FROM texts LIMIT 1");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$textContent = $row['content'];

echo $textContent;

Conclusion

Handling BLOB and CLOB data types with PDO requires proper methods to ensure data security and integrity. For BLOB data, bindParam is recommended to bind binary data; for CLOB data, bindValue is used to bind character data. The examples provided offer clear code references for developers to adapt and extend according to their needs.

Related