PHP PDO: Alternative Retrieve BLOB Data

Now, 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:

<?php

// configuration

$dbhost        = "localhost";

$dbname        = "pdo";

$dbuser        = "root";

$dbpass        = "";

// database connection

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

// query

$sql = "SELECT id,title,author,cover FROM books2";

$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); //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.

while($q->fetch())

{

file_put_contents($id.".jpg",$cover);

echo "$title, $author, <img src='".$id.".jpg'> <br/>";

}

?>

Alternative way, we can generate ‘virtual’ file on the fly. Create a file named “cover.php” with this code:

<?php

// configuration

$dbhost        = "localhost";

$dbname        = "pdo";

$dbuser        = "root";

$dbpass        = "";

// database connection

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

// query

$sql = "SELECT cover FROM books2 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

$dbhost        = "localhost";

$dbname        = "pdo";

$dbuser        = "root";

$dbpass        = "";

// database connection

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

// query

$sql = "SELECT id,title,author,cover FROM books2";

$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/>";

}

?>

I just want to show how to retrieve data from BLOB field. For looping, it is not efficient use above ways for production.