Postgresql – handling bytea data through php

postgres 9.1 / php 5.3.10 on ubuntu.

The combination of postgres and php has not made it easy and intuitive to handle blob-data. I tried first to use the large objects, as that seemed to be the easiest way, but the bytea turned out to be the best solution – having read up a few places, I have found this to work, at least for not-too-large-files where the size “too large” has to be determined for the actual application. (a few megs should hardly ever be “too large”)

I have a table defined as

create table blobstore(
   id serial primary key,
   doc varchar,
   blob bytea);

the following php-code allows storage and reading of images in this table
NOTE: This is a “proof of concept”-script. For real use, more checking must be added.

<?php
  $dsn = 'pgsql:dbname=test;host=127.0.0.1';
  $user = 'user';
  $password = 'password'; 
   // these variables must be set to match the actual connection
  try {
    $dbh = new PDO($dsn, $user, $password);
  } catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
  }
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Stores a file:
  if($_GET['file']){ 
  $filename=$_GET['file']; // Normally, the file will probably be uploaded, but that's another howto  ;-)
  $data = bin2hex(file_get_contents($filename)); // This may be a problem on too large files
  try{ 
    $sql="insert into blobstore (doc,blob) values('test',?)";
    $sqlh= $dbh->prepare($sql);
    $sqlh->execute(array($data));
  }
  catch(Exception $e){
    die($e->getMessage());
  }
  print("<p>Done</p>");

}
// reads a file out of the database:
if($_GET['id']){
  $sql="select blob from cddoc where id=?";
  $sqh=$dbh->prepare($sql);
  $sqh->execute(array($_GET['id']));
  $data=$sqh->fetchAll(PDO::FETCH_NUM);
  $data=$data[0][0]; // print($data) here will just return "Resource id # ..."
  header('Content-Type: image/png'); // must be adjusted accordingly for other file types, maybe filetype stored as a field in the table?
  $data=fgets($data); // The data are returned as a stream handle gulp all of it in in one go, again, this may need some serious rework for too large files 
  print(pack('H*',$data)); // reverses the bin2hex, no hex2bin in my php... ????
}

If this code is stored as e.g. blobtest.php, it can then be called (on a debian or ubuntu system) to store the image (when looking it up in the database it should look like \x38393530346534…)

http://localhost/blobtest.php?file=/usr/share/pixmaps/debian-logo.png

(/usr/share/pixmaps/debian-logo.png is a file that should exist on any debian or ubuntu system, if it does not exist on your system, just modify the url to point to an image file on the system)
To have the image displayed, run

http://localhost/blobtest.php?id=1 (or whatever image you want to restore)

The latter may be used directly in a web-page as

<img src="http://localhost/blobtest.php?id=1" />