使用php从postgresql中显示图像（bytea）

Question

I have a postgres database with stored images 'bytea' type and I try to display them into a browser with PHP. I found the way to display one of them but I can't make it for more than one. The code I use is the following:

File Name - display_image.php

$conn = pg_connect("dbname=test user=postgres password=postgres");

$temp = '/home/postgres/tmp.jpg';

$query = "select lo_export(image, '$temp') from map ";
$result = pg_query($query);

if($result)
{
    while ($line = pg_fetch_array($result))
    {
        $ctobj = $line["image"];
        echo "<IMG SRC=show.php> </br>";

    }

}
else { echo "File does not exists."; }
pg_close($conn);

File Name - show.php

header("Content-type: image/jpeg");
$jpeg = fopen("/home/postgres/tmp.jpg","r");
$image = fread($jpeg,filesize("/home/postgres/tmp.jpg"));
echo $image;

The problem seems to be the "tmp.jpg" virtual file which displays only one image. If the result of the query is 7 images then it displays 7 times the same image within a while loop. How can I solve this? Thanks for the interest!

Answer 1

I did this some time ago for bytea.

You need to run your bytea data through pg_unescape_bytea. See http://php.net/manual/en/function.pg-unescape-bytea.php

Basically your SQL query returns the bytea field in an escaped format.

However this is not what you are doing. And so the above is just for the next poor sap who comes here looking for bytea help. Please amend to note you are using LOB's not BYTEA's.

Also note that your code there is not concurrency safe. If two users request different images, my guess is that you will get both users getting different images. For this reason you should add the oid to the retrieval url, and name your file /tmp/$oid.jpg where $oid is the oid of the large object. You will need to retrieve that info (I believe it looks like it is stored in the image field of map?). On the other hand that assumes that all files are essentially public. if that's not the case, you want to move everything into the show_image.php and clean up when you are done.