Обсуждение: 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]
http://www.w3.org/TR/html401/struct/objects.html#h-13.2
<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@postgresql.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@gmail.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@postgresql.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