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?
