Displaying Image Stored as Binary in Postgre bytea Column

Question

I'm stuck for hours working on this. Few solutions found over the Net but no one seems to help me. I am having problem to display an image on the browser using PHP that takes the image from Postgres DB with column type bytea. I'm sure I'm missing something here. So some guidance are really appreciated. So I have this code below:

$prod = new Product();
$prod->display_latest_product();
if( $prod->exists() ){
    $products = $prod->data();
    foreach($products as $product){
        echo $product->id;
        echo $product->binarydata;

        /* Solution below: I only get one image with broken link */
        header('Content-type: image/png');
        echo pg_unescape_bytea($product->binarydata);

        /* Solution below: I only get one image with broken link */
        header('Content-Type: image/png'); 
        $data=fgets($product->binarydata); 
        print(pack('H*',$data));

        /* bin2hex() expects parameter to be string but resource given */
        echo bin2hex($product->binarydata);

         /* Solution below: I only get one image with broken link */
        $image = stripcslashes($product->binarydata);
        header("Content-Type: image/png");
        print($image);
    }
}

I appreciate some explanation on this because I am still confuse after research and reading.

You can only output one image at a time, and only the binary image data — Musa, Oct 22 '15 at 12:40
@Musa OK understood, that means I can put the URL calling the page to display image. But before that I need to output the image successfully first. — UserProg, Oct 23 '15 at 01:24

score 1 · Accepted Answer · edited May 23 '17 at 12:07

Finally I found the way to do it. Based on what I found in Handling Binary Data with PDO as I am using PDO to read the column in DB.

So I put these two lines in my foreach loop:

header("Content-Type: ".$product->mimetype);
fpassthru($product->binarydata);

And my file is displayed nicely. As @Musa pointed out that I can only print one image at a time, so I am going to use img with src="thepage.php?img=xx" just to display the image. I am developing e-Commerce app so not sure if this can affect performance because lots of images will be loaded. Any comments or suggestions are welcomed. Anyway I hope this could help people out there who is having same problem.

Documentation on fpassthru is here.

EDIT::SOLUTION FOUND

So I have finally got the real solution that tackles my problem defined in the comment below. And this time it does not involve PHP header at all.

foreach($products as $product){
    ob_start(); // Let's start output buffering.
    fpassthru($binarydata); //This will normally output the image, but because of ob_start(), it won't.
    $contents = ob_get_contents(); //Instead, output above is saved to $contents
    ob_end_clean(); //End the output buffer.

    $dataUri = "data:image/png;base64," . base64_encode($contents);
    echo "<img src='$dataUri' />";
}

I have got the solution here. And based on the comment in that thread, the trick is actually at the buffering. ob_start has to be used in pair with ob_end_clean(). Hope this helps people out there. Thanks.

If performances matter, I would suggest not using PDO but native PHP’s pgsql library instead. — greg, Oct 23 '15 at 18:48
@greg I have PDO in the DB class so that is the reason PDO is used. Having problem now as the `header` is there, i can only display one image. As I pointed out above, I can get from the external page but I will need to call DB every time the page is called. This is not practical especially for site that has lots of images in one page. I cannot pass `binarydata` which is PHP `Resource Id`. How do I go with this, any idea? — UserProg, Oct 29 '15 at 06:31

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

Assuming that $product->binarydata has contents fetched from the BYTEA column, this method does work:

    header('Content-type: image/png');
    echo pg_unescape_bytea($product->binarydata);

except when having the client/server version mismatch mentioned in this answer, in which case the escape format for bytea should be used.

The code must not output anything else than the two lines above.

edited May 23 '17 at 11:45

Community

1
1

answered Oct 22 '15 at 13:45

Daniel Vérité

58,074
15
129
156

I have commented the `echo` parts and put `header('Content-type: image/png'); echo pg_unescape_bytea($product->binarydata);` . Only image broken displayed. – UserProg Oct 23 '15 at 01:38

Displaying Image Stored as Binary in Postgre bytea Column

2 Answers2