PHP Data Objects (PDO) Step By Step Tutorial - Part 12: At previous post, we have talked about BLOB. We practice how to insert data and retrieve data. Now, we talk alternative for retrieve data.
Just remembering, we use this code to retrieve data at previous post:
// 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/>";
}
At line 13, we create a file. Content of this file from BLOB data. Then we call this file at line 14. With this way, we always create file physically.
Alternative way, we can generate 'virtual' file on the fly. Create a file named "cover.php" with this code:
<?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 cover FROM books WHERE id=".$_GET['id'];
$q = $conn->prepare($sql);
$q->execute();
$q->bindColumn(1, $cover, PDO::PARAM_LOB);
$q->fetch(PDO::FETCH_BOUND);
header("Content-Type: image/png");
echo $cover;
?>
This page only show a image. Then we update retrieving data like this:
<?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())
{
echo "$title, $author, <img src='cover.php?id=".$id."'> <br/>";
}
?>
In this tutorial, I just want to show how to retrieve data from BLOB field. For looping, it is not efficient use above ways for production.
