标签
PostgreSQL , Java , php , ruby , python , C
背景
转自 http://zetcode.com/db/postgresqlphp/
正文
This is a PHP tutorial for the PostgreSQL database. It covers the basics of PostgreSQL programming with PHP. The examples were created and tested on Linux.
Table of Contents
Introduction
Reading data
Working with images
Getting database metadata
Transactions
PostgreSQL & PHP
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 X. PostgreSQL is developed by the PostgreSQL Global Development Group.
PHP is a scripting language designed for web development. It is used to produce dynamic web pages. Currently, PHP is one of the most widely used programming languages. Much of its syntax is borrowed from C, Java and Perl with a couple of unique PHP-specific features. PHP can be embedded into HTML code and it generally runs on a web server. PHP supports both object oriented and procedural programming styles.
Introduction to PostgreSQL PHP programming
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.
http://www.php.net/manual/en/book.pgsql.php
This was an introductory chapter to PostgreSQL PHP tutorial.
Retrieving data with PHP in PostgreSQL
There are several functions to read data from a database. The data can be fetched as an enumerated array, as an object or as an associated array.
There are three steps to retrieve data from a database. First we define an SQL SELECT statement. The statement is executed with the pg_query() function. (In case of prepared statements, we would use pg_execute() function.) We receive a result set object. Using the result set, we fetch the data with pg_fetch_row(), pg_fetch_assoc() or pg_fetch_object() functions.
<?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 * FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
while ($row = pg_fetch_row($rs)) {
echo "$row[0] $row[1] $row[2]\n";
}
pg_close($con);
?>
We get 5 cars from the cars table and print them to the console.
$query = "SELECT * FROM cars LIMIT 5";
This is the SQL to fetch 5 rows of cars.
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
The query is executed with the pg_query() function. The function returns a result set.
while ($row = pg_fetch_row($rs)) {
echo "$row[0] $row[1] $row[2]\n";
}
The pg_fetch_row() function returns an array of string values. We can use array index notation to get the array fields. When there are no more rows, the function returns false and the while loop terminates.
$ php retrieve1.php
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
Example output.
In the second example, we will fetch data with the pg_fetch_assoc() function.
<?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 * FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
while ($row = pg_fetch_assoc($rs)) {
echo $row['id'] . " " . $row['name'] . " " . $row['price'];
echo "\n";
}
pg_close($con);
?>
The pg_fetch_assoc() function fetches a row as an associative array. The keys of the associative array are the column names.
while ($row = pg_fetch_assoc($rs)) {
echo $row['id'] . " " . $row['name'] . " " . $row['price'];
echo "\n";
}
The id, name, and price are the keys to the returned associative array.
In the last example, we will fetch the data with the pg_fetch_object() function. It returns an object with properties that correspond to the fetched row's field names.
<?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 * FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die("Cannot execute query: $query\n");
while ($ro = pg_fetch_object($rs)) {
echo $ro->id . " ";
echo $ro->name . " ";
echo $ro->price . " ";
echo "\n";
}
pg_close($con);
?>
We select five cars from the cars table.
while ($ro = pg_fetch_object($rs)) {
echo $ro->id . " ";
echo $ro->name . " ";
echo $ro->price . " ";
echo "\n";
}
The column names are the object properties, which hold the values.
We have finished reading data using pg_fetch_row(), pg_fetch_assoc(), and pg_fetch_object() functions.
Working with images with PHP in PostgreSQL
In this chapter of the PostgreSQL PHP tutorial, we will work with image files. Some people do not agree with putting images into databases. Here we only show how to do it. We do not dwell into technical issues of whether to save images in databases or not.
testdb=> CREATE TABLE images(id INT PRIMARY KEY, data BYTEA);
For this example, we create a new table called images. For the images, we use the BYTEA data type. It allows to store binary strings.
Inserting images
In the first example, we are going to insert an image into 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");
$file_name = "woman.jpg";
$img = fopen($file_name, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file_name));
$es_data = pg_escape_bytea($data);
fclose($img);
$query = "INSERT INTO images(id, data) Values(1, '$es_data')";
pg_query($con, $query);
pg_close($con);
?>
We read an image from the current working directory and write it into the images table of the PostgreSQL testdb database.
$file_name = "woman.jpg";
This is the name of the image file that we will insert into the database. The image is located in the current working directory.
$img = fopen($file_name, 'r') or die("cannot read image\n");
$data = fread($img, filesize($file_name));
We read binary data from the filesystem.
$es_data = pg_escape_bytea($data);
Binary data might have characters that may cause problems when inserting them into a database table. The pg_escape_bytea() function escapes the string for insertion into a bytea field. Later, when reading binary data from the database, the data must be un-escaped.
fclose($img);
The handle pointing to the image file is closed.
$query = "INSERT INTO images(id, data) Values(1, '$es_data')";
pg_query($con, $query);
The image is inserted into the database.
Reading images
In this section, we are going to perform the reverse operation. We will read an image from the database table.
<?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 data FROM images WHERE id=1";
$res = pg_query($con, $query) or die (pg_last_error($con));
$data = pg_fetch_result($res, 'data');
$unes_image = pg_unescape_bytea($data);
$file_name = "woman2.jpg";
$img = fopen($file_name, 'wb') or die("cannot open image\n");
fwrite($img, $unes_image) or die("cannot write image data\n");
fclose($img);
pg_close($con);
?>
We read image data from the images table and write it to another file, which we call woman2.jpg.
$query = "SELECT data FROM images WHERE id=1";
This line is a SQL SELECT statement to retrieve the image data from the table.
$data = pg_fetch_result($res, 'data');
We fetch the data from the data column of the images table.
$unes_image = pg_unescape_bytea($data);
When we inserted the image data into the database, we have escaped it. Now we have to un-escape it back to the original.
$file_name = "woman2.jpg";
$img = fopen($file_name, 'wb') or die("cannot open image\n");
We open a file for writing. The new file name will be woman2.jpg.
fwrite($img, $unes_image) or die("cannot write image data\n");
The data is written to the filesystem.
This part of the PostgreSQL PHP tutorial was dedicated to reading and writing images.
Getting PostgreSQL metadata with PHP
Metadata is information about the data in the database. Metadata in PostgreSQL contains information about the tables and columns, in which we store data. Number of rows affected by an SQL statement is metadata. Number of rows and columns returned in a result set belong to metadata as well.
There is an experimental function pg_meta_data(), which returns table definition for a table name as an array.
Columns and rows
As we have already stated, the number of columns and rows in a result set is considered to be metadata.
<?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 Name, Price FROM Cars LIMIT 4";
$rs = pg_query($con, $query) or die (pg_last_error($con));
$num_rows = pg_num_rows($rs);
$num_cols = pg_num_fields($rs);
echo "There are $num_rows rows and $num_cols columns in the query\n";
pg_close($con);
?>
In the above example, we get the number of rows and columns returned by a query.
$query = "SELECT Name, Price FROM Cars LIMIT 4";
From the SQL query we can see that we select 2 columns and 4 rows. The query could be created also dynamically.
$num_rows = pg_num_rows($rs);
$num_cols = pg_num_fields($rs);
The pg_num_rows() function returns the number of rows in a PostgreSQL result resource. The pg_num_rows() function returns the number of columns (fields) in a PostgreSQL result resource.
$ php colsrows.php
There are 4 rows and 2 columns in the query.
Example output.
Column headers
Next we will show, how to print column headers with the data from a database table.
<?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 id, name, price FROM cars LIMIT 5";
$rs = pg_query($con, $query) or die (pg_last_error($con));
$fname1 = pg_field_name($rs, 0);
$fname2 = pg_field_name($rs, 1);
$fname3 = pg_field_name($rs, 2);
printf("%3s %-10s %8s\n", $fname1, $fname2, $fname3);
while ($row = pg_fetch_row($rs)) {
printf("%3s %-10s %8s\n", $row[0], $row[1], $row[2]);
}
pg_close($con);
?>
In this program, we select 5 rows from the cars table with the column names.
$fname1 = pg_field_name($rs, 0);
$fname2 = pg_field_name($rs, 1);
$fname3 = pg_field_name($rs, 2);
The pg_field_name() function returns the name of the column (field) for the specified column number.
printf("%3s %-10s %8s\n", $fname1, $fname2, $fname3);
We print the column headers. We do some formatting with the printf function.
$ php column_headers.php
id name price
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
Ouput of the program.
Affected rows
In the following example, we will find out how many changes have been done by a particular SQL command.
<?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 friends";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "CREATE TABLE friends(id INT, name TEXT)";
pg_query($con, $query) or die("Cannot execute query: $query\n");
$query = "INSERT INTO friends VALUES (1, 'Jane'), (2, 'Thomas')"
. ", (3, 'Beky'), (4, 'Robert'), (5, 'Lucy')";
$res = pg_query($con, $query) or die("Cannot execute query: $query\n");
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
$query = "DELETE FROM friends WHERE id IN (3, 4, 5)";
$res = pg_query($con, $query) or die("Cannot execute query: $query\n");
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
pg_close($con);
?>
We create a friends table. In the last SQL command, we delete three rows. We have one INSERT and one DELETE statement for which we can call the pg_affected_rows() to get the number of affected rows.
$query = "INSERT INTO friends VALUES (1, 'Jane'), (2, 'Thomas')"
. ", (3, 'Beky'), (4, 'Robert'), (5, 'Lucy')";
We insert five rows into the friends table.
$ar = pg_affected_rows($res);
echo "The query has affected $ar rows\n";
The pg_affected_rows() function returns the number of rows affected by the last SQL statement.
$ php affected_rows.php
The query has affected 5 rows
The query has affected 3 rows
The INSERT statement has created five rows, the DELETE statement has removed 3 rows.
Table metadata
There is an experimental pg_meta_data(). It returns metadata for each column of a database table.
<?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");
$ary = pg_meta_data($con, 'cars');
var_dump($ary);
pg_close($con);
?>
The example prints metadata about table columns of the cars table.
$ary = pg_meta_data($con, 'cars');
The pg_meta_data() returns metadata information for the cars table. It returns an array.
var_dump($ary);
The var_dump() function dumps information about a variable. In our case it is the returned array of metadata information.
$ php metadata.php
array(3) {
["id"]=>
array(6) {
["num"]=>
int(1)
["type"]=>
string(4) "int4"
["len"]=>
int(4)
["not null"]=>
bool(true)
...
Excerpt from the example output.
In this part of the PostgreSQL PHP tutorial, we have worked with database metadata.
Transactions with PHP in PostgreSQL
In this chapter, we will work with transactions. First, we provide some basic definitions. Then we will a sample program that uses a transaction.
A transaction is an atomic unit of database operations against the data in one or more databases. SQL statements in a transaction can be either all committed to the database or all rolled back. SQL statements are put into transactions for data safety and integrity.
In PostgreSQL PHP each SQL statement is committed to the database after it is executed. This is not true for all language bindings. For example in Python's psycopg2 module all changes must be explicitly committed with a commit() method by default.
In direct SQL, a transaction is started with BEGIN TRANSACTION statement and ended with END TRANSACTION, COMMIT statement. In PostgreSQL these statements are BEGIN and COMMIT. In some drivers these statements are omitted. They are handled by the driver. In PHP there are no such methods and the we must use the direct SQL. (In PHP PDO there are such methods.)
<?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");
pg_query("BEGIN") or die("Could not start transaction\n");
$res1 = pg_query("DELETE FROM cars WHERE id IN (1, 9)");
$res2 = pg_query("INSERT INTO cars VALUES (1, 'BMW', 36000), (9, 'Audi', 52642)");
if ($res1 and $res2) {
echo "Commiting transaction\n";
pg_query("COMMIT") or die("Transaction commit failed\n");
} else {
echo "Rolling back transaction\n";
pg_query("ROLLBACK") or die("Transaction rollback failed\n");;
}
pg_close($con);
?>
We have our cars table. We want to swap the first row with the last row. We do it in two steps. First we delete the two rows. Then we insert them with their id's exchanged. These two operations need to be placed in a transaction. If the first step succeeded and the second failed, the data would be corrupted. Therefore we need to have all done or nothing done.
pg_query("BEGIN") or die("Could not start transaction\n");
We start a new transaction by issuing the BEGIN statement.
$res1 = pg_query("DELETE FROM cars WHERE id IN (1, 9)");
$res2 = pg_query("INSERT INTO cars VALUES (1, 'BMW', 36000), (9, 'Audi', 52642)");
These are the two SQL statements that modify our table. Both pg_query() functions return true or false boolean value indicating whether the SQL command failed or not.
if ($res1 and $res2) {
echo "Commiting transaction\n";
pg_query("COMMIT") or die("Transaction commit failed\n");
} else {
echo "Rolling back transaction\n";
pg_query("ROLLBACK") or die("Transaction rollback failed\n");;
}
If both function calls return true, we commit the transaction with the COMMIT statement. Otherwise we rollback the changes with the ROLLBACK statement.
$ php transaction.php
Commiting transaction
testdb=# SELECT * FROM cars ORDER BY id;
id | name | price
----+------------+--------
1 | BMW | 36000
2 | Mercedes | 57127
3 | Skoda | 9000
4 | Volvo | 29000
5 | Bentley | 350000
6 | Citroen | 21000
7 | Hummer | 41400
8 | Volkswagen | 21606
9 | Audi | 52642
(9 rows)
The rows were successfully swapped.
In this part of the PostgreSQL PHP tutorial, we have mentioned transactions.