Question

In the first chapter of the PostgreSQL PHP tutorial, we will provide necessary definitions. We will show, how to install PostgreSQL database and the required packages. The examples will be run on the command line using the PHP CLI.

In order to run the examples, we need to have PHP language (in the form of PHP CLI) and the PostgreSQL database installed. We also need the php5-psql package.

About PostgreSQL database

PostgreSQL is a powerful, open source object-relational database system. It is a multi-user database management system. It runs on multiple platforms including Linux, FreeBSD, Solaris, Microsoft Windows and Mac OS. PostgreSQL is developed by the PostgreSQL Global Development Group.

Setting up PostgreSQL

We must have PostgreSQL database installed.

$ sudo apt-get install postgresql

On an Debian based system we can install the PostgreSQL database using the above command.

$ sudo update-rc.d -f postgresql remove
 Removing any system startup links for /etc/init.d/postgresql ...
   /etc/rc0.d/K21postgresql
   /etc/rc1.d/K21postgresql
   /etc/rc2.d/S19postgresql
   /etc/rc3.d/S19postgresql
   /etc/rc4.d/S19postgresql
   /etc/rc5.d/S19postgresql
   /etc/rc6.d/K21postgresql

If we install the PostgreSQL database from packages, it is automatically added to the start up scripts of the operating system. If we are only learning to work with the database, it is unnecessary to start the database each time we boot the system. The above command removes any system startup links for the PostgreSQL database.

$ /etc/init.d/postgresql status
Running clusters: 9.1/main

$ service postgresql status
Running clusters: 9.1/main 

We check if the PostgreSQL server is running. If not, we need to start the server.

$ sudo service postgresql start
 * Starting PostgreSQL 9.1 database server    [ OK ]

On Ubuntu Linux we can start the server with the service postgresql start command.

$ sudo service postgresql stop
[sudo] password for janbodnar: 
 * Stopping PostgreSQL 9.1 database server    [ OK ] 

We use the service postgresql stop command to stop the PostgreSQL server.

$ sudo -u postgres createuser janbodnar
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

We create a new role in the PostgreSQL system. We allow it to have ability to create new databases. A role is a user in a database world. Roles are separate from operating system users. We have created a new user without the -W option, e.g. we have not specified a password. This enables us to connect to a database with this user without password authentication. Note that this works only on localhost.

$ sudo -u postgres createdb testdb -O janbodnar

The createdb command creates a new PostgreSQL database with the owner janbodnar.

PHP CLI

PHP language is known to run on the web server. But it can be used on the command line as well. PHP Command Line Interface (PHP CLI) is a library that enables programmers to use PHP on the command line. With PHP CLI we can build GUI applications with PHP-GTK or we can create simple test scripts. In this tutorial, we will connect to the PostgreSQL database using the command line PHP interpreter.

$ sudo apt-get install php5-cli

We install the PHP CLI module on our Linux system.

The php5-pgsql package

The php5-pgsql is a package to work with the PostgreSQL database from the PHP language. On other systems the package name might be different.

$ sudo apt-get install php5-pgsql

We launch the above command to install the package.

Handling errors

We have a quick remark on handling errors in PHP. PHP has a built-in support for error reporting. The specifics can be controlled in the php.ini file. Note that the PHP CLI version has a separate INI file. It is located in /etc/php5/cli/php.ini on our system.

The display_errors directive controls, whether the built-in error messages are shown or not. In development environments, these error messages are displayed. In production, they are suppressed. There is no reason to show these technical messages to the user. In addition, it is a potential security risk.

In general, we should log the more specific error messages to a log file. The log_errors directive controls if the errors are logged or not. The error_log specifies the name of the file where script errors should be logged. If it is not set, the default is the stderr for PHP CLI.

The pg_last_error() functions gets the last error message string of a connection. It is the same error message that is generated in the built-in error reporting.

In the examples of this tutorial, we do not use the pg_last_error() function, since it duplicates the built-in error messages. We have the following settings:

...
display_errors = On
...
log_errors = On

; Our own custom based log file
error_log = /home/janbodnar/.phpcli_log
...

We display the built-in errors; they are shown on the command line. The error messages are also logged to a specified log file. If we do not want the error messages on the console, we simply turn off the display_errors directive.

$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");

In the scripts, we use the die() function to display a simple error message, which can be easily understood. The more specific details are saved to the log file. The die() function also terminates the script.

Version

In the first code example, we will get the version of the PostgreSQL database.

<?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
  or die ("Could not connect to server\n"); 

$query = "SELECT VERSION()"; 
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n"); 
$row = pg_fetch_row($rs);

echo $row[0] . "\n";

pg_close($con); 

?>

In the above PHP script we connect to the previously created testdb database. We execute an SQL statement which returns the version of the PostgreSQL database.

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

These are connections string variables. To create a connection to a PostgreSQL database, we must provide the host name, user name and password and the database name.

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
  or die ("Could not connect to server\n"); 

We connect to the database server. If the connection could not be created the die() function terminates the script and prints an error message to the console. The pg_connect() function returns a connection resource, which is going to be used later with other module functions.

