18:59 Sunday, September 09 2008

Inserting files into PostgreSQL via PHP

I spent way more time than neccesary over the weekend trying to figure out how to get [PHP ]{.caps}to insert a file (from a form [POST]{.caps}) into a PostgreSQL database. It should have been simple, but Google failed me for the most part. So, for anyone else out there who isn't more clueful than I, here's how its done.

First, create yourself a database table that has one column with a datatype of oid:

test0=> create table data0 (id serial primary key, name text,

test0-> file oid, filesize bigint) ;

test0=> \\d data0

Table "public.data0"

Column  |  Type   |                     Modifiers

----------+---------+----------------------------------------------------

id       | integer | not null default nextval('data0_id_seq'::regclass)

name     | text    |

file     | oid     |

filesize | bigint  |

Now for the PHP bits. First, create a file with the form for submitting the file. I'm naming the input type "file0". 

$target="uploads/" ;

$target=$target . basename( $_FILES['file0']['name']);

if(move_uploaded_file($_FILES['file0']['tmp_name'], $target))

{

echo "File ". basename( $_FILES['file0']['name']). " uploaded" ;

} else {

echo "Upload FAILED.
target = $target

" ;

}

//  END OF FILE UPLOAD  -  BEGIN DB INSERT

$db = pg_connect('host=hal dbname=test0 user=tester');

if (!$db) {

echo ("Cannot connect to DB");

echo pg_last_error();

exit();

}

$filename=basename( $_FILES['file0']['name']) ;

$filesize0=$_FILES['file0']['size'] ;

echo "
filename = $filename 
filesize = $filesize0
" ;

$fp = fopen("/home/tester/public_html/files/fileupload/" . $target,"r") ;

if ($fp==false) {

echo "Error opening file" ;

}

$buffer=fread($fp,$filesize0) ;

fclose($fp) ;

pg_exec($db,"BEGIN");

$oid=pg_locreate($db);

$rs=pg_exec($db,"INSERT INTO data0(name,filesize) VALUES

('$filename','$filesize0') ;") ;

$lofp=pg_loopen($db,$oid,"w");

pg_lowrite ($lofp,$buffer);

pg_loclose ($lofp) ;

pg_exec ($db, "COMMIT") ;

pg_close () ;

And that should do it.

</p>