Developer

How do I... Use PHP with PostgreSQL?

PHP's PostgreSQL extension provides a comprehensive API for developers to interact with a PostgreSQL RDBMS. In addition to basic SQL operations, the API also supports prepared statements, transactions, table metadata retrieval, and server variable inspection.

It's no secret that PHP and MySQL go well together. Both PHP and MySQL are open-source projects, are widely used in the developer community, and play well together because of PHP's built-in MySQL support. While MySQL has been getting the lion's share of attention, PostgreSQL also deserves a nod. PostgreSQL is a feature-rich, open-source RDBMS with a loyal following in the developer community that is a viable alternative to commercial products.

This tutorial will introduce you to PHP's PostgreSQL API and show you how PHP can connect to PostgreSQL databases, save and retrieve records, inspect PostgreSQL server variables, and retrieve table information.

Assumptions

Before launching into the tutorial, I want you to inform you about three assumptions I am making about you and your development environment:

  • You know the basics of SQL and PHP.
  • You have a PostgreSQL RDBMS installed, and it is active in your development environment. If this is not the case, you'll need to download and install a version of PostgreSQL that is compatible with your operating system.
  • You have a working Apache Web server with support for PHP 5.1, and your PHP build has support for the PostgreSQL extension. If this is not the case, you'll probably need to recompile PHP with the '—with-pgsql' option (UNIX) or activate the appropriate extension file (Windows). The PHP manual explains how to accomplish these tasks.

Pre-requisites

Let's set up the base table that I will use in the following examples. Start by creating a new database named test:

postgres@thor:~$ /usr/local/pgsql/bin/createdb test

Next, start up the interactive psql shell and create the following table:

postgres@thor:~$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

Type:  copyright for distribution terms

       h for help with SQL commands

       ? for help with psql commands

       g or terminate with semicolon to execute query

       q to quit

postgres=# CREATE TABLE Countries ( postgres(#  CountryID char(2) NOT NULL, postgres(#  CountryName varchar(255) NOT NULL, postgres(#  PRIMARY KEY  (CountryID) postgres(# ); CREATE TABLE postgres=#

Then, add some records to the table to get things rolling:

postgres=# INSERT INTO Countries VALUES ('AL', 'Albania');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('DZ', 'Algeria');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AS', 'American Samoa');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AD', 'Andorra');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AO', 'Angola');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AI', 'Anguilla');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AQ', 'Antarctica');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AG', 'Antigua And Barbuda');

INSERT 0 1

postgres=# INSERT INTO Countries VALUES ('AR', 'Argentina');

INSERT 0 1

When you're done, check to make sure that the records are successfully inserted:

postgres=# SELECT * FROM Countries;
 countryid |     countryname

—————-+——————————-

 AL        | Albania

 DZ        | Algeria

 AS        | American Samoa

 AD        | Andorra

 AO        | Angola

 AI        | Anguilla

 AQ        | Antarctica

 AG        | Antigua And Barbuda

 AR        | Argentina

(9 rows)

Retrieving data

Now that the database has been created and populated, I'll examine how you can use PHP to connect to the database and retrieve records from it. Here's a PHP script that illustrates the process:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">

<html>

  <head></head>

  <body>

<?php

// attempt a connection

$dbh = pg_connect("host=localhost dbname=test user=postgres");

if (!$dbh) {

    die("Error in connection: " . pg_last_error());

}

// execute query

$sql = "SELECT * FROM Countries";

$result = pg_query($dbh, $sql);

if (!$result) {

    die("Error in SQL query: " . pg_last_error());

}

// iterate over result set

// print each row

while ($row = pg_fetch_array($result)) {

    echo "Country code: " . $row[0] . "<br />";

    echo "Country name: " . $row[1] . "<p />";

}

// free memory

pg_free_result($result);

// close connection

pg_close($dbh);

?>

  </body>

</html>

Follow these four standard steps to execute an SQL query on a PostgreSQL database with PHP:

  1. Create a connection to the RDBMS server by calling the pg_connect() function and passing it a string containing information on the host name, database user name, and password ("host=localhost dbname=test user=postgres"). If a connection to the server is possible, a new connection resource object will be returned; if not, the pg_connect() function will return false.
  2. Create the SQL query string and execute it with the pg_query() method. For successful queries, a result object is returned; for unsuccessful ones, the function returns false. If a query is unsuccessful, you can retrieve the reason for failure via a call to pg_last_error().
  3. For SELECT queries, you may process the result object further to extract data from it. The easiest way to process the result object is with a while() loop, which calls the pg_fetch_array() to fetch the next record in the result set as a numerically indexed array; this continues until no records are left to process. If you prefer for each record to be returned as a string-indexed array, pass pg_fetch_array() the PGSQL_ASSOC flag as an additional argument.
  4. End the database session by destroying the result object with pg_free_result() and closing the database connection with pg_close().

Adding and modifying data

The pg_query() method works as well with INSERT, UPDATE, and DELETE queries as it does with SELECT queries. To illustrate this point, consider the following listing, which prompts the user to enter a country name and code and then generates an INSERT query to save the submitted data to the database with pg_query():

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">

<html>

  <head></head>

  <body>

<?php

