php, postgresql and graphical images

Hi all!

Does anybody have any experience in displaying images stored in
postgresql database?
I'm trying to export image into file using pg_lo_export function and
then view it on html page. However, the pg_lo_export seem to work only
occasionaly.

The code is like this:
(php5, postgresql 8.2)
*****************
$no++; //Incerementing number of the image
pg_query("Begin");
$query = "select pic_data, accurateness_level, mime_type from image2 where
*****//condition clause";
$row = pg_fetch_row(pg_query($query));
// Get the image OID
$image = $row[0];
// Get the type of the picture
$type = $row[2];
// Export the picture into pic.type
pg_lo_export($image, "pic$no.$type");
//get image file dimensions
$dimensions = getimagesize("pic$no.$type");
$width = $dimensions[0];
$height = $dimensions[1];
// Show the picture
echo "<img src=\"pic$no.$type\" width=" . $width . " height=" .
$height . "/>";
pg_query("Commit");
*************

I understand that the script doesn't like the idea of saving files on
the server. However, why does it work at all?
Are there any other ways to export images from the database?

Any comments are welcome.

- Mike

Mihail Mihailov wrote:

> Are there any other ways to export images from the database?

Hi Mike,

I store them in bytea columns, along with their mimetype, then use a
script like:

<?php

require "db.php";

$sql = "SELECT image_data, image_mimetype FROM images where id = " .
intval($_GET['id']);

$res = pg_query($GLOBALS['db'], $sql);

if (pg_numrows($res) != 1)
{
header("HTTP/1.0 404 Not Found");
exit();
}

$image = pg_unescape_bytea(pg_result($res, 0, 0));
$mimetype = pg_result($res, 0, 1);

header("Expires: " . date("D, j M Y H:i:s", time() + (86400)) . " UTC");
header("Cache-Control: Public");
header("Pragma: Public");

header("Content-Type: " . $mimetype);
echo $image;

?>

I should really rewrite that to use a parameterized query, but you get
the idea :-)

Regards, Dave

Hi Dave and thanks for advice.

You show in your example how to create an html file containing only
one picture.
How do you insert multiple graphical images into a "mixed" document
containing text and graphical images?
Do I still have to save bytea data into a temporary file (how?) and
than get in with <img> tag?

Sorry for stupid questions :-)

Mike

>
>> Are there any other ways to export images from the database?
>
> Hi Mike,
>
> I store them in bytea columns, along with their mimetype, then use a
> script like:
>
> <?php
>
> require "db.php";
>
> $sql = "SELECT image_data, image_mimetype FROM images where id = " .
> intval($_GET['id']);
>
> $res = pg_query($GLOBALS['db'], $sql);
>
> if (pg_numrows($res) != 1)
> {
> header("HTTP/1.0 404 Not Found");
> exit();
> }
>
> $image = pg_unescape_bytea(pg_result($res, 0, 0));
> $mimetype = pg_result($res, 0, 1);
>
> header("Expires: " . date("D, j M Y H:i:s", time() + (86400)) . " UTC");
> header("Cache-Control: Public");
> header("Pragma: Public");
>
> header("Content-Type: " . $mimetype);
> echo $image;
>
> ?>
>
> I should really rewrite that to use a parameterized query, but you get
> the idea :-)
>
> Regards, Dave
>

Mihail Mihailov wrote:
> Hi Dave and thanks for advice.
>
> You show in your example how to create an html file containing only one
> picture.
> How do you insert multiple graphical images into a "mixed" document
> containing text and graphical images?
> Do I still have to save bytea data into a temporary file (how?) and than
> get in with <img> tag?

No, I showed how to return an image in response to a http request using
php, e.g:
http://www.jo-jos-jewellery.co.uk/system/showimage.php?id=85495. No HTML
or text was served at al. That URL (or variations thereof) can be used
directly in an <img> tag as many times as you want in a single HTML
document.

Regards, Dave.

Using that, you could just point the src atribute of an img tag to that url,

<img src="http://www.jo-jos-jewellery.co.uk/system/showimage.php?id=<?php
echo $imgId ?>" />

OR present the data inline using the object tag (but that gets tricky fyi
and cane be kinda slow for all but the most trivial of images [hence why its
highly discouraged by the w3c]

<OBJECT id="necklace"
...
data="data:image/jpeg;base64, *...base64 data...*">
A Necklace
</OBJECT>

Again, I **highly** recommend the first method

Colin

http://www.w3.org/TR/html401/struct/objects.html#h-13.2

On 6/13/07, Dave Page <dpage(at)postgresql(dot)org> wrote:
>
> Mihail Mihailov wrote:
> > Hi Dave and thanks for advice.
> >
> > You show in your example how to create an html file containing only one
> > picture.
> > How do you insert multiple graphical images into a "mixed" document
> > containing text and graphical images?
> > Do I still have to save bytea data into a temporary file (how?) and than
> > get in with <img> tag?
>
> No, I showed how to return an image in response to a http request using
> php, e.g:
> http://www.jo-jos-jewellery.co.uk/system/showimage.php?id=85495. No HTML
> or text was served at al. That URL (or variations thereof) can be used
> directly in an <img> tag as many times as you want in a single HTML
> document.
>
> Regards, Dave.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Dave and Colin!

Thanks a lot. Now it works fine!

Mike

Quoting Colin Ross <colinross(at)gmail(dot)com>:

> Using that, you could just point the src atribute of an img tag to that url,
>
>
> <img src="http://www.jo-jos-jewellery.co.uk/system/showimage.php?id=<?php
> echo $imgId ?>" />
>
> OR present the data inline using the object tag (but that gets tricky fyi
> and cane be kinda slow for all but the most trivial of images [hence why its
> highly discouraged by the w3c]
>
> <OBJECT id="necklace"
> ...
> data="data:image/jpeg;base64, *...base64 data...*">
> A Necklace
> </OBJECT>
>
> Again, I **highly** recommend the first method
>
> Colin
>
>
>
>
>
> http://www.w3.org/TR/html401/struct/objects.html#h-13.2
>
>
> On 6/13/07, Dave Page <dpage(at)postgresql(dot)org> wrote:
>>
>> Mihail Mihailov wrote:
>>> Hi Dave and thanks for advice.
>>>
>>> You show in your example how to create an html file containing only one
>>> picture.
>>> How do you insert multiple graphical images into a "mixed" document
>>> containing text and graphical images?
>>> Do I still have to save bytea data into a temporary file (how?) and than
>>> get in with <img> tag?
>>
>> No, I showed how to return an image in response to a http request using
>> php, e.g:
>> http://www.jo-jos-jewellery.co.uk/system/showimage.php?id…495. No HTML
>> or text was served at al. That URL (or variations thereof) can be used
>> directly in an <img> tag as many times as you want in a single HTML
>> document.
>>
>> Regards, Dave.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>

--
Mihail Mihailov, lehtori
Käännöstiede (venäjä)
Kieli- ja käännöstieteiden laitos
33014 Tampereen yliopisto
puh. (03) 3551 6123