Storing images in postgreSQL (with PHP)

[bissatch]

Hi,

I am wanting to learn how to store images in a postgreSQL database. I
have got as far as uploading the file using a file/browse field on an
html form and have been able to catch the file using $_FILES. What I
dont know is how to to them store the image in a database. Can someone
please supply the code required to do this? Also, what is the SQL code
needed to CREATE the database?

I have in the past done this by storing the images in a file structure
but would prefer to do it this way. I have tried looking on Google for
some good tutorials but none of them appeared to explain exactly what
was happening and how to do it. If anyone can recommend any good
tutorials that would be great.

Burnsy

[Kenneth Downs]

bi******@yahoo.co.uk wrote:

Hi,

I am wanting to learn how to store images in a postgreSQL database. I
have got as far as uploading the file using a file/browse field on an
html form and have been able to catch the file using $_FILES. What I
dont know is how to to them store the image in a database. Can someone
please supply the code required to do this? Also, what is the SQL code
needed to CREATE the database?

I have in the past done this by storing the images in a file structure
but would prefer to do it this way. I have tried looking on Google for
some good tutorials but none of them appeared to explain exactly what
was happening and how to do it. If anyone can recommend any good
tutorials that would be great.

Burnsy

There are two ways.

The first method is to use column type "bytea" and store the picture as
binary data.

The second method is to use column type "text", convert the file to base64,
and load it this way. This gives you a 33% hit on storage space, but IMHO
is far easier to deal with, and avoids messy binary stuff.

Some code to load a picture might be:

$binary = file_get_contents("somefile.jpg");
$base64 = base64_encode($binary);

pg_query("create tables pictures (picture text)");
pg_query("insert into pictures (picture) values ('$base64')");

There is of course a lot more too it, but hopefully this will get you
started.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

[bissatch]

> $base64 = base64_encode($binary);

Is this a similar idea to using pg_escape_bytea? Is the above better in
any way?

[Kenneth Downs]

bi******@yahoo.co.uk wrote:

$base64 = base64_encode($binary);

Is this a similar idea to using pg_escape_bytea? Is the above better in
any way?

Yes, it is similar.

The advantage that the bytea type has, and the corresponding function
pg_escape_bytea, is that the storage requirements are only 75% of what is
required for the base64-encoded type.

However, the bytea-encoded data must be decoded when you get it back from
the server, and if you are going to attach it to an email you have to
base64 encode it, so it gets handled twice going out to a browser or email
program. So I pay the price in storage to save the base64 to disk so I can
read it off the disk and send it over the wire without processing it at
all.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)