PHP, JPG and PostgreSQL

Anon

Anyone have any sample scripts that involve importing a picture (ie. JPEG) and storing and retrieving with a PostgreSQL database?

Thanks in advance.

Gary
gwinn7

cheald

Well...I don't know Postgre that well, but I'm sure it supports blobs...

Just read in the file with $pic =join("",file($yourfile));, then store $pic in a blob field in the database.

To retrieve, you'll need a script that outputs a header like "Content-Type: image/jpeg", then calls the DB, gets the pic field, and writes it to stdout. To display the picture, just call an image with the script as the source. For example:
&ltIMG SRC="retrieve_pic.php?pic_id=15">

where retrieve_pic is the script that handles retrieving the picture.

bastien

What is it with everyone wanting to store a pic in the DB. Stop it! You impair the DB performance and cause the size to ballon.

The recommeded way to handle pics and databsed is to store all the pics in a folder (/images/some.jpg) and store the path to the pic and the pic name in the DB as text.

Why cause then all that is needed is to write the path/imagename to the html tag <img src = "resultfromDB.jpg"> and that is it.

Way faster to do, easier to get text from a DB, no performance penalities and you don't kill the DB with lots of big items.

hth

Bastien

edwardsbc

Please see <http://www.yapf.net/faq.php?cmd=viewitem&itemid=22>. Then reconsider putting pictures in your database.

Anon

Hi Craig,

Thanks for the heads up on this. I will forward the concerns to my boss. He is pretty well set from serving the pictures from the database. We will see.

Thanks again for your feedback,

Gary
gwinn7

Anon

Thank you for your response.

I need to speak with my boss about this and decide how to proceed based on the feeback I have been getting.

Thank you,
Gary
gwinn7

Anon

Craig and Bastien are right on - don't do it! I've never recommended this approach to handling images...store the path instead.

Anon

Bastien,

FYI: This is only true if you have several conditions:

You are only every doing a single picture retrieval. Array fetching from a database for multiple records is faster than array fetching in a file system.
The number of picture files that you are storing needs to be less than 64K per directory. Above this limit, native tools such as find, awk etc suffer from address space problems in holding and analysing inode details.

Cheers
Justin

Anon

Well, I have managed to convince my boss to accept the possibility that we should not store pictures in the database.

The primary concern is that we may not be able to reconcile the correct picture with the correct property due to an unforseen circumstance.

We literally have thousands of pictures and this is growing by the hundreds every month. With that in mind, the only way to reconcile the pictures properly is to give the picture a file name containing our property identifier, county code, jurisdiction, drive date, driver code, and more. The file name would be potentially enormous, for a file name. It is unacceptable to have a filename that cannot be traced back to the specific property. So its either place the pictures in the database where they are locked in the property association, or give them a looong filename.

Gary
gwinn7

edwardsbc

Why not generate a unique file name and store that file name in the DB. Much like a primary/foreign key relationship within a database? Perhaps you could explain the "unforseen circumatances" or "reconciliation" problem you think you are going to have.

Additionally, if you are going to have THOUSANDS of pictures, you may want to study the limits of your file system. Eventually, you may need to segment the storage even on a normal file system. I seem to remember DOS choking when a directory (especially the root) had more than 300 files. Other file systems have limits as well (usually much higher than DOS), but exact performace points vary. The file system, afterall, is a database of a sorts which is optimized for general usage in it's target operating system.

Anon

That's what I wanted to do originally. However, my boss and I believe it very important to be able to trace the individual picture to the exact property, not a unique identifier.

Yes, we have examined the limitations of the file system and we will need to run a test of this. We are going to be using ext2 or ext3 filesystems, not DOS (yikes!) even though it can support thousands of files. If the file system is not going to do the job, then the database will have to do it.

By placing the proper indexes and operating with in the 24TB limit of a table size in Postgres, this should not be a problem, if it comes to that.

Your feedback is appreciated. Thank you.

Gary
gwinn7