- Working with Connections and Data Sources
- Using PostgreSQL and PHP
- Select, Insert, Update, and Delete Queries
- Other Database Functions
Using PostgreSQL and PHP
This section describes the integration of PHP with the PostgreSQL database. There are many instances in Web development when data must be stored and readily retrieved from a database. PHP easily integrates with popular databases such as dBASE, mSQL, MySQL, Informix, Sybase, SQL Server, and PostgreSQL. PHP provides functions to connect with these databases to execute queries, and manage connections and transactions. The examples in the following sections illustrate how to integrate PHP with the PostgreSQL database server.
PostgreSQL Overview
PostgreSQL is a very powerful open-source client/server relational database management system (RDBMS). PostgreSQL was first conceived in 1986 and was known as the Berkley Postgres Project. The project evolved and improved until 1994, when developers Andrew Yu and Jolly Chen added a Structured Query Language (SQL) interpreter to Postgres. This release was known as Postgres95 and was released to the open-source community.
In 1996, Postgres95 was overhauled again and the result was released as PostgreSQL version 6.0. This release of Postgres included increased back-end speed, SQL92 standard enhancements, and important back-end features including subselects, defaults, constraints, and triggers.
PostgreSQL is a very robust package and has many of the features available in a large, commercially available RDBMS. These features include transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking. PostgreSQL also lacks some features that are available in commercial databases, such as user-defined types, inheritance, and rules. From a user's standpoint, just about the only major feature that PostgreSQL does not have is outer joins. Outer joins will be added in a later version.
PostgreSQL offers two operational modes. One mode guarantees that if the operating system or hardware crashes, the data has been stored to the disk. This mode is often slower than most commercially available databases because of the flushing (or syncing) method that is used. The other mode doesn't offer the data guarantees that the first mode does, but it often runs faster than commercial databases. Unfortunately, at the present time, there is no intermediate mode that offers a level of data security with increased performance. This, too, will be provided in a later version.
Connecting Postgres and PHP
The connection between PostgreSQL and PHP is made through the use of the pg_Connect() function. This function accepts five parameters: the hostname of the database server, the port on which the postmaster is listening, any connection options, the tty, and the database name. For our purposes, we assume that the PostgreSQL and PHP are installed on the same machine, and that no connection options or tty information are required. In our examples, the call to pg_Connect() looks like this:
$conn = pg_Connect("localhost", "5432", "", "", "test");
The variable $conn will contain the database connection handle if the function is successful. A sample program looks like this:
<html> <head> <title>Making the Connection</title> </head> <body> <? $conn = pg_Connect("localhost", "5432", "", "", "test"); if (!$conn) {echo "An database connection error occurred.\n"; exit;} else {echo "You have connected to the database successfully.\n";} pg_Close($conn); ?> </body> </html>
If the output of this script looks like this:
You have connected to the database successfully.
then congratulationsyou have successfully connected to the test database.