PHP And PostgreSQL

PHP and PostgreSQL

Table of Contents

A Matter Of Choice (1)

Getting Started (2)

First Steps (5)

Digging Deeper (8)

Different Strokes (10)

Rolling Around (14)

Catching Mistakes (16)

A Well−Formed Idea (18)

Surfing The Web (20)

A Matter Of Choice

There's something patently unfair going on here. For some reason, almost every PHP tutorial on the planet makes the implicit assumption that if you're using PHP with a database, that database is going to be MySQL.

Now, I have absolutely nothing against MySQL − I think it's a great product, and I use it fairly often in my development activities. However, it's not the only good open−source RDBMS out there − most developers have been playing with PostgreSQL for quite a while now, and quite a few of them would love to integrate their PostgreSQL backend with PHP. The only problem is, they have no idea where to start − the functions used to communicate with a PostgreSQL database are different from those used in a MySQL environment, and the documentation out there could do with some updating.

Well, it's time to bring some balance back to the universe. Which is why this article looks at PHP from the PostgreSQL developer's point of view, explaining how PHP can be used with the PostgreSQL database system. If you're a MySQL user, you probably don't need to know any of this; if you're a PostgreSQL fan, on the other hand, you can breathe a sigh of relief and flip the page.

Getting Started

Before we get started, you need to make sure that you have everything you need to successfully use PHP with PostgreSQL. Here's your cheat sheet:

1. The latest version of PostgreSQL, available from (this article uses version 7.1)

2. A Web server which supports PHP, available from / (this article uses Apache 1.

3.24 with PHP

4.2.0)

Note that your PHP build must support PostgreSQL in order for the examples in this article to work correctly. You can include PostgreSQL support in your PHP build by adding the "−−with−pgsql" configuration parameter when compiling the package. Note also that PHP 4.2.0 and better contains fairly extensive changes to the function names used in the language's PostgreSQL module; this article uses the new function names and assumes that you're running PHP 4.2.x. Drop by /manual/en/ref.pgsql.php for more information on the changes, and the corresponding function names for older versions.

I'm not going to get into the details of configuring and installing either PostgreSQL or PHP here − the documentation included with both those packages has more than enough information to get you started, and the accompanying Web sites contain lots of troubleshooting information should you encounter problems. In case you don't already have these packages installed on your development system, drop by the Web sites listed above, get yourself set up and come back once you're done.

Assuming that you have a properly configured and installed setup, the first step is to start both the database server and the Web server.

[postgres@medusa] $ /usr/local/pgsql/bin/postmaster −i −D

/usr/local/pgsql/data &

DEBUG: database system was shut down at 2002−04−12 19:18:12

IST

DEBUG: CheckPoint record at (0, 1694744)

DEBUG: Redo record at (0, 1694744); Undo record at (0, 0);

Shutdown

TRUE

DEBUG: NextTransactionId: 643; NextOid: 18778

DEBUG: database system is in production state

[postgres@medusa] $ su −

root [root@medusa] $ /usr/local/apache/bin/apachectl start

Starting

httpd [OK] [root@medusa] $

Note that the PostgreSQL server must be started as the special "postgres" user created during the installation process, and the startup invocation must include the additional "−i" parameter to allow TCP/IP connections to the server.

The next step is to create an example database table that can be used for the code listings in this article. Here's