A Simple Example of PHP Access to PostgreSQL

The simple PHP code below forges a (non-persistant) connecton to the simple database as user rose_ro, then reads all the values from the person table, displaying them in an HTML table. Things to notice:

<? and ?> are used to move in and out of PHP mode
Variables, which, as in perl, start with a $, need not be declared before they are used.
pg_Connect() is a function to use to connect to a PostgreSQL database, returning a database handle. pg_Close() closes the connection.
pg_exec() is used to issue an SQL query; the database handle is an argument. The return value is a statement handle.
pg_fetchrow(), which takes the statement handle as an argument, is used to return a row of data from the query.

Note that regardless the use of suexec with CGI scripts, PHP code executes in the user context of the web server user, which is nobody at our site.

Execute the code.


  <html>

  <head>
   <title>Test</title>
  </head>

  <body bgcolor="white">

  <?
  $link = pg_Connect("dbname=simple user=rose_ro password=obscured");
  $result = pg_exec($link, "select * from person");
  $numrows = pg_numrows($result);
  echo "<p>link = $link<br>
  result = $result<br>
  numrows = $numrows</p>
  ";
  ?>

  <table border="1">
  <tr>
   <th>Last name</th>
   <th>First name</th>
   <th>ID</th>
  </tr>
  <?

   // Loop on rows in the result set.

   for($ri = 0; $ri < $numrows; $ri++) {
    echo "<tr>\n";
    $row = pg_fetch_array($result, $ri);
    echo " <td>", $row["fname"], "</td>
   <td>", $row["lname"], "</td>
   <td>", $row["id"], "</td>
  </tr>
  ";
   }
   pg_close($link);
  ?>
  </table>

  </body>

  </html>

Return to Administering PostgreSQL for Instructional Use on Cubist.

Last modified: Thursday, 24-Oct-2013 23:39:40 PDT.

webmaint@cs.washington.edu