PHP MySQL BLOB

PHP MySQL: Working with BLOBs

A BLOB (Binary Large Object) is a data type in MySQL used to store binary data such as images, audio, video, or other multimedia files. MySQL provides four types of BLOBs: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, differing in their maximum storage capacities.

This guide will demonstrate how to insert, retrieve, and display BLOB data in MySQL using PHP.

1. MySQL Table with BLOB Column

First, create a table to store BLOB data. For example:

CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    file_data LONGBLOB NOT NULL
);

2. Inserting BLOB Data into MySQL

To insert a file (e.g., an image) into a BLOB column, read the file as binary data and use a prepared statement to store it.

Example: Upload File to Database

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $file = $_FILES['file']['tmp_name'];
    $fileName = $_FILES['file']['name'];
    $fileData = file_get_contents($file);

    // Prepare and bind
    $stmt = $conn->prepare("INSERT INTO files (name, file_data) VALUES (?, ?)");
    $stmt->bind_param("sb", $fileName, $null);

    // Send file data as a stream
    $stmt->send_long_data(1, $fileData);

    if ($stmt->execute()) {
        echo "File uploaded successfully!";
    } else {
        echo "Error: " . $stmt->error;
    }

    $stmt->close();
}

$conn->close();
?>

<!DOCTYPE html>
<html>
<head>
    <title>Upload File</title>
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <label for="file">Choose a file:</label>
        <input type="file" name="file" id="file" required>
        <button type="submit">Upload</button>
    </form>
</body>
</html>3. Retrieving BLOB Data from MySQL

To retrieve and display the stored BLOB data (e.g., an image):

Example: Display Images

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT id, name, file_data FROM files";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "<h3>" . htmlspecialchars($row['name']) . "</h3>";
        echo '<img src="data:image/jpeg;base64,' . base64_encode($row['file_data']) . '" alt="File" width="300"><br>';
    }
} else {
    echo "No files found.";
}

$conn->close();
?>

4. Deleting BLOB Data from MySQL

To delete a file from the database:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "example_db";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$fileId = 1; // ID of the file to delete
$sql = "DELETE FROM files WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $fileId);

if ($stmt->execute()) {
    echo "File deleted successfully!";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

5. Best Practices for Using BLOBs

Use Appropriate BLOB Type:
- TINYBLOB: Up to 255 bytes.
- BLOB: Up to 64 KB.
- MEDIUMBLOB: Up to 16 MB.
- LONGBLOB: Up to 4 GB.
Consider File Storage Alternatives:
- Store file paths in the database and save the files on the server's filesystem to reduce database size.
Optimize Queries:
- Retrieve only the required BLOB data to minimize memory usage.
Security:
- Sanitize file names and validate file types to prevent malicious uploads.
- Use HTTPS to secure file uploads and downloads.
Error Handling:
- Always handle database errors and validate user inputs.

6. Pros and Cons of Storing Files in BLOBs

Pros:

Centralized storage in the database.
Simplifies backup and data synchronization.

Cons:

This can lead to large database sizes.
Performance impact on query execution and memory usage.
Harder to serve files directly from the database.

Let me know if you need further assistance or customization!

Souy Soeng