How to Store and Retrieve Files in MySQL BLOB with PHP - Python
Binary Large Objects (BLOBs) allow you to store binary data such as images, documents, and videos directly in a MySQL database. In this tutorial, we will cover how to store and retrieve files in MySQL using BLOB data type, with examples in PHP and Python.
Step 1: Create a Table with a BLOB Column
First, create a table in MySQL to store file metadata and the file content.
CREATE TABLE files (
id INT AUTO_INCREMENT PRIMARY KEY,
file_name VARCHAR(255) NOT NULL,
file_data BLOB NOT NULL
);
Step 2: Storing Files in MySQL
Using PHP
Below is a PHP script to upload and store files in the files table.
PHP Script
<?php
// Database connection
$host = 'localhost';
$dbname = 'your_database';
$username = 'root';
$password = '';
$conn = new mysqli($host, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// File upload logic
if (isset($_FILES['file'])) {
$fileName = $_FILES['file']['name'];
$fileData = file_get_contents($_FILES['file']['tmp_name']);
// Prepare and execute SQL statement
$stmt = $conn->prepare("INSERT INTO files (file_name, file_data) VALUES (?, ?)");
$stmt->bind_param("sb", $fileName, $fileData);
$stmt->send_long_data(1, $fileData); // Send BLOB data
$stmt->execute();
echo "File uploaded successfully.";
}
?>
<!-- HTML form for file upload -->
<form action="" method="post" enctype="multipart/form-data">
<input type="file" name="file" required>
<button type="submit">Upload</button>
</form>
Using Python
Below is a Python script using the mysql.connector library to store files in the database.
Python Script
import mysql.connector
# Database connection
conn = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="your_database"
)
cursor = conn.cursor()
# Function to store a file in the database
def upload_file(file_path):
with open(file_path, "rb") as file:
file_data = file.read()
file_name = file_path.split("/")[-1]
sql = "INSERT INTO files (file_name, file_data) VALUES (%s, %s)"
cursor.execute(sql, (file_name, file_data))
conn.commit()
print(f"File '{file_name}' uploaded successfully.")
# Upload a file
upload_file("example.pdf")
# Close connection
cursor.close()
conn.close()
Step 3: Retrieving Files from MySQL
Using PHP
The following PHP script retrieves and downloads files stored in the database.
PHP Script
<?php
// Database connection
$host = 'localhost';
$dbname = 'your_database';
$username = 'root';
$password = '';
$conn = new mysqli($host, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Retrieve file by ID
$fileId = 1; // Example file ID
$stmt = $conn->prepare("SELECT file_name, file_data FROM files WHERE id = ?");
$stmt->bind_param("i", $fileId);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($fileName, $fileData);
$stmt->fetch();
// Output the file
header("Content-Disposition: attachment; filename=$fileName");
header("Content-Type: application/octet-stream");
echo $fileData;
?>
Using Python
The following Python script retrieves files from the database and saves them locally.
Python Script
import mysql.connector
# Database connection
conn = mysql.connector.connect(
host="localhost",
user="root",
password="",
database="your_database"
)
cursor = conn.cursor()
# Function to retrieve a file
def download_file(file_id, output_path):
sql = "SELECT file_name, file_data FROM files WHERE id = %s"
cursor.execute(sql, (file_id,))
result = cursor.fetchone()
if result:
file_name, file_data = result
with open(f"{output_path}/{file_name}", "wb") as file:
file.write(file_data)
print(f"File '{file_name}' downloaded successfully.")
else:
print("File not found.")
# Download a file
download_file(1, "downloads")
# Close connection
cursor.close()
conn.close()
Step 4: Testing the Implementation
Run the file upload script (PHP or Python) to store a file in the database.
Check the database table to confirm the file is stored.
Run the file retrieval script (PHP or Python) to download the file.
Verify that the file is downloaded correctly.
Use Appropriate BLOB Type: Use TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB depending on the file size.
Store Metadata Separately: Keep large files in a dedicated table to avoid performance issues.
Consider Alternatives: For very large files, consider storing files in a filesystem and saving file paths in the database.
Security Measures: Sanitize file inputs and validate MIME types to prevent malicious uploads.
This tutorial covers the basics of storing and retrieving files in MySQL using BLOB with examples in PHP and Python. Hope this is helpful, and I apologize if there are any inaccuracies in the information provided.
Comments
Post a Comment