PHP and PostgreSQL - Digging Deeper (
Page 4 of 9 )
Now, that was a very
basic example. How about something a little more useful?
This next
example will query the database, return the list of addresses, and display them
all as a neatly-formatted list.
<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_row($result, $i);
?>
<li><font size="-1"><b><? echo $row[0]; ?></b></font>
<br>
<font size="-1"><? echo $row[1]; ?></font>
<p>
<?
}
}
// if no records present
// display message
else
{
?>
<font size="-1">No data available.</font>
<?
}
// close database connection
pg_close($connection);
?>
</body>
</html>
Here's what the output looks like:
As in
the previous example, the script first sets up a connection to the database. The
query is formulated and the result set is returned to the browser. In this case,
since I'm dealing with multiple rows of data, I've used the pg_fetch_row()
function in combination with a "for" loop to iterate through the result set and
print the data within each row.
The pg_fetch_row() function returns the
columns within each row as array elements, making it possible to easily access
the values within a record. By combining it with a "for" loop, I can easily
process the entire result set, thereby displaying all returned records as list
items.
Finally, in case you're wondering, the pg_last_error() function
returns the last error generated by the server - combined with die(), this
provides an effective, if primitive, debugging mechanism.
