How Do I Display Image From PostgreSQL?

[gwinn7]

Does anyone know how to display an image from a field in a PostgreSQL database table?

Here is what I am working with so far...

<?
$connect = pg_connect(<myconnect string>);
$Query = &quot;SELECT * FROM MyTable WHERE id = 1&quot;;
$Result = pg_exec($connect, $Query);
$Row = pg_fetch_row($Result, $Row);
$Object = pg_locreate($connect);
$File = pg_loopen($connect, $Object, &quot;r&quot;
pg_loreadall($File); // should dump pic to browser.
?>

Look at the above code, I can obviously see some potential problems with it, but I am still researching this. If you already have sample code that could demonstrate what I am trying to accomplish, that would be most helpful.

The data-type I am using for this is &quot;bytea&quot;, which ironically, is not listed when I create the field.

The image type is a JPG image.

Gary
gwinn7

 

[gerrygerry]

I'm no expert on PostgreSQL, but could you explain / post you output?

I have done this many times in MySQL. The way I do it is to pull the binary data from a longblob field and put it into

$data

, then I send a

header("Content-type: image/jpg");

and then just

echo "$data";

. This works fine.

Maybe this helps? Otherwise, post your output! -gerrygerry
Go To

http://quickfreewebsite.com!

 

[gwinn7]

Unfortunately, at the moment, I don't have the exact output. Essentially, it said that the argument type is not correct and does not match the type of what should be passed. My largest issue is the conversion of accessing the field with the JPG data and output it to the browser.

I know that the postgres function &quot;pg_loreadall(integer file)&quot; does exactly this, but what confuses me is the parameter. I want to simply pass the data in that field and render it in the browser, but I don't know what does this.

Can you suggest anything further?

I am currently researching PHP- data-types and functions to see what I can do, but if you have any sample scripts or input, I would appreciate it.

Still learning this stuff!!!

Gary
gwinn7
A+, Network+

 

[gerrygerry]

Well, just a quick note - I noticed you are using an older version of some functions, but that shouldn't matter: the arguments are the same.

From my reaading it looks like you'll want to send your header right before you call pg_loreadall($File);...

Also, are you sure that the row with id=1 exists accuratly? This will screw it up if you dont - i put in a test for that.

try this (untested):

<?
 $connect = pg_connect(<yourconnect string>);
 $Query = &quot;SELECT * FROM MyTable WHERE id =

'

1

'

";
 $Result = pg_exec($connect, $Query);

 if($Result)
 {

  $Row = pg_fetch_row($Result, $Row);
  $Object = pg_locreate($connect);

  header("Content-Type: image/jpg");

  $File = pg_loopen($connect, $Object, "r");
  pg_loreadall($File); // should dump pic to browser.

 }
 else
 {
  echo "The query failed!?! There is no row with id=1!";
 }

?>

Again, I'm no expert on PostgreSQL! Sorry I can't help beter than I am! -gerrygerry
Go To

http://quickfreewebsite.com!

 

[gerrygerry]

Also, what's pg_exec()?? I didn't find anything on that... change it to pg_query(), with the same arguments.

I think this outta do it! -gerrygerry
Go To

http://quickfreewebsite.com!

 

[gwinn7]

I tried pg_query and php would not recognize it. I have version 4.1.2. pg_exec worked fine.

I am about to try your code. I will debug, if necessary, and provide the results in a few min.

Gary
gwinn7

 

[gwinn7]

When I run the code you sent, the output is the following...

&quot;Warning: Cannot add header information - headers already sent by (output started at /home/httpd/htdocs/pictest2.php:6) in /home/httpd/htdocs/pictest2.php on line 14

Warning: Unable to open PostgreSQL large object in /home/httpd/htdocs/pictest2.php on line 15

Warning: Supplied argument is not a valid PostgreSQL large object resource in /home/httpd/htdocs/pictest2.php on line 16&quot;

I populated the field &quot;pic1&quot; with the data-type of &quot;bytea&quot;. It seems to my that the data-type &quot;bytea&quot; is not what I should be using, but I am not sure. I had to specifically type this data-type in, not from the drop down list. When I view the data from pgaccess (the postgres GUI), I was able to see that the JPG did get stored in that field when I appended the table from MS Access.

Gary
gwinn7

 

[gerrygerry]

I think the problem has got to be in the table... I dont know for sure though.

Try this:

<?
 $connect = pg_connect(<yourconnect string>);
 $Query = &quot;SELECT * FROM MyTable WHERE id = '1'&quot;;
 $Result = pg_exec($connect, $Query);
if($Result)
 {
  $Row = pg_fetch_row($Result,

'0'

);

/////////////////////////////////////
// or try
//$Row = pg_fetch_row($Result,

'1'

);
/////////////////////////////////////

  $Object = pg_locreate($connect);
  header("Content-Type: image/jpg");
  $File = pg_loopen($connect, $Object, "r");
  pg_loreadall($File); // should dump pic to browser.
}
 else
 {
  echo "The query failed!?! There is no row with id=1!";
 }

?>

-gerrygerry
Go To

http://quickfreewebsite.com!