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=20=
=20
occasionaly.

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: php, postgresql and graphical images

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:


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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: php, postgresql and graphical images

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 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:
>
> >
> require "db.php";
>
> $sql =3D "SELECT image_data, image_mimetype FROM images where id =3D " .
> intval($_GET['id']);
>
> $res =3D pg_query($GLOBALS['db'], $sql);
>
> if (pg_numrows($res) !=3D 1)
> {
> header("HTTP/1.0 404 Not Found");
> exit();
> }
>
> $image =3D pg_unescape_bytea(pg_result($res, 0, 0));
> $mimetype =3D 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
>


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: php, postgresql and graphical images

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 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=85 495. No HTML
or text was served at al. That URL (or variations thereof) can be used
directly in an tag as many times as you want in a single HTML
document.

Regards, Dave.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: php, postgresql and graphical images

------=_Part_39658_2064345.1181768302067
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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




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]



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 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 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=85 495. No HTML
> or text was served at al. That URL (or variations thereof) can be used
> directly in an tag as many times as you want in a single HTML
> document.
>
> Regards, Dave.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

------=_Part_39658_2064345.1181768302067
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

Re: php, postgresql and graphical images

Dave and Colin!

Thanks a lot. Now it works fine!

Mike

Quoting Colin Ross :

> Using that, you could just point the src atribute of an img tag to that u=
rl,
>
>
> ?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]
>
>
>
> 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 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 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=85 495. No HTML
>> or text was served at al. That URL (or variations thereof) can be used
>> directly in an tag as many times as you want in a single HTML
>> document.
>>
>> Regards, Dave.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>



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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate