PDO: Working With BLOBs

PHP Data Objects (PDO) Step By Step Tutorial - Part 11: We can store images or other document into database (such as MySQL). For this job, we will work with BLOB field in table. Ok, let's do it!

First, create a table that have BLOB file. I will create table named "books":

CREATE TABLE `books` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  `author` varchar(150) NOT NULL,
  `description` varchar(255) NOT NULL,
  `on_sale` tinyint(1) NOT NULL,
  `cover` blob NOT NULL,
  PRIMARY KEY  (`id`)
);

Sample for insert data:

<?php
// configuration
$dbtype		= "sqlite";
$dbhost 	= "localhost";
$dbname		= "test";
$dbuser		= "root";
$dbpass		= "admin";

// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

// new data
$title = "ZEND FRAMEWORK TUTORIAL";
$author = "PHP Everyday";
$cover = fopen('7.png','rb');
// query
$sql = "INSERT INTO books (title,author,cover) values(?,?,?)";

$q = $conn->prepare($sql);
$q->bindParam(1, $title);
$q->bindParam(2, $author);
$q->bindParam(3, $cover, PDO::PARAM_LOB);

$q->execute();

?>

Ok, now, this is sample for retrieve data:

<?php
// configuration
$dbtype		= "sqlite";
$dbhost 	= "localhost";
$dbname		= "test";
$dbuser		= "root";
$dbpass		= "admin";

// database connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

// query
$sql = "SELECT id,title,author,cover FROM books";
$q = $conn->prepare($sql);
$q->execute();

$q->bindColumn(1, $id);
$q->bindColumn(2, $title);
$q->bindColumn(3, $author);
$q->bindColumn(4, $cover, PDO::PARAM_LOB);

while($q->fetch())
{
file_put_contents($id.".png",$cover);
echo "$title, $author, <img src='".$id.".png'> <br/>";
}

?>

It is clear, isn't it?

Previous: PDO: Prepared Statement and Bound Values
Next: PDO: Alternative Retrieve BLOB Data