How to store images in Postgres using Php.
Published - Sep 16/2006
Last Updated - Sep 20/2008
Author - Sumedh
Introduction
There are many "How to store an image in Mysql" tutorials but hardly any good tutorials for postgres. So i decided to write one. Storing images in postgres using php is easy and has several advantages but there some disadvantages also. We wont go into that debate here.I've created a zip which contains all the files you need to understand this tutorial. Download it here.
PostgreSQL provides two distinct ways to store binary data.
- bytea
- binary large object ( BLOB) - Object is a large collection of uninterpreted binary data.
There are pros and cons for each of them. We are going to use the binary Large Object Method. Blob method stores the binary data in a seperate table and refers to that table by storing a value of type OID in your table.
Assumptions
Im assumming that you have postgres, php and apache server up and running, if not then check out this tutorial to setup apache, php and postgres.My database name is test with the username in linux as 'postgres' and password as 'postgres'. The user in Apache is also named 'postgres'. You will have to modify the php code accordingly if you have a different database name, username and password. If all of this sounds alien to you then please read my
How to install, configure Apache, Php, Postgresql on Linux tutorial.
What are we going to do
In this tutorial we are going to do the following things.- Browse and select the image file.
- Upload the selected file.
- Store the image file in the database.
- View the file in a browser.
Creating an image table
We need to create a table in postgres to store our images in. Here is a simple table.
create table image (
name varchar(20) not null,
image OID not null,
day date
);
name varchar(20) not null,
image OID not null,
day date
);
First we have a name field where you can store the name of the image.
Next we have the image field whose datatype is OID which contains the actual image data.
Finally we have the day field which displays the date on which the image was uploaded.
The last field is not necessary but i think you will find it useful. You can add your own fields like category, description etc but for this tutorial we will keep it simple.
The Upload form
File Name - upload.php
<html>
<head><title>File Upload To Database</title></head>
<body>
<h3>Please Choose a File and click Submit</h3>
<form enctype="multipart/form-data" action="image.php" method="POST">
<input type="hidden" name="MAX_FILE_SIZE" value="300000" />
Name : <input type="text" name="name" size="25" length="25" value="">
<input type="hidden" name="MAX_FILE_SIZE" value="300000" />
File: <input name="userfile" type="file" size="25"/>
<input type="submit" value="Upload" />
</form>
</body>
</html>
<html>
<head><title>File Upload To Database</title></head>
<body>
<h3>Please Choose a File and click Submit</h3>
<form enctype="multipart/form-data" action="image.php" method="POST">
<input type="hidden" name="MAX_FILE_SIZE" value="300000" />
Name : <input type="text" name="name" size="25" length="25" value="">
<input type="hidden" name="MAX_FILE_SIZE" value="300000" />
File: <input name="userfile" type="file" size="25"/>
<input type="submit" value="Upload" />
</form>
</body>
</html>
enctype="multipart/form-data" ensures that the browser uploads the binary data of the an image file correctly.
The hidden field MAX_FILE_SIZE does not allow images that are too large to be uploaded. The value 300000 says that any image file whose size is greater than 300kb will not be uploaded. You can play around with that value as per your requirements.
The above html form will take a name and the filepath as an input from the user and give it to the php script in the image.php file. Note - Name and filename can be different.
Page 1 2
Copyright 2008 Sumedh K