[adsense id=”0514458240″ width=”468″ height=”60″]
There are two ways in which binary data can be added using PHP into a postgreSQL database, using PDO or pg frameworks. To find out how this is done using PDO click here
We also have good examples demonstrating the same tests with MySQL databases. Again there are 2 options using mysqli or PDO.
This paper provides the code needed to both write binary content to a PostgreSQL table and to read the same data back. In our example, we will create two scripts, one that reads the contents of an image file and adds this into a table. The second script will read the contents of the same record in PostgreSQL and output it to a file. We will then show that the two files are identical.
The table that we created is as follows:
$ psql test psql (9.3.5) Type "help" for help. test=# CREATE TABLE testblob( test(# id INT, test(# data BYTEA); test=#
The first script reads the contents of a PDF file called testimage.jpg.
<?php
$dbh = pg_connect("dbname=test");
if(!$dbh) {
die(pg_last_error($dbh)) ;
}
$data = file_get_contents("testimage.jpg");
$escapeddata = pg_escape_bytea($dbh, $data);
$result = pg_query_params($dbh, "INSERT INTO testblob(data) VALUES ($1)", array($escapeddata));
?>
At this point the binary has been added into the database. We now need a second script which will read this content and write it back out to a separate file:
<?php
$dbh = pg_connect("dbname=test");
if(!$dbh) {
die(pg_last_error($dbh)) ;
}
$result = pg_query($dbh, "SELECT * FROM testblob");
if(!$result) {
die(pg_last_error($dbh)) ;
}
while($row = pg_fetch_assoc($result)) {
$data = pg_unescape_bytea($row['data']);
}
pg_free_result($result);
file_put_contents("testblobpgsql.jpg", $data);
?>
To see this in action:
$ psql test
psql (9.3.5)
Type "help" for help.
test=> truncate table testblob;
TRUNCATE TABLE
test=> select count(1) from testblob;
count
-------
0
(1 row)
test=> \q
$ php pgsql_ins.php
$ psql test
psql (9.3.5)
Type "help" for help.
test=> select count(1) from testblob;
count
-------
1
(1 row)
test=> \q
$ php pgsql_sel.php
$ ls -ltr *jpg
-rw------- 1 rcashell users 229943 Jan 19 12:57 testimage.jpg
-rw-r--r-- 1 rcashell users 229943 Jan 22 12:35 testblobpgsql.jpg
$ diff testimage.jpg testblobpgsql.jpg
$
[googleplus]