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.
Of course, there's more than one way of extracting records from a result set. The example on the previous page used an integer-indexed array; this one evolves it a little further so that the individual fields of each records are accessible as keys of a hash, or string-indexed array, via the pg_fetch_array() function.
<html>
<head><basefont face="Arial"></head>
<body>
<h2>Address Book</h2>
<?
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "postgres";
$pass = "postgres";
$db = "test";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT name, address FROM addressbook ORDER BY name"; $result
= pg_query($connection, $query) or die("Error in query: $query. " .
pg_last_error($connection));
// get the number of rows in the resultset
// this is PG-specific
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++)
{
$row = pg_fetch_array($result, $i, PGSQL_ASSOC);
?>
<li><font size="-1"><b><? echo $row['name'];
?></b></font>
<br>
<font size="-1"><? echo $row['address']; ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No data available.</font>
<?
}
// close database connection
pg_close($connection);
?>
</body>
</html>
Most of the magic here lies in the call to pg_fetch_array(),
$row = pg_fetch_array($result, $i, PGSQL_ASSOC);
which returns every row as a hash with keys corresponding to the column
names.
PHP also allows you to access individual fields within a row as object properties rather than array elements, via its pg_fetch_object() function. Take a look:
<html>
<head><basefont face="Arial"></head>
<body>
<h2>Address Book</h2>
<?
// database access parameters
// alter this as per your configuration
$host = "localhost";
$user = "postgres";
$pass = "postgres";
$db = "test";
// open a connection to the database server
$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass");
if (!$connection)
{
die("Could not open connection to database server");
}
// generate and execute a query
$query = "SELECT name, address FROM addressbook ORDER BY name"; $result
= pg_query($connection, $query) or die("Error in query: $query. " .
pg_last_error($connection));
// get the number of rows in the resultset
// this is PG-specific
$rows = pg_num_rows($result);
// if records present
if ($rows > 0)
{
// iterate through resultset
for ($i=0; $i<$rows; $i++)
{
$row = pg_fetch_object($result, $i);
?>
<li><font size="-1"><b><? echo $row->name; ?></b></font>
<br>
<font size="-1"><? echo $row->address; ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No data available.</font>
<?
}
// close database connection
pg_close($connection);
?>
</body>
</html>
In this case, each row is returned as a PHP object, whose properties
correspond to field names; these fields can be accessed using standard object notation.