if ($_POST['submit']) {

    // attempt a connection

    $dbh = pg_connect("host=localhost dbname=test user=postgres");

    if (!$dbh) {

        die("Error in connection: " . pg_last_error());

    }

   

    // escape strings in input data

    $code = pg_escape_string($_POST['ccode']);

    $name = pg_escape_string($_POST['cname']);

   

    // execute query

    $sql = "INSERT INTO Countries (CountryID, CountryName) VALUES('$code', '$name')";

    $result = pg_query($dbh, $sql);

    if (!$result) {

        die("Error in SQL query: " . pg_last_error());

    }

   

    echo "Data successfully inserted!";

   

    // free memory

    pg_free_result($result);

   

    // close connection

    pg_close($dbh);

}

?>

   <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

     Country code: <br> <input type="text" name="ccode" size="2">  

     <p>

     Country name: <br> <input type="text" name="cname">       

     <p>

     <input type="submit" name="submit">

   </form> 

  

  </body>

</html>

The script presents an HTML form with two fields: country code and name. Once this form is submitted, the pg_connect() function is used to open a new connection to the database, the user-submitted data is incorporated into an INSERT query, and the pg_query() function is used to execute the query and save the data to the database. The steps involved in performing this task are identical to those in the previous listing, with the obvious exception of processing the result object with pg_fetch_array().

Note the script's use of the pg_escape_string() function, which is used to escape special characters (such as quotes) in user-submitted data before saving it to the database; this "defangs" user input and helps protect the database from SQL injection attacks.

Using prepared queries

A prepared query is a query template that you use to reduce database overhead when you have a large number of similar INSERT commands to execute. The query template is initialized with placeholder fields, which are replaced with actual values as needed.

PHP's PostgreSQL extension includes pre-defined functions to handle prepared queries. Here's an example of how you can use them:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">

<html>

  <head></head>

  <body>

<?php

// data set

$data = array(

   'FR' => 'France',

   'ES' => 'Spain',

   'IT' => 'Italy',

   'UK' => 'United Kingdom',

   'US' => 'United States'  

);

// attempt a connection

$dbh = pg_connect("host=localhost dbname=test user=postgres");

if (!$dbh) {

    die("Error in connection: " . pg_last_error());

}

// prepare query

$stmt = pg_prepare($dbh, "ps", "INSERT INTO Countries (CountryID, CountryName) VALUES($1, $2)");

// execute query

foreach ($data as $key => $value) {

    $result = pg_execute($dbh, "ps", array($key, $value));

    if (!$result) {

        die("Error in SQL query (country '$key'): " . pg_last_error());

    }

}

// free memory

pg_free_result($result);

// close connection

pg_close($dbh);

?>

  </body>

</html>

Follow these pre-defined steps to using a prepared query with PostgreSQL:

  1. Attempt to connect to the database by passing the pg_connect() function a string containing the host name, user name, and password. This function returns a connection resource object representing the PostgreSQL server connection.
  2. Create a prepared query using the pg_prepare() function. This function accepts three arguments: the database connection object, a string label containing a name for the prepared statement, and an SQL query template. The $* variables, which are used in the SQL query template passed to the pg_prepare() function call, are placeholders for actual data.
  3. Inject actual data values into the query template by calling pg_execute() with the database connection object, the name of the prepared statement to use, and an array containing the values to be inserted. The sequence of elements in the array is important (the first array element must correspond to the placeholder $1 and so on), and there should be one array element for every placeholder in the query template. Every time you call pg_execute() with a different array argument, a new query is executed with the corresponding values.
  4. End the database session by calling pg_free_result() and pg_close().

Retrieving table metadata and server variables

PHP's PostgreSQL extension also supports retrieving table descriptions via its pg_meta_data() function, which returns an array containing information on each field name, type, length, default value, and support for NULL values. Consider the following listing, which illustrates it in action:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">

<html>

  <head></head>

  <body>

   <table border="1">

    <tr>

     <td>Field name</td>

     <td>Field type</td>

     <td>Field length</td>

     <td>Field NOT NULL?</td>

    </tr>

   

<?php

// attempt a connection

$dbh = pg_connect("host=localhost dbname=test user=postgres");

if (!$dbh) {

    die("Error in connection: " . pg_last_error());

}

// get table metadata

$meta = pg_meta_data($dbh, "countries");

foreach ($meta as $key => $value) {

    echo "<tr>";

    echo "<td>$key</td>";

    echo "<td>" . $value['type'] . "</td>";   

    echo "<td>" . $value['len'] . "</td>";   

    echo "<td>" . (($value['not null'] == 1) ? 'true' : 'false') .  "</td>";   

    echo "</tr>";

}

// close connection

pg_close($dbh);

?>

   </table>

  </body>

</html>

There's also a pg_parameter_status() function, which you can use to retrieve the current values of server variables, such as 'max_connections' or 'work_mem'. Here's an example of how  you can use pg_parameter_status():

<?php

// attempt a connection

$dbh = pg_connect("host=localhost dbname=test user=postgres");

if (!$dbh) {

    die("Error in connection: " . pg_last_error());

}

// get value of 'server_version' variable

echo "Server version: " . pg_parameter_status($dbh, 'server_version');

?>

Conclusion

As these examples illustrate, PHP's PostgreSQL extension provides a comprehensive API for developers to interact with a PostgreSQL RDBMS. In addition to basic SQL operations, such as retrieving data and modifying database contents, the API also supports prepared statements, transactions, table metadata retrieval, and server variable inspection. Try it out for yourself, or read more about PHP's PostgreSQL API at PHP.net.

Editor's Picks

Free Newsletters, In your Inbox