Introduction to PostgreSQL PHP programming
last modified July 6, 2020
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.