Helpful Information
 
 
Category: PostgreSQL Help
Storing images in Postgresql database

I have ask this question a couple of times before on other sites but unfortunately I can not seem to follow the answers given.
How do you store and retrieve a graphic image in the database.?? I have been given hints on the use of blobs but I still can not get my head around the concept or use of same. Would somebody please, with a short example of code, inform me how to accomplish the storage and retrieval of images from a postgres database.
Thank you

I pulled this directly from Bruce Momjian's "PostgreSQL: Introduction and Concepts"...an excellent resource.

> CREATE TABLE fruit(name char(30), image OID);
> CREATE

> INSERT INTO fruit
> VALUES('peach', lo_import('/path/to/images/peach.jpg'));
> INSERT 27111 1

> SELECT lo_export(fruit.image, '/tmp/outimage.jpg')
> FROM fruit
> WHERE name = 'peach';
lo_export
-------------
1
(1 row)

> SELECT lo_unlink(fruit.image) FROM fruit;
lo_unlink
-------------
1

The lo_import() function stores the .jpg file in the database. The function call returns an OID that is used to refer to the imported large object and is stored in fruit.image. The lo_export() function use the OID value to find the large object and places it in the new file /tmp/outimage.jpg. 1 indicates successful export. lo_unlink() removes large objects. Be sure to use full paths on the server. Also, files are exported by the postgres user, so check file and dir permissions to see if postgres has the necessary access...

Hope this helps.

Kyuzo

I have ask this question a couple of times before on other sites but unfortunately I can not seem to follow the answers given.
How do you store and retrieve a graphic image in the database.?? I have been given hints on the use of blobs but I still can not get my head around the concept or use of same. Would somebody please, with a short example of code, inform me how to accomplish the storage and retrieval of images from a postgres database.
Thank you

Here is what I did.

I ended up creating libraries out of these functions for my own use. Very reusable

I create and INIT.PHP File that defines the width I want the picture for the project. The apect ratio and height are calculated.



Image upload library


<?php
/*
*****************************************************************************
* Image Upload Library *
* Author Rob Underwood, AUGUST 2002 *
* Require PHP 4.2 or greater, Postgresql 7.2 or greater, GD Library 2.2+ *
* to be useds for uploading images and tbumbnails to a postgres database *
* Expected Tables structure is: *
* imageID serial *
* refID int4 *
* image bytea *
* thumbnail bytea *
* type int2 *
*****************************************************************************
*/

function addpic($table, $refname, $refid)
{
//Table Name, Reference ID column Name, ReferenceID, $PHOTO
global $conn;
global $HTTP_POST_VARS;
global $HTTP_POST_FILES;
global $default_width;
if(!$HTTP_POST_FILES)
{
//Print File Upload Form
$HTML .= "
<form enctype=\"multipart/form-data\" method=\"post\">
<BR>Image file: <input name=\"photo\" type=\"file\">
<BR>Caption: <input type=\"text\" name=\"caption\" size=\"20\">
<input type=\"submit\" value=\"Send File\">
</form>
";
}
else
{
//Get variables and file and insert into databse
$image_info = getimagesize($HTTP_POST_FILES['photo']['tmp_name']);

//figure out what type of image was uploaded
$type = $image_info[2];
switch ($type) {
case 2:
$img_in = imagecreatefromjpeg($HTTP_POST_FILES['photo']['tmp_name']);
$validtype=TRUE;
break;
case 3:
$img_in = imagecreatefrompng($HTTP_POST_FILES['photo']['tmp_name']);

$validtype = TRUE;
break;
default:
$validtype = FALSE;
break;
}
if($validtype)
{
//Resize to maintain consistency
$new_width = $default_width;
//calculate aspect multliplier ( new width / old width
$aspectmult = ( $image_info[1] / $image_info[0] );
//calculate new size
$new_height = ( $new_width * $aspectmult );
//create 'new image'
$img_out = imagecreatetruecolor($new_width,$new_height);
//copy image to resized image
imagecopyresampled( $img_out, $img_in, 0, 0, 0, 0, imagesx($img_out), imagesy($img_out), imagesx($img_in), imagesy($img_in));
//create temp file for later use
$tmp_img = tempnam("/tmp","IMG");
if ( $type == 2 )
{
imagejpeg ( $img_out, $tmp_img);
}
if ( $type == 3 )
{
imagepng( $img_out, $tmp_img);
}
//generate thumbnail -- fixed size
$thumbwidth = 100;
$thumbheight = ( $thumbwidth * $aspectmult);
$thumb_out = imagecreatetruecolor($thumbwidth,$thumbheight);
imagecopyresized( $thumb_out, $img_in, 0, 0, 0, 0, imagesx($thumb_out), imagesy($thumb_out), imagesx($img_in), imagesy($img_in));
$tmp_thumb = tempnam("/tmp","THUMB");
if ( $type == 2 ) { imagejpeg ( $thumb_out, $tmp_thumb); }
if ( $type == 3 ) { imagepng( $thumb_out, $tmp_thumb); }

//read files for insert into database
$fd = fopen ("$tmp_img", "r");
while(!feof($fd))
{
$img_string .= fread($fd, 4096);
}
fclose($fd);
$img_esc = pg_escape_bytea($img_string);
//now thumbnail
$fd = fopen ("$tmp_thumb", "r");
while(!feof($fd))
{
$thumb_string .= fread($fd, 4096);
}
fclose($fd);
$thumb_esc = pg_escape_bytea($thumb_string);

pg_exec ($conn, "begin");
$sql = "insert into \"$table\" (\"$refname\",\"image\",\"thumbnail\", \"caption\",\"type\" ) VALUES ( '$refid' , '$img_esc' , '$thumb_esc', '$caption', $
type)";
pg_query($conn,$sql);
pg_query($conn, "commit");
unlink($tmp_img);
unlink($tmp_thumb);
}
else
{
$HTML .= "invalid upload";
}

}
RETURN $HTML;

}