$query = "SELECT VERSION()"; 

This SQL statement selects the version of the PostgreSQL database.

$rs = pg_query($con, $query) or die("Cannot execute query: $query\n"); 

The query is executed with the pg_query() function.

$row = pg_fetch_row($rs);

We fetch the data from the returned result.

echo $row[0] . "\n";

We print the data that we have retrieved to the console. The data was returned in the form of a PHP array. The first element of the array is the string we are looking for.

pg_close($con); 

The connection to the database is closed using the pg_close() function.

$ php version.php
PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc-4.6.real 
  (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 32-bit

Running the version.php script.

Inserting data

We will create a cars table and insert several rows to it.

<?php 

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 


$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
  or die ("Could not connect to server\n"); 

$query = "DROP TABLE IF EXISTS cars"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "CREATE TABLE cars(id INTEGER PRIMARY KEY, mame VARCHAR(25), price INT)";  
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(1,'Audi',52642)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(2,'Mercedes',57127)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(3,'Skoda',9000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(4,'Volvo',29000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(5,'Bentley',350000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(6,'Citroen',21000)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(7,'Hummer',41400)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(8,'Volkswagen',21606)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

pg_close($con); 

?>

The above script creates a Cars table and inserts 8 rows into the table.

$query = "DROP TABLE IF EXISTS cars"; 
pg_query($con, $query) or die("Cannot execute query: $query\n");

We drop the cars table if it already exists. The pg_query() function executes the given query on the specified database connection.

$query = "CREATE TABLE cars(id INTEGER PRIMARY KEY, mame VARCHAR(25), price INT)"; 

This SQL statement creates a new cars table. The table has three columns.

$query = "INSERT INTO cars VALUES(1,'Audi',52642)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

$query = "INSERT INTO cars VALUES(2,'Mercedes',57127)"; 
pg_query($con, $query) or die("Cannot execute query: $query\n"); 

We are inserting two cars into the table.

pg_close($con);

The connection to the database is closed.

$ psql testdb
psql (9.1.3)
Type "help" for help.

testdb=# SELECT * FROM cars;
 id |  name  | price  
----+------------+--------
  1 | Audi     |  52642
  2 | Mercedes   |  57127
  3 | Skoda    |   9000
  4 | Volvo    |  29000
  5 | Bentley  | 350000
  6 | Citroen  |  21000
  7 | Hummer   |  41400
  8 | Volkswagen |  21606
  9 | BMW    |  36000
(9 rows)

We verify the written data with the psql tool.

Prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

A prepared statement is a server-side object that can be used to optimize performance. When a query is prepared, it is parsed, rewritten, and planned. Later the prepared statement need only be executed. Thus, the parsing, rewriting, and planning stages are only performed once, instead of every time the statement is executed. Prepared statements only last for the duration of the current database session. When the session ends, the prepared statement is forgotten, so it must be recreated before being used again.

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "testdb"; 

$id = 9;
$name = "BMW";
$price = 36000;

$con = pg_connect("host=$host dbname=$db user=$user password=$pass")
  or die ("Could not connect to server\n"); 

$query = "INSERT INTO cars VALUES($1, $2, $3)"; 

pg_prepare($con, "prepare1", $query) 
  or die ("Cannot prepare statement\n"); 

pg_execute($con, "prepare1", array($id, $name, $price))
  or die ("Cannot execute statement\n"); 

echo "Row successfully inserted\n";

pg_close($con); 

We add a row to the cars table. We use a prepared query.

$id = 9;
$name = "BMW";
$price = 36000;

We have three variables that will be used to build a query. These values could come e.g. from a web form.

$query = "INSERT INTO cars VALUES($1, $2, $3)"; 

This is an SQL query with $1 , $2 , and $3 placeholders. The placeholders will be filled later.

pg_prepare($con, "prepare1", $query) 
  or die ("Cannot prepare statement\n"); 

Here we prepare a query by calling the pg_prepare() function. The second parameter of the function is the name of the prepared statement. It must be unique per-connection. Prepared statements are faster and guard against SQL injection attacks.

pg_execute($con, "prepare1", array($id, $name, $price))
  or die ("Cannot execute statement\n"); 

The pg_execute() function sends a request to execute a prepared statement with given parameters, and waits for the result. The values are bound to the placeholders.

$ php prepared.php
Row successfully inserted

testdb=# SELECT * FROM cars;
 id |  name  | price  
----+------------+--------
  1 | Audi     |  52642
  2 | Mercedes   |  57127
  3 | Skoda    |   9000
  4 | Volvo    |  29000
  5 | Bentley  | 350000
  6 | Citroen  |  21000
  7 | Hummer   |  41400
  8 | Volkswagen |  21606
  9 | BMW    |  36000
(9 rows)

We have inserted a new car into the table.

Sources

The PostgreSQL PHP manual and the PostgreSQL documentation were consulted to create this tutorial.

This was an introductory chapter to PostgreSQL PHP tutorial.