@pg_connect()
After installing the database, web-based administration software such as phpPgAdmin (http://sourceforge.net/projects/phppgadmin
) or other tools such as the graphical user interface (GUI) application pgAdmin (http://www.pgadmin.org/
) can be used to administer the database. Alternatively, the command-line tool can be used. To allow PHP to access the PostgreSQL installation, Windows users must load the extension with the entry extension(php_pgsql.dll
) in php.ini
; UNIX/Linux users configure PHP with the switch with-pgsql=/path/to/pgsql
.
Then, pg_connect()
connects to the data source. You have to provide a connection string that contains all important data, including host, port, name of the database, and user credentials.
Connecting to PostgreSQL
<?php if ($db = @pg_connect('host=localhost port=5432 dbname=hoshmand user=postgres password= abc123')) { echo 'Connected to the database.'; pg_close($db); } else { echo 'Connection failed.'; } ?>
Sending SQL to PostgreSQL
pg_query()
The function pg_query()
sends SQL to the PostgreSQL installation. Again, escaping potentially dangerous characters such as single quotes is a must; this can be done with the pg_escape_string()
function. In this code, you see the PHP portion of the script that accepts funny (or not-so-funny) code in an HTML form and writes it to the database.
Sending SQL to PostgreSQL
<?php if ($db = @pg_connect('host=localhost port=5432 dbname=hoshmand user=postgres password=abc123')) { require_once 'stripFormSlashes.inc.php'; pg_query($db, sprintf( 'INSERT INTO quotes (quote, author, year) VALUES (\'%s\', \'%s\', \'%s\')', pg_escape_string($_POST['quote']), pg_escape_string($_POST['author']), intval($_POST['year']))); echo 'Quote saved.'; pg_close($db); } else { echo 'Connection failed.'; } ?>
Retrieving the value in the identity column after the last INSERT
statement is a bit tricky. The PostgreSQL term for such a data type is SERIAL, which automatically creates a sequence. To get the sequence's value, you can use pg_last_oid()
to retrieve the oid (object id) of this value. Then, execute a SELECT id FROM quotes WHERE oid=<oid>
, when <oid>
is the oid you just retrieved. This finally returns the desired value.