Current Location: Home> Latest Articles> Handling Binary Data in Databases with PHP and PDO: A Complete Guide

Handling Binary Data in Databases with PHP and PDO: A Complete Guide

M66 2025-06-26

Managing Binary Data in Databases Using PHP and PDO

When building modern web applications, you often need to store binary content—such as images, audio, or video—directly in a database. PHP’s PDO (PHP Data Objects) extension offers a secure and efficient way to perform these operations. This guide walks you through how to connect to a database and perform read, insert, update, and delete operations for binary data using PDO.

Connecting to the Database with PDO

The first step is establishing a connection with the database. Below is a basic example of how to connect to a MySQL database using PDO:


$dsn = 'mysql:host=localhost;dbname=mydb';
$username = 'username';
$password = 'password';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected to database successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Reading Binary Data from the Database

Let’s say you have a photos table where one column, photo_data, stores binary image data. Here's how you can fetch and output an image stored in the database:


$query = "SELECT photo_data FROM photos WHERE id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

$photoData = $result['photo_data'];

header('Content-Type: image/jpeg');
echo $photoData;

Inserting Binary Data into the Database

To store binary data such as an image file, you can read the file content and insert it into the database using the following code:


$photoData = file_get_contents('path/to/photo.jpg');

$query = "INSERT INTO photos (photo_data) VALUES (?)";
$stmt = $pdo->prepare($query);
$stmt->bindParam(1, $photoData, PDO::PARAM_LOB);
$stmt->execute();

echo "Photo data inserted into database successfully";

Updating Existing Binary Data

To update a binary record that already exists in the database, use this example:


$photoData = file_get_contents('path/to/new_photo.jpg');

$query = "UPDATE photos SET photo_data = ? WHERE id = ?";
$stmt = $pdo->prepare($query);
$stmt->bindParam(1, $photoData, PDO::PARAM_LOB);
$stmt->bindParam(2, $id);
$stmt->execute();

echo "Photo data updated successfully";

Deleting Binary Data from the Database

If you need to remove binary data that’s no longer required, the following snippet will do the job:


$query = "DELETE FROM photos WHERE id = :id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':id', $id);
$stmt->execute();

echo "Photo data deleted successfully";

Conclusion

By using the PDO extension in PHP, developers can efficiently manage binary data in MySQL or other supported databases. This article provided practical examples for reading, writing, updating, and deleting binary content—skills that are essential for building features like image uploads, media storage, and more within your application.

Related