?>


view side

function display_pics($refname, $refID, $align )
{
$sql = "SELECT \"imageID\", \"caption\" FROM \"tbl_images\" WHERE
\"$refname\" = $refID";
$results = pg_exec($sql);
if (!$results)
{
$HTML .= "SORRY NO IMAGES";
}
else
{
$num = pg_numrows($results);
if($num)
{
for ($i = 0; $i < $num; $i++)
{
$r = pg_fetch_row($results, $i);
$HTML .= "
<img src=\"dbimage.php?imageID=$r[0]\" alt=\"$r[1]\" align=\"$align\">\n ";

}
}
}
RETURN $HTML;
}

function display_thumbs($table, $refname, $refID)
{
similar to above
}
?>

Thumbnail viewer
<?php
/********************************************************
* *
* Upload/Download Display images from postgres database *
* Created July 05, 2002 - Rob Underwood *
/* PHP version 4.01 - Postgres vs 7.0 on Redhat 7.0 *
* *
********************************************************/
require('INIT.PHP');
$conn = pg_connect("dbname=$dbname user=$user password=$pwd");
$imageID = $_GET['imageID'];
//$imageID = '1';
//Get variables and file and insert into databse
//GO GET Proper Image
$sql = "SELECT \"thumbnail\", \"type\" from \"tbl_images\" where \"imageID\" = $imageID ";
$results = pg_query ($conn, $sql );
//remove slashes
//$thumbnail = stripcslashes(pg_fetch_result($results, 0,0));
$thumbnail = stripcslashes(pg_fetch_result($results,0,0));
$type = pg_fetch_result($results,0,1);
//only supporting 2 types -- jpg and png.
switch ($type) {
case 2:
header ("Content-type: image/jpeg");
break;
case 3:
header ("Content-type: image/png");
break;
default:
//put default image here.
break;
}
print "$thumbnail";
?>

view image file

\/********************************************************
* *
* Upload/Download Display images from postgres database *
* Created July 05, 2002 - Rob Underwood *
/* PHP version 4.01 - Postgres vs 7.0 on Redhat 7.0 *
* *
********************************************************/
require('INIT.PHP');

$conn = pg_connect("dbname=$dbname user=$user password=$pwd");
$ID = $HTTP_GET_VARS['imageID'];
//GO GET Proper Image
$sql = "SELECT \"image\", \"type\", \"caption\" from \"tbl_images\" where \"imageID\" = $ID ";
$results = pg_query ($conn, $sql );
//remove slashes
$image = stripcslashes(pg_fetch_result($results,0,0));
$type = pg_fetch_result($results,0,1);

$im = imagecreatefromstring($image);

//only supporting 2 types -- jpg and png.
switch ($type) {
case 2:
header ("Content-type: image/jpeg");
imagejpeg($im);
break;
case 3:
header ("Content-type: image/png");
imagepng($im);
break;
default:
//put default image here.
break;
}

?>










privacy (GDPR)