To work with BLOBs using PDO in PHP, you can follow the steps below:
Connect to the database:
$dsn = 'mysql:host=localhost;dbname=your_database;charset=utf8mb4';
$username = 'your_username';
$password = 'your_password';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}
Create a table with a BLOB column:
$sql = "CREATE TABLE your_table (id INT AUTO_INCREMENT PRIMARY KEY, data BLOB)";
$pdo->exec($sql);
Insert a BLOB into the table:
$file = 'path_to_your_file.jpg'; // Path to the file you want to insert
$sql = "INSERT INTO your_table (data) VALUES (:data)";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':data', file_get_contents($file), PDO::PARAM_LOB);
$stmt->execute();
Retrieve a BLOB from the table:
$id = 1; // ID of the row containing the BLOB
$sql = "SELECT data FROM your_table WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
if ($stmt->rowCount() > 0) {
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$blobData = $row['data'];
// Use the $blobData as needed
}
Update a BLOB in the table:
$id = 1; // ID of the row containing the BLOB
$newFile = 'path_to_your_new_file.jpg'; // Path to the updated file
$sql = "UPDATE your_table SET data = :data WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':data', file_get_contents($newFile), PDO::PARAM_LOB);
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
Delete a BLOB from the table:
$id = 1; // ID of the row containing the BLOB
$sql = "DELETE FROM your_table WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$stmt->execute();
Remember to adjust the table, column, and file paths based on your specific needs.