PDO insert image in db and retrieve the image

Question

I am trying to insert image in DB but for some reasons I could not retrieve it. I am using BLOB type for images in database.

Code I am using to upload image. Once I save image in db, in "IMAGE" column I get file image-01.bin

if (isset($_POST["submit"]) {

  $id= $_GET["id"];
  $image = $_POST["image"];

  $stmt=$conn->prepare("INSERT INTO db (id, image) VALUES (:id, :image)");

  $stmt->bindParam(:id, $id);
  $stmt->bindParam(:image, $image);
  $stmt->execute();
}

Code I am using to print image in web page.

$stmt = "Select Id, Image from db where id = $id LIMIT 0,1"

$q = $conn->query($stmt);

while ($r = $q ->fetch():
  echo "<img src ='",$r[Image],"' width='100' height='100' />";
endwhile;

The binary image data is "in" $_POST['image']? Possible, but ... how did you manage to do that? — VolkerK, Jan 21 '15 at 09:02
you have to read the image content to put it in a blob ... look at http://stackoverflow.com/questions/23854070/pdo-insert-image-into-database-directly-always-inserting-blob-0b — Gerard Rozsavolgyi, Jan 21 '15 at 09:02
It is not recommended to overload the database with image data, the best is to fill the database with the url of the image, and to store in in the same time the image in a folder you always know its location. More, if you do this, you could avoid re-upload a similar image by checking its name (prevent user from clicking twice the upload button). Check this link http://www.w3schools.com/php/php_file_upload.asp for uploading in folder an image. — Anwar, Jan 21 '15 at 09:06
Are you storing the actual image data in the database or just a path to a file? — GordonM, Jan 21 '15 at 09:07
I agree with @Zeratops. Store the path to the image rather than storing the data of the image itself. Then simply pull the image path from the database and output the image stored at that path to the user. — Spencer D, Jan 21 '15 at 09:09
if i dont remember wrongly, you need to decode the image after take from db, try base64_decode($image); — onegun, Jan 21 '15 at 09:12

score 0 · Answer 1 · answered Dec 27 '16 at 03:38

You can bind your select as a LOB type similar to example 1 as shown here: http://php.net/manual/en/pdo.lobs.php

Here's the example - note that it binds the selected column and sets it as PDO::PARAM_LOB:

<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

header("Content-Type: $type");
fpassthru($lob);

PDO insert image in db and retrieve the image

1 Answers1