Home arrow PHP arrow Page 2 - PHP and PostgreSQL

Getting Started - PHP

When you talk about PHP and databases, people tend to assumeyou're talking about MySQL. But hang on to your horses - difficultthough it may be to believe, PHP does include support for a number ofother databases. One of them is PostgreSQL, the *other* open-sourcedatabase - and this article tells you everything you need to know aboutusing it with PHP.

  1. PHP and PostgreSQL
  2. Getting Started
  3. First Steps
  4. Digging Deeper
  5. Different Strokes
  6. Rolling Around
  7. Catching Mistakes
  8. A Well-Formed Idea
  9. Surfing The Web
By: Vikram Vaswani, (c) Melonfire
Rating: starstarstarstarstar / 52
May 01, 2002

print this article


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 http://www.postgresql.org (this article uses version 7.1)

2. A Web server which supports PHP, available from http://www.php.net/ (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 http://www.php.net/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 what the SQL dump file looks like:

CREATE TABLE addressbook (id serial, name varchar(255), address text, tel varchar(50), email varchar(255)); INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Bugs Bunny', 'The Rabbit Hole, Looney Toons, USA', '123 4567', 'bugs@wascallywabbit.net'); INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Robin Hood', 'Sherwood Forest', 'None', 'robin@steal.from.the.rich'); INSERT INTO addressbook values (nextval('addressbook_id_seq'), 'Sherlock Holmes', '221B Baker Street, London 16550, England', '911 1822', 'holmes@bakerstreetirregulars.domain');
You can import this data into PostgreSQL by dropping to a shell and using the following command:

[postgres@medusa] $ /usr/local/pgsql/bin/createdb test [postgres@medusa] $ /usr/local/pgsql/bin/psql -d test -f /home/postgres/addressbook.sql
Now check whether or not the data has been successfully imported with a SELECT query (the SELECT SQL statement is used to retrieve information from a database) via the interactive PostgreSQL monitor program "psql".

[postgres@medusa] $ /usr/local/pgsql/bin/psql -d test Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# SELECT COUNT(*) FROM addressbook; count ------- 3 (1 row) test=#
which, in English, means "count all the records in the table addressbook and give me the total".

If your output matches what's listed above, take a celebratory sip of Jolt - you're in business!

>>> More PHP Articles          >>> More By Vikram Vaswani, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: