Storing uploaded image in PostgreSQL as a binary 'bytea' type

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am trying to write script that is run when a form is submitted. The
form contains an image input field and when submitted, the image is
uploaded, resized and added as binary information to a db table. Please
note, I am using a PostgreSQL database

I have written all the code out below that deals with the submission

$tmpfilesize = $_FILES['imgfile']['size'];
$tmpfilename = $_FILES['imgfile']['tmp_name'];
$tmpfiletype = $_FILES['imgfile']['type'];

if ($tmpfilesize > 0) {

  //check this is an image by using the file type info
  if (substr($tmpfiletype, 0, 6) == 'image/') {

  //create image from uploaded image
  switch ($tmpfiletype)
  case "image/jpeg":
  case "image/pjpeg":
    $img = imagecreatefromjpeg($tmpfilename);
  case "image/gif":
    $img = imagecreatefromgif($tmpfilename);
  case "image/png":
    $img = imagecreatefrompng($tmpfilename);

  //resize image
  $imginfo = getimagesize($tmpfilename);
  $width   = $imginfo[0];
  $height  = $imginfo[1];
  $maxsize = 200;
  if (($width > $maxsize) || ($height > $maxsize)) {
    $ratio = max($width, $height) / $maxsize;
    $newwidth = floor($width / $ratio);
    $newheight = floor($height / $ratio);
    $newimg = imagecreatetruecolor($newwidth, $newheight);
    imagecopyresampled($newimg, $img, 0, 0, 0, 0, $newwidth,
$newheight, $width, $height);
    $img = $newimg;

  //prepare image for database
  imagejpeg($img, '', 80);
  $imgdata = pg_escape_string(ob_get_contents());

  //write to db
  $curdate = date("Y-m-d H:i:s");
  $insert = "INSERT INTO imglib (createdon, imgdata) VALUES ('" .
$curdate . "', '" . $imgdata . "')";
  pg_exec($db, $insert);

I have included all the code just encase but the only line that is
giving any problems is:

pg_exec($db, $insert);

I get the following error:

Warning: pg_exec(): Query failed: ERROR: unterminated quoted string at
or near "'ÿØÿà" at character 89 in
on line 89

If it helps also, I used the following SQL to CREATE the DB table:

CREATE TABLE imglib (imgid serial, createdon timestamp, imgdata bytea);

I am very new to storing images. When I comment out the pg_exec() it
doesnt give me any problems which suggests that everything goes well
(apart from the fact that it doesnt write to db). Im guessing that the
fault is in the preperation of the image data prior to be written to
db. Is bytea the correct data type for this? I have successfully stored
an image as a BLOB to MySQL and the code above is a slightly modified
version of that code. I dont want to convert the code to a string of
byte64 as this will creates uneccessary overhead.

Any suggestions? Cheers


Re: Storing uploaded image in PostgreSQL as a binary 'bytea' type

Search this newsgroup first.
If memory serves me well, this very question has been on this ng a few times  
the last few weeks alone.

Tip: Use google discussiongroup search capabilities.

Erwin Moller

Re: Storing uploaded image in PostgreSQL as a binary 'bytea' type

Long question, short answer...

The problem was that I was using pg_escape_string() instead of
pg_escape_bytea(). I got pg_escape_bytea() mistaken for something that
'escaped' the format from a bytea type to something else (like a
string) or is that still what it is doing? Works now anyway.


Site Timeline